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
