0

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
dhananjay sharma
  • 9
  • 1

1 Answers1

0

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
eaolson
  • 14,717
  • 7
  • 43
  • 58
  • i tried this but got same error again "Invalid lob locator specified at line..." but the error is on line following l_clob:='' initialization. my code is dbms_lob.append(l_clob,login_info) – dhananjay sharma Mar 26 '21 at 09:52
  • Show your code. That line is not in what you posted in your question. – eaolson Mar 26 '21 at 22:24
  • l_login-->clob type l_login:='{some json structure for login into REST API}' dbms_lob.createtemporary(l_clob,TRUE); for i in start_batch to end_batch loop l_clob := ''; dbms_lob.append(l_clob,l_login);-->error at this line dbms_lob.append(l_clob,data_from_cursor); call_to_webservice_proc(l_clob,l_response) ...code to generate request object. end loop; dbms_lob.freetemporary(l_clob,TRUE); – dhananjay sharma Mar 30 '21 at 09:52
  • The problems starts as soon as you initialize your l_clob variable with '' .do this as first statement in for loop.I want to initialize it so that new data does not get appended on old one. – dhananjay sharma Mar 31 '21 at 07:54
  • I cant use || operator as data i am apending is going to be of clob type.|| works find till the size of data is les than or equals that of varchar beyond that it does not works we have to use append method. – dhananjay sharma Apr 03 '21 at 06:15