There's no probelm when receiving clob data but sending clob data to Oracle in TOraObject by calling a Stored Prodecure

[helppass]

Hello there.

There's no probelm when receiving clob data but sending clob data to Oracle in TOraObject by calling a Stored Prodecure.
I am using Delphi 7 and ODAC 5.70.028 on Windows XP and Oracle 10g eXpress(international).


I have a lot of stored-procedures which passing main variables all by a TOraObject. and I found that I can read clob in
in TOraObject by calling a Stored Prodecure,but when I call stored-procedures and try to send a long string as clob,
it won't be right but always 0x8148(unicode ?).


In PL/SQL, I built 2 types:

Code: Select all

CREATE OR REPLACE TYPE BT_SUB_DATA AS OBJECT
(
	ASTRING VARCHAR2(60),
	ACLOB CLOB 
);

CREATE OR REPLACE TYPE BT_MAIN_QUERY AS OBJECT
(
	QUERYFLAG VARCHAR2(4),
	QUERYVALUE VARCHAR2(20),
	SINGLEDATA BT_SUB_DATA
);

and prepared such a package

Code: Select all

PROCEDURE MAIN_QUERY(vo_Main_Query IN OUT BT_MAIN_QUERY, vo_OperationStatus OUT NUMBER, vo_OperationStatusDesc OUT VARCHAR2) AS
 
 BEGIN
  
   insert into TESTCLOB(clob_id, CLOB_DATA) values(testid.nextval, vo_Main_Query.SINGLEDATA.ACLOB);
   
   SELECT BT_SUB_DATA(
                      '22', 
                      CLOB_DATA
                     ) 
     INTO vo_Main_Query.SINGLEDATA
     FROM TESTCLOB t
    WHERE t.CLOB_ID = 1;

   vo_OperationStatus := 0;
   vo_OperationStatusDesc := '';

 EXCEPTION
  WHEN OTHERS THEN
   vo_OperationStatus := 109521;
   vo_OperationStatusDesc := SQLERRM;
 END;

In delphi, I use this below.

Code: Select all

// TOraObject2SimpleTObject

Class Function TSub_DataOraEx.SaveToOraObject(AOraSession: TOraSession; ABO: TSub_Data; BaseObject: TOraObject = Nil): TOraObject;
....
 Begin

  If BaseObject = Nil Then
   Begin
    AOraType := TOraType.Create(AOraSession.OCISvcCtx, 'BT_SUB_DATA');
    AOraObject := TOraObject.Create(AOraType);
    AOraType.Free;
   End
  Else
   AOraObject := BaseObject;

  With AOraObject Do
   Begin
   ...
    If ABO.ACLOB  '' Then 
     Begin
      AttrAsLob['ACLOB'].CreateTemporary(ltClob);
      AttrAsLob['ACLOB'].IsUnicode := True;
      AttrAsLob['ACLOB'].AsWideString := ABO.ACLOB;
      AttrAsLob['ACLOB'].WriteLob;
     End;

// this always make the wrong results, and view inserted clob by PL/SQL text or hex mode, 
// it always be ???(0x8148), and half of origin data length,
// not only japanese charactors makes the bad results, but also simple string as "ABC123"!

   End;

  Result := AOraObject;
 End;
....

Class Procedure TSub_DataOraEx.LoadFromOraObject(AOraObject: TOraObject; var ABO: TSub_Data);
...
 Begin

  With AOraObject Do
   Begin
    Refresh;
    ...
    s := '';
    If Not AttrIsNull['ACLOB'] Then // this always get true result of my prepared a japanese issue
     Try
      a := AttrAsLob['ACLOB'].LengthLob;
      If a > 0 Then
       Begin
        t := TMemoryStream.Create;
        AttrAsLob['ACLOB'].SaveToStream(t);
        t.Seek(0, soFromBeginning);
        SetLength(s, t.size);
        t.ReadBuffer(s[1], t.size);
        t.Free;
       End;
     Except  
     End;
    ABO.ACLOB := s;
   End;

 End;

...
// call sp to execute 

   AOraQuery.SQL.Text := 'BEGIN MYTEST.MAIN_QUERY(:BO, :OperationStatus, :OperationStatusDesc); END;';
   AOraObject := TMain_QueryOraEx.SaveToOraObject(AOraSession, AOraEx);
   AOraQuery.ParamByName('BO').AsObject := AOraObject;
   AOraQuery.ParamByName('OperationStatus').AsInteger := 0;
   AOraQuery.ParamByName('OperationStatusDesc').AsString := '';
   AOraQuery.Execute;

   TMain_QueryOraEx.LoadFromOraObject(AOraQuery.ParamByName('BO').AsObject, AOraEx);
   AOraEx.OperationStatus := AOraQuery.ParamByName('OperationStatus').AsInteger;
   AOraEx.OperationStatusDesc := AOraQuery.ParamByName('OperationStatusDesc').AsString;

however, if I pass strings as array of VARCHAR2 in Object by calling the stored procedure, I can get the right answer...
Any suggestions ? What can be the problem ??

Thanks for helping.

[helppass]

and, when testing this case on Oracle9iR2 which default page is JIS

1. use NCLOB,
got the same result of unicode ???

2. use CLOB
1) IsUnicode = True,
half length of the original data, '112233' becomes '112';

and when giving huge data, characters turns bad(unicode?) from stream position 32768 to 65535, length=32K, again and again.

2) IsUnicode = False,
got the same result of unicode ???

...

[Plash]

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next ODAC build.