Hi all,
I've a problem to initialise 2 CLOB columns in 2 different tables with the same TLobLocator through PL/SQL procedure.
Take a look at this simplified scheme to view the problem.
I have 2 PL/SQL procedures defined like this.
Create or Replace procedure P1 (iID in PLS_INTEGER, ioLOB in out CLOB,...)
as
Begin
Update MYTABLE1
set COL2 = EMPTY_CLOB(),...
where COL1 = iID
returning COL2 into ioLOB;
...
P2(iID, ioLOB);
...
End;
Create or Replace procedure P2 (iNewID in PLS_INTEGER, ioNewLOB in out CLOB,...)
as
Begin
...
Update MYTABLE2
set COL2 = EMPTY_CLOB(),...
where COL1 = iNewID
returning COL2 into ioNewLOB;
...
End;
The corresponding Delphi code is:
Var
Lob: TLobLocator;
...
Begin
...//MyQuery calls P1
Lob := TLobLocator.Create(MyQuery.Session, OtCLOB);
MyQuery.SetComplexVariable('ioLOB', Lob);
MyQuery.Execute;
Lob.AsString := 'Blabla';
MyQuery.Session.Commit;
Lob.Free;
...
End;
In my database, the CLOB column in MYTABLE1 contains '' and the CLOB column in MYTABLE2 contains 'Blabla'.
But, i want 'Blabla' in MYTABLE1 too!
The only workaround i found is to have 2 lob parameters in my query (one for MYTABLE1 et one for MYTABLE2). But, duplicating the CLOB parameter is not good for memory (I have 2 other CLOB parameters in my parameters list of the PL procedure)
Is there any other solution ?
Thanks in advance for your responses.
jleg.