Hello,
I've read a few messages from people who's having the same problem I am. But I dit not really see the solution to my problem. I'm new to Oracle and new DOA components. Here is the problem:
I use Delphi 5; DOA 4.0.5; Oracle 9.2.0.1.0 client; and Oracle 9.2.0.3.0 server.
In oracle I have a package and a function which takes CLOB inpot parameter. I need to call this function in a loop.
Here is my delphi code:
var pq :TOracleQuery;
cq :TOracleQuery;
LobLoc : TLOBLocator ;
begin
pq := TOracleQuery.Create(nil);
cq := TOracleQuery.Create(nil);
LobLoc:=TLobLocator.CreateTemporary(MyOracleSession, otCLOB, True);
try
pq.Session := MyOracleSession;
cq.Session := MyOracleSession;
cq.Optimize := True;
pq.DeclareVariable( 'function_result', otCursor );
pq.SetComplexVariable( 'function_result', cq );
pq.DeclareVariable( 'P_ITEM_NOTES', otCLOB);
pq.Sql.Add( 'begin' );
pq.Sql.Add( ' :function_result := MY_PKG.MY_FUNCTION(P_ITEM_NOTES =>
_ITEM_NOTES );' );
pq.Sql.Add( 'end;' );
while not MyCondition do
begin
LobLoc.AsString:=MyString;
pq.SetComplexVariable( 'P_ITEM_NOTES', LobLoc);
pq.Execute;
cq.Execute;
md.Next;
end;
finally
pq.free;
cq.free;
LobLoc.Free;
end;
So, finally here is the problem. When it gets to the line LobLoc.AsString:=MyString for the second time in a loop, it gives me the error:ORA-22922: nonexistent LOB value. The first time it goes through just fine.
But, what's really strange, it works perfectly with oracle 10g server version.
Is there are problem in oracle? in DOA component? in my code?
I also tried different things, for example, use SetEmpty method, or create and destroy TLobLocatior inside the loop. But it still did not work with oracle 9 (I was getting invalid LOB specifier error; or TOracle exception with message '' (empty striing)), but it still worked fine with oracle 10g.
I need this to be working in both versions of oracle, if possible.
Please, help.
Thank you.
I've read a few messages from people who's having the same problem I am. But I dit not really see the solution to my problem. I'm new to Oracle and new DOA components. Here is the problem:
I use Delphi 5; DOA 4.0.5; Oracle 9.2.0.1.0 client; and Oracle 9.2.0.3.0 server.
In oracle I have a package and a function which takes CLOB inpot parameter. I need to call this function in a loop.
Here is my delphi code:
var pq :TOracleQuery;
cq :TOracleQuery;
LobLoc : TLOBLocator ;
begin
pq := TOracleQuery.Create(nil);
cq := TOracleQuery.Create(nil);
LobLoc:=TLobLocator.CreateTemporary(MyOracleSession, otCLOB, True);
try
pq.Session := MyOracleSession;
cq.Session := MyOracleSession;
cq.Optimize := True;
pq.DeclareVariable( 'function_result', otCursor );
pq.SetComplexVariable( 'function_result', cq );
pq.DeclareVariable( 'P_ITEM_NOTES', otCLOB);
pq.Sql.Add( 'begin' );
pq.Sql.Add( ' :function_result := MY_PKG.MY_FUNCTION(P_ITEM_NOTES =>
_ITEM_NOTES );' );pq.Sql.Add( 'end;' );
while not MyCondition do
begin
LobLoc.AsString:=MyString;
pq.SetComplexVariable( 'P_ITEM_NOTES', LobLoc);
pq.Execute;
cq.Execute;
md.Next;
end;
finally
pq.free;
cq.free;
LobLoc.Free;
end;
So, finally here is the problem. When it gets to the line LobLoc.AsString:=MyString for the second time in a loop, it gives me the error:ORA-22922: nonexistent LOB value. The first time it goes through just fine.
But, what's really strange, it works perfectly with oracle 10g server version.
Is there are problem in oracle? in DOA component? in my code?
I also tried different things, for example, use SetEmpty method, or create and destroy TLobLocatior inside the loop. But it still did not work with oracle 9 (I was getting invalid LOB specifier error; or TOracle exception with message '' (empty striing)), but it still worked fine with oracle 10g.
I need this to be working in both versions of oracle, if possible.
Please, help.
Thank you.
