Home » Developer & Programmer » JDeveloper, Java & XML » Another question: how to free temporary lob when using store procedure
Another question: how to free temporary lob when using store procedure [message #243651] Fri, 08 June 2007 03:56
leilss
Messages: 2
Registered: June 2007
Junior Member
want to write a generic program to call store procedure, making the caller no need to care about how to free the temporary lobs.

My rule is:

for the input, if type is IN, store the temporary lob in a list, and free it before commit or rollback the transaction.

for the output, free the out lob if it's a temporary lob by test it using oracle.sql.BLOB.isTempopary().

My question is, how to free the INPUT lob type of the store procedure?

for the input, if the store procedure like this:


create or replace procedure DP_TESTRDBMSCONNECTORS(
pid in int,
ppic in out BLOB
) AS
g_blob BLOB:=NULL;
begin
insert into SP_TEST(id, pic) values (pid,ppic);
select pic into g_blob from sp_test where id =pid and rownum<=1;
ppic:=g_blob;
end;


then I can't free it, otherwise, I'll get InvocationTargetException. If I don't free it, the unfreed lob exists if the store procedure like this:

create or replace procedure DP_TESTRDBMSCONNECTORS(
pid in int,
ppic in out BLOB
) AS
g_blob BLOB:=NULL;
begin
DBMS_LOB.CREATETEMPORARY(ppic,FALSE,DBMS_LOB.CALL);
insert into SP_TEST(id, pic) values (pid,EMPTY_BLOB()) returning pic into g_blob;
end;

Can you help me?
Previous Topic: Why this Exception: duration is invalid for this function
Next Topic: JDBC and query as command-line argument
Goto Forum:
  


Current Time: Thu Dec 12 23:37:50 CST 2024