Page 1 of 1
ODAC Fixed Char Field Bug on 64bit Solaris Oracle
Posted: Mon 02 Oct 2006 13:19
by paulzip
If I define a stored function (or packaged function) which returns a char type, and I write a query which calls this stored function as a column, the corresponding ODAC run time field is sized as 4000 bytes. This is understandable as ODAC has defaulted to its max char column size. However, as this causes DBGrids to appear with huge columns (Column width of 24000). To workaround this we have always uses "substr" to limit these columns to their correct size.
Here is a simple example of what I mean:
create package P_Test is
subtype TCharOne is char(1);
function CHAR_TEST return TCharOne;
end;
/
create package body P_Test is
function CHAR_TEST return TCharOne is
vResult TCharOne;
begin
return 'P';
end;
end;
/
select substr(P_Test.CHAR_TEST, 1, 1) as CHAR_TEST from dual
Using this technique char columns are returned as the size we want (e.g. 1 char), and this has worked for all 32 bit servers we have on both Windows and Unix (8i, 9i, 10g), however one of our customers has a 64 bit Oracle on Solaris and the application is not working correctly.
Details from v$version:
Oracle9i Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
On this 64 bit server, all ODAC field sizes using this substr technique are being returned as 4000 bytes. This is a major problem for us as our DBGrids are now totally messed up. Our applications are large (some have 400+ forms) and so we can not feasibly manually create persistent fields to for all bug cases to size these correctly.
The following observations have been made:
1. Stored functions of char based return type TABLE.COLUMN%TYPE are treated as 4000 bytes on both 32 bit and 64 bit Oracle.
2. Table columns of char based return type are returned as correct size.
3. Oracle itself returns correct size, i.e. If I use VSIZE from SQL Plus it returns 1...
select VSIZE(substr(P_Test.CHAR_TEST, 1, 1)) from dual
4. This problem occurs on ODAC 4.x and ODAC 5.x
Can you help us solve this problem?
Paul
Posted: Tue 03 Oct 2006 13:52
by Challenger
OCI returns 4000 for the CHAR_TEST column so we can't change this behavior. You can also try to use CAST function or create corresponding view.
Posted: Tue 03 Oct 2006 16:54
by paulzip
There could be some confusion as to what I mean. Taking this example :
select
CHAR_TEST,
substr(CHAR_TEST, 1, 1) as CHAR_TEST_SUBSTR
from
dual
CHAR_TEST :
For all versions of Oracle, ODAC creates a run time field of size 4000. I am aware of this!
CHAR_TEST_SUBSTR :
For all 32 bit versions, ODAC creates a run time field of size 1,
For v9 64 bit Solaris, ODAC creates a run time field size 4000 !!!
As for using CAST...we cannot go through 400 datamodules rewriting 1000s of queries just for one version of Oracle.
The bottom line is, such behaviour is inconsistent and therefore seems to be a bug in either the OCI or ODAC. This bug is causing us horrendous problems in DBGrids and we need to find a solution!
Paul
Posted: Fri 06 Oct 2006 11:39
by paulzip
I've had no comment from Core Lab regarding my last post. Can you tell me if this inconsistency is due to a bug in ODAC or Oracle OCI?
Also, can you suggest a course of action to remedy this bug, as I am under increasing pressure to find an easy solution.
Paul
Posted: Fri 06 Oct 2006 12:13
by Challenger
Sorry for delay. We were investigating this problem. As we wrote in our previous post this length is returned by Oracle. And Oracle 64 bit returns wrong length value. So this is not bug of ODAC, this is bug of Oracle.
Posted: Fri 06 Oct 2006 16:25
by paulzip
Thank you very much for your reply!
I will have to log a bug report with Oracle. Is it possible you can tell me the OCI call which is returning the inconsistent result, and perhaps a little example of how the OCI call is made, so I can demonstrate the problem to them?
Thanking you kindly
Paul
Posted: Wed 11 Oct 2006 10:34
by paulzip
Can you please reply to my previous posting, i.e. requests for the OCI call that is giving the inconsistent result and an example of OCI call usage.
This is dragging on, and I am really under pressure from a customer to get this issue resolved, so I desperately need to get it logged with Oracle as a bug.
Paul
Posted: Wed 11 Oct 2006 11:32
by Challenger
Example:
Code: Select all
uses
OraCall;
const
sql: PChar = 'select substr(P_Test1.CHAR_TEST1, 1, 1) from dual';
var
hSvcCtx : pOCISvcCtx;
hServer : pOCIServer;
hSession : pOCISession;
hStmt: pOCIStmt;
hDefine: pOCIDefine;
hParam: pOCIParam;
ValuePtr: Integer;
begin
InitOCI;
Check(OCIHandleAlloc(hOCIEnv, hSvcCtx, OCI_HTYPE_SVCCTX, 0, nil));
Check(OCIHandleAlloc(hOCIEnv, hServer, OCI_HTYPE_SERVER, 0, nil));
Check(OCIHandleAlloc(hOCIEnv, hSession, OCI_HTYPE_SESSION, 0, nil));
Check(OCIServerAttach(hServer, hOCIError, 'SERVERNAME', 8, OCI_DEFAULT));
Check(OCIAttrSet1(hSvcCtx, OCI_HTYPE_SVCCTX, hServer, 0, OCI_ATTR_SERVER, hOCIError));
Check(OCIAttrSet3(hSession, OCI_HTYPE_SESSION, 'USERNAME', Length('USERNAME'), OCI_ATTR_USERNAME, hOCIError));
Check(OCIAttrSet3(hSession, OCI_HTYPE_SESSION, 'PASSWORD', Length('PASSWORD'), OCI_ATTR_PASSWORD, hOCIError));
Check(OCISessionBegin(hSvcCtx, hOCIError, hSession, OCI_CRED_RDBMS, OCI_DEFAULT));
Check(OCIAttrSet1(hSvcCtx, OCI_HTYPE_SVCCTX, hSession, 0, OCI_ATTR_SESSION, hOCIError));
Check(OCIHandleAlloc(hOCIEnv, hStmt, OCI_HTYPE_STMT, 0, nil));
Check(OCIStmtPrepare(hStmt, hOCIError, sql, length(sql), OCI_NTV_SYNTAX, OCI_DEFAULT));
Check(OCIStmtExecute(hSvcCtx, hStmt, hOCIError, 0, 0, nil, nil, OCI_DEFAULT));
Check(OCIParamGet(hStmt, OCI_HTYPE_STMT, hOCIError, hParam, 1));
Check(OCIAttrGet2(hParam, OCI_DTYPE_PARAM, ValuePtr, nil, OCI_ATTR_DATA_SIZE, hOCIError));
ShowMessage(IntToStr(sb2(ValuePtr)));
Check(OCIHandleFree(hStmt, OCI_HTYPE_STMT));
end;
Posted: Fri 27 Oct 2006 10:49
by paulzip
According to Oracle, this is not a bug in OCI, but a bug in the way you are calling the OCI and the OCI types you are mapping to.
Please read :
https://metalink.oracle.com/metalink/pl ... ,helvetica
Doc id 332084.1
https://metalink.oracle.com/metalink/pl ... ,helvetica
I still really need this fixed in version 4.x and 5.x of ODAC.
Paul
Posted: Mon 30 Oct 2006 14:27
by Challenger
We currently have no access to Oracle Metalink. Please send us these messages.
Posted: Tue 31 Oct 2006 14:30
by paulzip
Bug No. 4566644
Filed 22-AUG-2005 Updated 23-AUG-2005
Product Oracle Server - Enterprise Edition Product Version 9.2.0.5
Platform Solaris Operating System (SPARC 32-bit) Platform Version 5.8
Database Version 9.2.0.5 Affects Platforms Generic
Severity Severe Loss of Service Status Closed, Not a Bug
Base Bug N/A Fixed in Product Version No Data
Problem statement:
OCIATTRGET REPORTING WRONG DATATYPE SIZE FOR OCI_ATTR_DATA_SIZE
*** 08/22/05 01:32 pm ***
*** 08/22/05 01:32 pm ***
PROBLEM STATEMENT:
------------------
On Solaris, a call to OCIAttrGet is reporting the incorrect data size for all
.
queries. The datasize is being reported anywhere from 400K to 1Million.
The same code works fine on Windows
.
.
.
On Windows this problem does not occur.
.
On Solaris, you get incorrect values for the size of the columns.
.
*** 08/22/05 01:32 pm ***
*** 08/22/05 01:32 pm ***
*** 08/22/05 01:32 pm ***
*** 08/22/05 01:32 pm ***
*** 08/22/05 01:50 pm ***
*** 08/23/05 01:02 am *** (CHG: Asg->NEW OWNER)
*** 08/23/05 04:53 am *** (CHG: Sta->32)
*** 08/23/05 04:53 am ***
Whenever you get the right result on a byte swapped platform but silly
numbers on others you know you've used the wrong datatype for the variable.
.
Testcase is not on ess30. Looking on T+D machine. In SizeTest.cpp, the
variable holding the byte size is ColumnLength which is declared as ub4.
However it should be a ub2 as shown in the Describing Schema Metadata chapter
in the oci manual. Please correct the datatype and retest.
*** 08/23/05 06:14 pm *** (CHG: Sta->92)
*** 08/23/05 06:14 pm ***
Modified the program as described in
Oracle Call Interface Programmer's Guide Release 2 (9.2) Part Number
A96584-01
.
Notes on Types and Attributes
OCI_ATTR_DATA_TYPE returns typecodes which represent the datatypes stored in
database columns. These are similar to the describe values returned by
previous versions of Oracle. These values are represented by SQLT constants
(ub2 values). BOOLEAN types return SQLT_BOL.
.
New code is as follows:
/* ub4 col_name_len, ColumnLength=0, char_semantics, col_char_width; */
ub4 col_name_len, char_semantics, col_char_width;
ub2 i=0, ColumnLength=0;
.
This has resolved the issue.
As stated, this is not a BUG, but a coding problem.
.
Test Results now on Solaris are:
sizetest
datatype is 1
Column name is USERNAMEUSER_ID
Column name length: 8
the column length = 30
the datasize is 30
-------------------------------------------------
datatype is 2
Column name is USER_ID
Column name length: 7
the column length = 0
the datasize is 22
-------------------------------------------------
datatype is 12
Column name is CREATED
Column name length: 7
the column length = 0
the datasize is 7
-------------------------------------------------
===============================================
Subject: OCIAttrGet Reporting Wrong Datatype Size For OCI_ATTR_DATA_SIZE
Doc ID: Note:332084.1 Type: PROBLEM
Last Revision Date: 07-APR-2006 Status: PUBLISHED
In this Document
Symptoms
Cause
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.5
Solaris Operating System (SPARC 64-bit)
Solaris Operating System (SPARC 32-bit)
Symptoms
On Solaris, you get incorrect values for the size of the columns.
On Windows and Linux this problem does not occur.
NOTE: this problem can occur on any UNIX platform other than Linux (non-byte swapped platform).
Example of incorrect output:
datatype is 1
Column name is USERNAMEUSER_ID
Column name length: 8
the column length = 1966080
the datasize is 1966080
With the following OCI code:
ub4 col_name_len, ColumnLength=0, char_semantics, col_char_width;
mStatus=OCIStmtExecute(mpOciService,
mpOciStatement, mpOciError, (ub4) 0, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
OCI_DESCRIBE_ONLY);
mStatus=OCIAttrGet((dvoid*) colhd,
(ub4) OCI_DTYPE_PARAM, (dvoid *) &ColumnLength,
(ub4 *) 0,
(ub4) OCI_ATTR_DATA_SIZE , (OCIError *) mpOciError);
Cause
The cause is that the type ub4 was used for the columnlength, but should have been ub2.
Here is an extract from the OCI documentation:
Oracle Call Interface Programmer's Guide
Notes on Types and Attributes
OCI_ATTR_DATA_TYPE returns typecodes which represent the datatypes stored in database columns. These are similar to the describe values returned by previous versions of Oracle. These values are represented by SQLT constants (ub2 values). BOOLEAN types return SQLT_BOL.
Note on Implicit and Explicit Describes
The column attribute OCI_ATTR_PRECISION can be returned using an implicit describe with OCIStmtExecute() and an explicit describe with OCIDescribeAny(). When using an implicit describe, the precision should be set to sb2. When using an explicit describe, the precision should be set to ub1 for a placeholder. This is necessary to match the datatype of precision in the dictionary.
Table: Attributes Belonging to Type Attributes
Attribute Description Attribute/Datatype
OCI_ATTR_DATA_TYPE The data type of the type attribute. See "Note on Datatype Codes". ub2
Solution
To implement the solution, please execute the following steps:
1. Modify the code as follows:
/* ub4 col_name_len, ColumnLength=0, char_semantics, col_char_width; */
/* ColumnLength must not be ub4 */
ub4 col_name_len, char_semantics, col_char_width;
ub2 i=0, ColumnLength=0;
2. Rebuild the application and it will work.
References
Bug 4566644 - Ociattrget Reporting Wrong Datatype Size For Oci_Attr_Data_Size
Keywords
'DATATYPE'
Help us improve our service. Please email us your comments for this document. .
Posted: Thu 02 Nov 2006 08:38
by Challenger
In the example we have posted before the variable that stores column size is declared as integer(sb4). But before displaying the result we convert it to sb2 value. Please try to compile our example and test it on Windows and Solaris servers. So we suppose that this can be a mistake when converting our Pascal example to C code.
Posted: Tue 28 Nov 2006 16:45
by paulzip
Your example OCI test code does not work with ODAC 4.5.x.x which is what I have installed in my development environment. Can you please include example testcode which will work on this version.
That aside, looking at your example - it does seem that you are using the incorrect type for column size as the metalink shows. Perhaps this is why ODAC is showing this bug?
I am still desperate for a fix for this problem!
Paul
Posted: Wed 29 Nov 2006 15:28
by Challenger
This example is compatible with ODAC 4.50:
Code: Select all
const
sql: PChar = 'select substr(P_Test1.CHAR_TEST1, 1, 1) from dual';
var
hSvcCtx : pOCISvcCtx;
hServer : pOCIServer;
hSession : pOCISession;
hStmt: pOCIStmt;
hDefine: pOCIDefine;
hParam: pOCIParam;
ValuePtr: Integer;
begin
OracleHome := ohHome1;
InitOCI;
Check(OCIHandleAlloc(hOCIEnv, @hSvcCtx, OCI_HTYPE_SVCCTX, 0, nil));
Check(OCIHandleAlloc(hOCIEnv, @hServer, OCI_HTYPE_SERVER, 0, nil));
Check(OCIHandleAlloc(hOCIEnv, @hSession, OCI_HTYPE_SESSION, 0, nil));
Check(OCIServerAttach(hServer, hOCIError, PChar('SERVER'), Length('SERVER'), OCI_DEFAULT));
Check(OCIAttrSet(hSvcCtx, OCI_HTYPE_SVCCTX, hServer, 0, OCI_ATTR_SERVER, hOCIError));
Check(OCIAttrSet(hSession, OCI_HTYPE_SESSION, Pchar('test'), Length('test'), OCI_ATTR_USERNAME, hOCIError));
Check(OCIAttrSet(hSession, OCI_HTYPE_SESSION, PChar('test'), Length('test'), OCI_ATTR_PASSWORD, hOCIError));
Check(OCISessionBegin(hSvcCtx, hOCIError, hSession, OCI_CRED_RDBMS, OCI_DEFAULT));
Check(OCIAttrSet(hSvcCtx, OCI_HTYPE_SVCCTX, hSession, 0, OCI_ATTR_SESSION, hOCIError));
Check(OCIHandleAlloc(hOCIEnv, @hStmt, OCI_HTYPE_STMT, 0, nil));
Check(OCIStmtPrepare(hStmt, hOCIError, sql, length(sql), OCI_NTV_SYNTAX, OCI_DEFAULT));
Check(OCIStmtExecute(hSvcCtx, hStmt, hOCIError, 0, 0, nil, nil, OCI_DEFAULT));
Check(OCIParamGet(hStmt, OCI_HTYPE_STMT, hOCIError, @hParam, 1));
Check(OCIAttrGet(hParam, OCI_DTYPE_PARAM, @ValuePtr, nil, OCI_ATTR_DATA_SIZE, hOCIError));
ShowMessage(IntToStr(sb2(ValuePtr)));
Check(OCIHandleFree(hStmt, OCI_HTYPE_STMT));