CODEWITHSUNDEEP
oracle-databasebatch-processingcloblob
dhananjay sharma
dhananjay sharma

Reputation: 9

How to reuse temporary lob in oracle

I am having one program in Oracle PL/SQL.The program does some batch processing ie. it sends data to another system through REST API in batches of fixed number of records. the request and response object are clob and hence i am creating temporary lob and freeing it for each iteration. My question is ,can't i create temp lob once and resuse it for every batch i process and then free it at last only once. Basically i want to bring create and free out of the loop so that it can improve performance and reuse the memory. When i try to bring it outside loop, i will need to initialize clob variable at the start of each iteration, so i tried it using empty_clob() but did not work.Also assigning null does not work. I am getting error as "Invalid lob locator specified at ..." Below is my pseudo code

for i in start_batch to end_batch
loop
dbms_lob.createtemporary(l_clob,TRUE);
...code to generate request object.
dbms_lob.freetemporary(l_clob,TRUE) ;
end loop

Upvotes: 0

Views: 691

Answers (1)

eaolson
eaolson

Reputation: 15094

Huh. I swear that worked, but you are correct. I shouldn't try to remember these things. I guess assigning '' to a clob does set it to null. You can't use a null clob with dbms_lob.append, since it's expecting basically a pointer. Try using the concatenation operator, ||.

I've confirmed this works:

declare
    l_clob clob;
begin
    for i in 1..5 loop
        l_clob := '';
        for j in 1..5 loop
            l_clob := l_clob || 'a';
        end loop;
        dbms_output.put_line(l_clob);
    end loop;
end;

Edit:

I'm not sure it's true that a clob concatenated with a varchar is a varchar and therefore limited to 32 kB. But that does contradict what the documentation says. Take this for example:

declare
    c clob;
begin
    for i in 1..40000 loop
        c := c || 'a';
    end loop;
    dbms_output.put_line('len=' || dbms_lob.getlength(c));
end;

Result:

len=40000

Upvotes: 0

Related Questions