Bug in Oracle 10 interface? (and 9)

[Jacques]

We are upgrading our database from Oracle 8 to Oracle 10 and are now using the Oracle 10 interface from VA Smalltalk 7.5.

However we encounter problems with the CHAR datatype field caused by the conversion of Oracle from single-byte characters (version 8 ) to multi-byte characters (version 10). Oracle seems to have changed the metadata description of the table definitions.

In Oracle 8:

TABLE COL_NAME DATATYPE DATA_LENGTH
---------- ---------- ---------- -----------
BASPR_WGAP GESLACHT CHAR 1

And in Oracle 10:
TABLE COL_NAAM DATATYPE DATA_LENGTH CHAR_LENGTH
---------- ---------- ---------- ----------- -----------
BASPR_WGAP GESLACHT CHAR 4 1

So the length of a column is no longer determined by DATA_LENGTH definition, however by CHAR_LENGTH. DATA_LENGTH is now used by Oracle to determine the bytes of data stored in the database.

We have been debugging the code and think to have located the problem in AbtOracle8DatabaseConnection>>getColLengthForDescriptor: ifError:

Code: Select all

getColLengthForDescriptor: parmdpp ifError: anErrorBlock

      | colLength array rc |
      colLength := ByteArray new: 2.
      (array :=  Array new: 6)
            at: 1 put: parmdpp;
            at: 2 put: OCI_DTYPE_PARAM;
            at: 3 put: colLength;
            at: 4 put: 0;
            at: 5 put: OCI_ATTR_DATA_SIZE;
            at: 6 put: self errhp;
            yourself.
      ((rc := self callPlatformFunction: OCIAttrGet
                  withArray: array
                  useThreadPreference: true
                  threadKey: (self defaultThreadKey) ) = OCI_SUCCESS)
      ifFalse: [^self verifyReturnCode: rc ifError: anErrorBlock].
      ^ colLength abtAsInteger.


AbtOracle10DatabaseConnection uses this code from the version 8 interface to get the column length of a CHAR field with OCI_ATTR_DATA_SIZE. This however delivers the length of raw data reserved in the database, not the length of the field itself.

Do you have a suggestion in solving this problem?

Thanks.[quote][/quote]

[Jacques]

In addition to my previous post:

The problem we encounter is when retrieving data from a table with a CHAR(1) datatype field, the VA result table with Oracle 10 is different from the result table with Oracle 8.

With Oracle 8 a query of a CHAR(1) field will result in data like:
'1',
'2',
'3',
...

With Oracle 10 the same query results in:
'1___',
'2___',
'3___',
...

<The ___ should be read as 3 spaces>.

So the data returned by VA is 4 bytes in length, whereas the column definition is CHAR(1).
According to our DBA this is caused by reading the DATA_LENGTH from the metadata, instead of the CHAR_LENGTH. The VA Oracle interface seems to add spaces up to the DATA_LENGTH instead of CHAR_LENGTH.

Hope this will help.

[wembley]

Jacques -

From the Oracle 10g SQL Reference:

CHAR Datatype
The CHAR datatype specifies a fixed-length character string. Oracle ensures that all values stored in a CHAR column have the length specified by size. If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. If you try to insert a value that is too long for the column, then Oracle returns an error.

The semantics of the length specification changes based on the setting of the NLS_LENGTH_SEMANTICS parameter. Can you tell me what its value is for your installation?

[Jacques]

The NLS_LENGTH_SEMANTICS are :
DATABASE:
select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_CHARACTERSET', 'NLS_LENGTH_SEMANTICS')

"PARAMETER", "VALUE"
NLS_CHARACTERSET, AL32UTF8
NLS_LENGTH_SEMANTICS, BYTE

INSTANCE:
select * from NLS_INSTANCE_PARAMETERS where parameter = 'NLS_LENGTH_SEMANTICS'

"PARAMETER", "VALUE"
NLS_LENGTH_SEMANTICS, CHAR

SESSION:
select * from NLS_SESSION_PARAMETERS where parameter = 'NLS_LENGTH_SEMANTICS'

"PARAMETER", "VALUE"
NLS_LENGTH_SEMANTICS, CHAR

The tables are created with CHAR semantics ( on Oracle 10g , database characterset AL32UTF8)
f.e.
SQL> desc aov.BASPR_WGAP_07A3
Name Null? Type
----------------------------------------- -------- ----------------
GESLACHT NOT NULL CHAR(1 CHAR)
<...> more


In order to improve the support on CHARACTER based and Byte based language semantics, oracle distinguishes between
CHAR_LENGTH and DATA LENGTH in the datadictionary (This holds for Oracle 9 and higher versions)


In Oracle 8 (single byte characterset: WE8ISO8859P15 :

select table_name TABEL, column_name COL_NAAM, DATA_TYPE, DATA_LENGTH
--, CHAR_LENGTH (does not exist)
from dba_tab_columns
where table_name= 'BASPR_WGAP_07A3' and column_name = 'GESLACHT';


TABEL COL_NAAM DATATYPE DATA_LENGTH
--------------- ---------- ---------- -----------
BASPR_WGAP_07A3 GESLACHT CHAR 1

(If you want to use a multibyte characterset on oracle 8 you must specify a different field length at table creation than when using a single byte characterset) !

and on oracle 9 and 10 :

select table_name TABEL, column_name COL_NAAM, DATA_TYPE, DATA_LENGTH, CHAR_LENGTH
from dba_tab_columns
where table_name= 'BASPR_WGAP_07A3' and column_name = 'GESLACHT';

( single byte characterset: WE8ISO8859P15)

TABEL COL_NAAM DATATYPE DATA_LENGTH CHAR_LENGTH
--------------- --------- ---------- ----------- -----------
BASPR_WGAP_07A3 GESLACHT CHAR 1 1


(multibyte characterset AL32UTF8)

TABEL COL_NAAM DATATYPE DATA_LENGTH CHAR_LENGTH
--------------- --------- ---------- ----------- -----------
BASPR_WGAP_07A3 GESLACHT CHAR 4 1


VA is probably collecting metadata on the oracle objects with queries on the oracle dictionary querying for the DATA LENGTH of the columns in stead of the CHAR_LENGTH as it is supposed to do on Oracle 9 and higher versions.

[Jacques]

In order to clarify the problem, we have create a script to reproduce the problem.

1) Create a TEST table in Oracle 10:

CREATE TABLE AOV_OWNER.TEST ( TEST1 CHAR(1) );


2) Open a database connection from VA to the database and evaluate the following script:

Code: Select all

connection := AbtDbmSystem activeDatabaseConnectionWithAlias: ''.
connection executeQuerySpec: (AbtQuerySpec new statement: 'INSERT INTO AOV_OWNER.TEST TEST1 VALUES (''a'')').
connection commitUnitOfWork.

resultTable := connection
   resultTableFromQuerySpec: (
      AbtQuerySpec new
         statement: 'SELECT TEST1 FROM AOV_OWNER.TEST' ).
            .
resultTable do: [ :eachRec |
   ( eachRec at: 'TEST1' ) inspect ]


The String inspector opens with: 'a___', an 'a' with 3 blanks, whereas the TEST1 column is defined as CHAR(1)! We would expect just 'a' instead of 'a___'.

Hope this will help.

[wembley]

Jacques -

Thank you or the additional information.

I have run your testcase and I see only a one-character string in the inspector. But I am currently running with a single-byte character set. I will report more when I reset my Oracle parameters to UTF8 and rerun the test.

[wembley]

Jacques -

Can you modify the method you showed to use 286 (which is the value of OCI_ATTR_CHAR_SIZE) in place of OCI_ATTR_DATA_SIZE and tell me if it fixes your problem?

[Jacques]

John,

286 fixes the problem! Thank you.

Jacques

[wembley]

Jacques -

Great! I've opened Case 32394 for the change and will roll the fix into the next update. Thank you for your help in improving the quality of the VA Smalltalk product.