Allow to get/set RAW attributes of Objects

[Elias481]

For general questions:

I need to access RAW attributes of Objects. Currently support for this is missing in odpi...

I implemented this to get my script work.
It's only about 26 lines of code to add.
(You could see how I implemented for me at https://github.com/Elias481/odpi/pull/1/files).

But probably I would change slightly more lines and change signature of dpiOci__rawPtr to char** for ptr as this looks more clean to me (beeing not a C programmer...).
I also checked this works but not really tested for side effects on the other code that uses dpiOci_rawPtr..
(https://github.com/Elias481/odpi/pull/2/files)

Answer the following questions:

1. What version of ODPI-C are you using (see dpi.h)?
   current (3.0.0) / master from github

2. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?
   get or set RAW attributes of Objects

3. What error(s) you are seeing?
   For example in cx_Oracle I get "cx_Oracle.NotSupportedError: Oracle type 0 not supported." when trying to access the RAW attributes of Objects

4. What OS (and version) is your application executing on?
   Linux

5. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where it is installed?
   Different (11.2.0.4, 18.3.0.0, ORACLE_HOME and Instant-Client)

6. What is your Oracle Database version?
   Issue exists with 11.2.0.4 and 18.3.0.0 and probably all the versions between..

7. What is the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) set to? On macOS, what is in ~/lib?
   The one needed for the client I want to use...

8. What environment variables did you set? How exactly did you set them?
   Depends on client and setup I want to use. Eigther ORACLE_HOME or LD_LIBRARY_PATH or nothing at all if I use Instant-Client with ld.conf.so setup for the client and use Python ctypes to preload libclntsh.so*. Sometimes I also set NLS_LANG... How I set them depends on how I execute the Python scripts...

9. What compiler version did you use?
   For example gcc 4.8.5 20150623 (Red Hat 4.8.5-28)

[anthony-tuininga]

For your first request for support of RAW attributes I'll look into getting that sort of code added. If you have an example that currently fails it would be helpful to include it, as that way something like it can be included in the test suite as well.

For your second suggestion about the use of char** instead of void** I don't think that should be changed. For raw (binary) data the use of void* is common and expected. See such functions as memset(), memcpy(), etc.

[ddevienne]

unsigned char* is just as often used for "bytes" than void* IMHO.
you store "bytes" in a std::vector<unsigned char> for example.
using void* will force casting. memset() and co. use void* and will thus accept unsigned char*.
while the reverse, putting the content bytes in a std::vector will require casting.
So basing the API on unsigned char* makes more sense to me. Mainly because sizeof(char)==1.
(and some prefer to use char* rather than unsigned char*).

Accepting args as void* is more general, I agree.
But when returning the bytes to someone, unsigned char* or char* is more convenient.

BTW OCIRawAssignBytes() takes a const ub1* as arg, i.e. an unsigned char* :)

[Elias481]

Normally I would just include a RAW attribute in UDT_OBJECTDATATYPES and set/get that attribute in Test 1205... But I cannot verify this works as tests as this test fails on my setup anyway:

1200. verify metadata returned by dpiStmt_getQueryInfo() [FAILED]
    Value 400 does not match expected value 100.
...
1205. verify get and set attributes of an object [FAILED]
    Value 2 does not match expected value 4.

 [FAILED]
    FN: dpiObject_getAttributeValue
    ACTION: start
    MSG:

Also some more tests are failing:

1006. dpiVar_setFromBytes() with value too large [FAILED]
    Expected error: 'DPI-1019: buffer size of 2 is too small'
415. verify DBOP attr is set as expected [FAILED]
    FN: dpiStmt_getQueryValue
    ACTION: check fetched row
    MSG: DPI-1029: no row currently fetched
1926. verify CLOB, NCLOB, BLOB values with different buffer sizes [FAILED]
    String 'pkDHTxmMR18N2l9k88EmLgN7cCCTt9rWksb1fEBw397vi5Ug1YHC3UAVUAoB9VYjCxYhzZSrWPc5IuZAHeCAyATJA7KAQghSS6yHTEyqRPja76aCbaANbTUbOdZf97vP1hVIlHw3UVRSQrSrFT4gmP61qTUnOD3FlOMKV8DoUS6i7OPDfHjIUd7AxPoBShF3tRCCPFqhYkFVxSWSa4YsXTtIiVI10NsjcujpyONKwQdhh005uMnFgUOCpW3fhkC9UkoGyzQiEsGli4eQGHVkchnF3elYElXZLAd7xug1cka8e4OkAhJaFwf6QETVqoszoLva3PPTzqRTid1g9A6Cua6BePUI4C8gLt6D8MWv0mEWD33C4xEmN9nsO50I5wpHdjKZteKjGM4IGxK8iNkwMMcaHQhDDKIgrQ6buEAzR47XpFTOS38cDa1LqYMrgUMNkGoSKnHbfEwLKFXa7T3AtuJXGVFmnPxNVcgfDl5iqrryvEOqXFoEzyc
 [FAILED]
    FN: dpiLob_readBytes
    ACTION: start
    MSG:
+ invalid login tests because the message is in german (with NLS_LANG set to AMERICAN...)

Not sure why this happens with my test setup... Other compiler? German language setup? Enabled extended long varchar/raw feature?

I can try to find issue with Test 1205 on my machine but I need some time as I cannot see where a value 4 is expected, for example.
Or I verify with a standalone test case...
I did my tests with cx_Oracle / Python...

Regarding void*/char* I understand it does not make much difference. For me "void" sounds a bit strange but I'm not really into C... And it doesn't matter as it would work with void*..

[Elias481]

OK, found reasons for failing tests.

1. Had set my NLS_LANG to AMERICAN_AMERICA.AL32UTF8 (so expected client size differed and also the LOB test did not fail anymore where I did not analyze the exact reason).
2. The 1205 failed because DBTIMEZONE is Europe/Berlin so the Objects "timestamp with local time zone" field was converted to somehow but not converted back...
   I got around the 2nd with just setting expected value to +2 hours in the test (could not fix it with different ORA_SDTZ env and spent not too much time possibly this is expected behaviour but at least another topic).

After that only the 415 and the invalid login tests are failing. So I could verify that the Test 1205 can just be extended.

Expected error with current version that cannot handle RAW attributes:

 [FAILED]
    FN: dpiObject_setAttributeValue
    ACTION: get attribute value
    MSG: DPI-1008: data type 95 is not supported

And expect no error with changes applied that enable the use of RAW attributes.

I just pushed my changes in test cases to branch so they are visible in the "pull/1"... But they include the "hack" to get around the timezone conversion issue. (Which will cause the test to fail on Timezone with different offset to +2:00 during DST.)

P.S. I forgot to mention, I did some more tests using cx_Oracle. (Inserting into real object table, selecting from it, max out the length to 32767 with extended sizes enabled database, trying to insert the object with a raw(32767) value set to a value of length 32768 [getting ORA-21525 on statement execute]..)

[anthony-tuininga]

Thanks for the additional analysis. I pushed one change to the test setup to ensure that NLS_LENGTH_SEMANTICS is set to BYTE (you may have it set to CHAR?). The rest will probably have to wait until after the conference, unfortunately, but I appreciate the suggested code.

[Elias481]

No I let it at default in this case so it's already BYTE... Not sure whether this could apply at all (afaik the NLS_LENGTH_SEMANTICS setting should not be in effect for "/as sysdba" or SYS connection used to set up the schema)...

You could set the encoding parameter for the connection to ISO-8859-1 to overrule a different NLS_LANG setting to avoid the issue I had with the different expected client-sizes and with the lob test.

[anthony-tuininga]

No I let it at default in this case so it's already BYTE... Not sure whether this could apply at all (afaik the NLS_LENGTH_SEMANTICS setting should not be in effect for "/as sysdba" or SYS connection used to set up the schema)...

The patch I applied to the setup script certainly made a difference, and it was running as SYSDBA. :-) So it was definitely something that needed to be done in order to make sure that the setup was as expected, even if that wasn't your issue.

You could set the encoding parameter for the connection to ISO-8859-1 to overrule a different NLS_LANG setting to avoid the issue I had with the different expected client-sizes and with the lob test.

What is the value of this query?

select parameter, value
from nls_database_parameters
where parameter like '%CHARACTERSET'

[Elias481]

The database character set is quite default, AL32UTF8 and AL16UTF16 for NCHAR.
But I would think this should be unrelated to the database character set reading the docs as this should be derived from OCI_NLS_CHARSET_MAXBYTESZ...

OCI_NLS_CHARSET_MAXBYTESZ: Maximum character byte size for OCI environment or session handle character set

And OCI_NLS_CHARSET_MAXBYTESZ will probably not take into account the server character set but only the character set effective for the OCI env/session (so the one set through encoding parameter for NlsEnv creation or NLS_LANG/NLS_NCHAR in case it was not set/set to 0). At least it shouldn't as database character set setting to US7ASCII would not prohibit You to get a mutibyte character from server selected/converted into the character set defined at client (OCI env).

Don't know what is fact regarding NLS_LENGTH_SEMANTICS. I never really checked this. But according to Oracle docs I read it should not apply if running as SYS. (Which should be the case if "/ as sysdba" connection is used but not probably not if another user is with sysdba privileges is used..)

And regarding the test setup instructions I'm wondering whether they really work if You just follow the readme... As the default connect string refers to "orclpdb" (which somehow implies the default/instruction is for running tests in a PDB) but the test setup itself does not account for using a pdb, as far as I see... Maybe it should be documented or better implemented in test setup..

define pdb = "orclpdb"

begin if '&pdb' is not null and ('' is null or '&pdb' <> '') then execute immediate 'alter session set container=&pdb'; end if; end;
/

(to allow for "null"(empty) define and account for Oracles reserved right to fix the behaviour treating empty string as null in future release it's a bit longer then currently needed....)

I also checked the other failing tests issues. So the language issue with login message is just an still unfixed bug [in oracle db server software] (why does it use the instances NLS_LANGUAGE for this particular message and not the language set on client side? if trying to log in with empty user the same error is returned in client specified language and other errors such as empty passwords are also reported in client language).

The issue with the failing DBOP is just that I only have SE2 were control_management_pack_access is disabled per default for licensing reasons.
Not sure whethere licensing would allow to enable the feature for to run such tests if no EE+Tuning license was bought....
Anyway checking the test I found that the query should be changed to

select dbop_name from v$sql_monitor where sid = sys_context('userenv', 'sid') and status = 'EXECUTING' and dbop_name = 'ODPIC_TEST_DBOP'

because there is no guaranty for the order of the rows returned it is not said that You get an entry belonging to the dbop_name (there could be others from long running statements in same PDB) and also if You do not check for the executing You may get an older value from previous test run as there is only a guaranty for min duration the entries appear in v$sql_monitor (and I saw that in fact can stay there for many hours or days at least if there is not much traffic on the db... but in my case I the entries where from a point in time where the mgmt-pack access was disabled, so maybe they do not stay that long there if the access is and stays enabled...)

So only the issue with the "timestamp with local timezone" is still unresolved...

[anthony-tuininga]

As you can see I have pushed code that addresses your request to be able to get/set raw attributes on objects in both ODPI-C and cx_Oracle.

Anyway checking the test I found that the query should be changed to

select dbop_name from v$sql_monitor where sid = sys_context('userenv', 'sid') and status = 'EXECUTING' and dbop_name = 'ODPIC_TEST_DBOP'

I added the status = 'EXECUTING' clause but the last clause should not be necessary as we are only looking at this particular session and it is only executing one query. I made the same change in cx_Oracle.

And regarding the test setup instructions I'm wondering whether they really work if You just follow the readme... As the default connect string refers to "orclpdb" (which somehow implies the default/instruction is for running tests in a PDB) but the test setup itself does not account for using a pdb, as far as I see... Maybe it should be documented or better implemented in test setup..

They definitely work. :-) But you're right that you do have to adjust the connect string or perform an alter session. I think its easier to just change the connect string but an alter session statement would also work. I'll look into adjusting the README to make that clearer.

So only the issue with the "timestamp with local timezone" is still unresolved...

I'd suggest opening a new issue for that situation.

[Elias481]

Thanks for pushing the code for the RAW field. I still did not found time to complete my script that depends on this, but having this in official cx_Oracle is fine.

Regarding query for dbop You are right. There shouldn't be more then one EXECUTING statements in v$sql_monitor for single SID.

I can open new issue for timestamp situation. I did some research on it but already forgot the results. I will try to remeber and log a new issue for that when I find time.

[anthony-tuininga]

Sounds good. I'll close this issue, then, and you can open a new one when you get the time to do so.