4

I have huge Oracle function which is used to calculate data from 6 tables.

create or replace FUNCTION STATISTICS_FUNCTION(NAMEIN IN VARCHAR2

)
RETURN CLOB
AS
    LAST_60_CPU NUMBER;
    .............

    LINE CLOB;

    CURSOR LAST_60_CPU_CURSOR IS
     .................

BEGIN

    LINE := EMPTY_CLOB();
    DBMS_LOB.CREATETEMPORARY(LINE,true);
  OPEN LAST_60_CPU_CURSOR;
   LOOP
      FETCH LAST_60_CPU_CURSOR INTO LAST_60_EVENT_DATE, LAST_60_CPU;
      ....................
      DBMS_LOB.APPEND(LINE, TO_CHAR(LAST_60_EVENT_DATE));
      DBMS_LOB.APPEND(LINE, 'I');
      DBMS_LOB.APPEND(LINE, TO_CHAR(LAST_60_CPU));
      DBMS_LOB.APPEND(LINE, CHR(10));
   END LOOP;
   CLOSE LAST_60_CPU_CURSOR;
   DBMS_LOB.APPEND(LINE, 'LAST_60_CPU');
   DBMS_LOB.APPEND(LINE, CHR(10));
.......................................................

-------------------------------------
DBMS_OUTPUT.PUT_LINE(LINE);

  RETURN LINE;
END STATISTICS_FUNCTION;

I use this Java code to call the function:

public void callFunction() throws SQLException
{
// initialize the driver and try to make a connection

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "admin", "qwerty");

// prepareCall uses ANSI92 "call" syntax
CallableStatement cstmt = conn.prepareCall("{? = call AGENT_STATISTICS_FUNCTION(?)}");

// get those bind variables and parameters set up
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.setString(2, "agent");

// now we can do it, get it, close it, and print it
cstmt.execute();
String result = cstmt.getString(1);
conn.close();
System.out.println(result);
}

I tired to call the function without this line:

DBMS_LOB.CREATETEMPORARY(LINE,true);

But I get error:

Connecting to the database local.
ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 639
ORA-06512: at "ADMIN.STATISTICS_FUNCTION", line 596
ORA-06512: at line 7
Process exited.
Disconnecting from the database local.

Do you have any idea why I get this error without DBMS_LOB.CREATETEMPORARY(LINE,true);?

Peter Penzov
  • 1,126
  • 134
  • 430
  • 808

2 Answers2

12

Do you have any idea why I get this error without DBMS_LOB.CREATETEMPORARY(LINE,true);?

Yes. A LOB is a pointer/reference to a memory/disk storage. You need to "memalloc()" (... initialize) the storage first, assign the pointer/reference to your LOB variable. That's what dbms_lob.createTemporary() is for. Unless you initialize a LOB variable with a valid LOB locator, all your operations on that LOB variable will fail with ORA-22275: invalid LOB locator specified.

Enhancement: Have your PL/SQL function refactored a bit: (And please note that I used a dummy query for the last_60_cpu_cursor cursor. Do not reuse the cursor, use your own! :-))

create or replace
function statistics_function
    ( namein                        in varchar2 )
    return clob
is
    line                            clob;
    cursor last_60_cpu_cursor       is
        select 1 as last_60_cpu, sysdate as last_60_event_date
        from dual
    ;
begin
    dbms_lob.createtemporary(lob_loc => line, cache => true, dur => dbms_lob.call);

    for cv in last_60_cpu_cursor loop
        dbms_lob.append(line, to_char(cv.last_60_event_date)||'i'||to_char(cv.last_60_cpu)||chr(10));
    end loop;

    dbms_lob.append(line, 'last_60_cpu'||chr(10));

    return line;
end statistics_function;
  1. You don't need to open+fetch+close the cursor. A regular cursor-loop will do just fine (if not even better, thanks to the implicit bulk-fetching under the hoods).
  2. Explicitly declare the temporary LOB as cached (cache => true; as you already have). This ensures data chunks are added to the LOB in memory, instead of being added on disk (cache => false).
  3. Concatenate the strings to be appended to the LOB so as to minimize the number of calls to the dbms_lob.append().
  4. Remove the dbms_output.put_line() from your function. In case of LOB contents larger than 32K, this would throw an exception anyway.

Also, after you're done with delivering the LOB back to your Java env., free the temporary LOB. (I'm not a Java guy, can't write the Java code snippet myself.)

Also, you have a conceptual error in your Java code; registering the return of the function as Types.VARCHAR is wrong. You should rather use the Oracle's dedicated CLOB type. (I've seen those in C#, Java must have them too.)

Also, there's one performance issue with your solution. Your function returns a LOB. In PL/SQL, each function value is returned to its caller as a deep copy of the inside value. Hence, if you return a LOB from a function, the LOB contents are duplicated in the background with a new LOB locator(/pointer/reference). You should use You may consider using a stored procedure instead of a function and pass the LOB to Java as an out nocopy parameter. The stored proc would then look like this:

create or replace
procedure statistics_function
    ( namein                        in varchar2
    , lob_out                       out nocopy clob )
is
    cursor last_60_cpu_cursor       is
        select 1 as last_60_cpu, sysdate as last_60_event_date
        from dual
    ;
begin
    dbms_lob.createtemporary(lob_loc => lob_out, cache => true, dur => dbms_lob.session);

    for cv in last_60_cpu_cursor loop
        dbms_lob.append(lob_out, to_char(cv.last_60_event_date)||'i'||to_char(cv.last_60_cpu)||chr(10));
    end loop;

    dbms_lob.append(lob_out, 'last_60_cpu'||chr(10)||chr(10));
end statistics_function;

How would your Java call look, is up to you and JDBC doc; but, for sure, a LOB returned this way would mean no background contents copying. Of course, the need for freeing the allocated temporary LOB still applies.

peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34
  • Some JDBC doc links added to the answer. Hopefully the correct ones. :-) – peter.hrasko.sk Nov 19 '15 at 10:49
  • 1
    are you sure, that this return value copying also applies to LOB? There is also DBMS_LOB.ASSIGN which clones a locator, but not a LOB. PS: this topic is not 100% covered by Orale's docs. for example freeing of temporary lobs works differently if they were allocated vie PL/SQL versus allocated via JDBC. – ibre5041 Nov 19 '15 at 11:05
  • I'm almost 100% certain about the copying; but I may give it some more thinking later these days. – peter.hrasko.sk Nov 19 '15 at 11:17
  • @ibre5041, no, it's not being deep-copied. Answer corrected. – peter.hrasko.sk Nov 13 '18 at 07:05
2

CLOB is something like a file handle. Therefore Oracle calls it sometimes a lob locator. It must either point into table's data segment(lob segment) or to a temporary tablespace. LOBs can have up to 176TB, therefore they can not be kept in database server RAM nor in Java JVM heap.

Sometimes implicit conversion from/to VARCHAR2 works, so it might seem that CLOB is variable like any other. You allways should check CLOB code with texts exceeding 32KB.

PS: you should also call freetemporary() explicitly at right place. Java GC does not talk to database engine and therefore the database does not know when the TEMP space can be freed.

UPDATE1: You should call either oracle.sql.CLOB.freetemporary() or java.sql.BLOB.free or BEGIN DBMS_LOB.CREATETEMPORARY(:CLOB,true); END; after you process data from called procedure. You cal also re-use the locator foe each call (unless you use commit on the connection). I recall I had some problems with freeing LOB locators allocated via PL/SQL using JDBC API.

UPDATE2: you will probably need DBA privs and also access to db server to start session trace. To start and stop session trace execute:

exec dbms_monitor.session_trace_enable(session_id=>X,serial_num=>Y,binds=>true,waits=>true);
exec dbms_monitor.session_trace_disable(session_id=>X,serial_num=>Y);

Where X and Y are session identifiers SID and SERIAL# from v$session view.

This is how session trace looks like when creating temporary lob of size 65KB with parameter cache set to false:

WAIT #0: nam='Disk file operations I/O' ela= 277 FileOperation=2 fileno=201 filetype=2 obj#=-1 tim=1448362135289035
LOBTMPCREATE: c=1000,e=689,p=0,cr=0,cu=2,tim=1448362135289171
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1448362135289218
WAIT #0: nam='SQL*Net message from client' ela= 2594 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1448362135291842
WAIT #0: nam='SQL*Net more data from client' ela= 20 driver id=1413697536 #bytes=32 p3=0 obj#=-1 tim=1448362135292339
WAIT #0: nam='SQL*Net more data from client' ela= 796 driver id=1413697536 #bytes=83 p3=0 obj#=-1 tim=1448362135293233
WAIT #0: nam='SQL*Net more data from client' ela= 18 driver id=1413697536 #bytes=65 p3=0 obj#=-1 tim=1448362135293361
WAIT #0: nam='SQL*Net more data from client' ela= 16 driver id=1413697536 #bytes=27 p3=0 obj#=-1 tim=1448362135293449
WAIT #0: nam='SQL*Net more data from client' ela= 749 driver id=1413697536 #bytes=30 p3=0 obj#=-1 tim=1448362135294243
WAIT #0: nam='SQL*Net more data from client' ela= 301 driver id=1413697536 #bytes=12 p3=0 obj#=-1 tim=1448362135294623
WAIT #0: nam='SQL*Net more data from client' ela= 35 driver id=1413697536 #bytes=22 p3=0 obj#=-1 tim=1448362135294786
WAIT #0: nam='SQL*Net more data from client' ela= 16 driver id=1413697536 #bytes=189 p3=0 obj#=-1 tim=1448362135294866
WAIT #0: nam='SQL*Net more data from client' ela= 10 driver id=1413697536 #bytes=103 p3=0 obj#=-1 tim=1448362135294913
WAIT #0: nam='SQL*Net more data from client' ela= 9 driver id=1413697536 #bytes=17 p3=0 obj#=-1 tim=1448362135294955
WAIT #0: nam='SQL*Net more data from client' ela= 697 driver id=1413697536 #bytes=184 p3=0 obj#=-1 tim=1448362135295685
WAIT #0: nam='SQL*Net more data from client' ela= 16 driver id=1413697536 #bytes=98 p3=0 obj#=-1 tim=1448362135295801
WAIT #0: nam='SQL*Net more data from client' ela= 21 driver id=1413697536 #bytes=12 p3=0 obj#=-1 tim=1448362135296189
WAIT #0: nam='SQL*Net more data from client' ela= 12 driver id=1413697536 #bytes=179 p3=0 obj#=-1 tim=1448362135296274
WAIT #0: nam='SQL*Net more data from client' ela= 10 driver id=1413697536 #bytes=93 p3=0 obj#=-1 tim=1448362135296344
WAIT #0: nam='CSS initialization' ela= 7307 p1=0 p2=0 p3=0 obj#=-1 tim=1448362135303779
WAIT #0: nam='CSS operation: action' ela= 2479 function_id=65 p2=0 p3=0 obj#=-1 tim=1448362135306327
WAIT #0: nam='Disk file operations I/O' ela= 823 FileOperation=2 fileno=0 filetype=15 obj#=-1 tim=1448362135307307
WAIT #0: nam='CSS initialization' ela= 22 p1=0 p2=0 p3=0 obj#=-1 tim=1448362135307865
WAIT #0: nam='CSS operation: query' ela= 5 function_id=42 p2=0 p3=0 obj#=-1 tim=1448362135307914
WAIT #0: nam='CSS operation: query' ela= 1 function_id=42 p2=0 p3=0 obj#=-1 tim=1448362135307932
WAIT #0: nam='CSS operation: query' ela= 1 function_id=42 p2=0 p3=0 obj#=-1 tim=1448362135307947
WAIT #0: nam='CSS operation: query' ela= 1 function_id=42 p2=0 p3=0 obj#=-1 tim=1448362135307963
WAIT #0: nam='CSS operation: query' ela= 6 function_id=33 p2=0 p3=0 obj#=-1 tim=1448362135307986
WAIT #0: nam='CSS operation: query' ela= 612 function_id=39 p2=0 p3=0 obj#=-1 tim=1448362135308625
WAIT #0: nam='CSS operation: action' ela= 2589 function_id=65 p2=0 p3=0 obj#=-1 tim=1448362135311258
WAIT #0: nam='direct path write temp' ela= 1373 file number=201 first dba=927747 block cnt=16 obj#=-1 tim=1448362135313337
LOBWRITE: c=9998,e=21487,p=0,cr=0,cu=61,tim=1448362135313441
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1448362135313499
WAIT #0: nam='SQL*Net message from client' ela= 3187 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1448362135316722

As you can see it contains direct path write into temp tablespace. This is how if looks like when using cache set to true.

WAIT #0: nam='Disk file operations I/O' ela= 267 FileOperation=2 fileno=201 filetype=2 obj#=-1 tim=1448363565002340
LOBTMPCREATE: c=0,e=650,p=0,cr=0,cu=2,tim=1448363565002469
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1448363565002515
WAIT #0: nam='SQL*Net message from client' ela= 2424 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1448363565004970
WAIT #0: nam='SQL*Net more data from client' ela= 17 driver id=1413697536 #bytes=32 p3=0 obj#=-1 tim=1448363565005390
WAIT #0: nam='SQL*Net more data from client' ela= 975 driver id=1413697536 #bytes=83 p3=0 obj#=-1 tim=1448363565006434
WAIT #0: nam='SQL*Net more data from client' ela= 21 driver id=1413697536 #bytes=65 p3=0 obj#=-1 tim=1448363565006545
WAIT #0: nam='SQL*Net more data from client' ela= 621 driver id=1413697536 #bytes=27 p3=0 obj#=-1 tim=1448363565007210
WAIT #0: nam='SQL*Net more data from client' ela= 337 driver id=1413697536 #bytes=30 p3=0 obj#=-1 tim=1448363565007648
WAIT #0: nam='SQL*Net more data from client' ela= 20 driver id=1413697536 #bytes=12 p3=0 obj#=-1 tim=1448363565007795
WAIT #0: nam='SQL*Net more data from client' ela= 18 driver id=1413697536 #bytes=22 p3=0 obj#=-1 tim=1448363565007925
WAIT #0: nam='SQL*Net more data from client' ela= 10 driver id=1413697536 #bytes=189 p3=0 obj#=-1 tim=1448363565007983
WAIT #0: nam='SQL*Net more data from client' ela= 555 driver id=1413697536 #bytes=103 p3=0 obj#=-1 tim=1448363565008576
WAIT #0: nam='SQL*Net more data from client' ela= 21 driver id=1413697536 #bytes=17 p3=0 obj#=-1 tim=1448363565008749
WAIT #0: nam='SQL*Net more data from client' ela= 10 driver id=1413697536 #bytes=184 p3=0 obj#=-1 tim=1448363565008811
WAIT #0: nam='SQL*Net more data from client' ela= 176 driver id=1413697536 #bytes=98 p3=0 obj#=-1 tim=1448363565009038
WAIT #0: nam='SQL*Net more data from client' ela= 23 driver id=1413697536 #bytes=12 p3=0 obj#=-1 tim=1448363565009438
WAIT #0: nam='SQL*Net more data from client' ela= 12 driver id=1413697536 #bytes=179 p3=0 obj#=-1 tim=1448363565009525
WAIT #0: nam='SQL*Net more data from client' ela= 12 driver id=1413697536 #bytes=93 p3=0 obj#=-1 tim=1448363565009607
LOBWRITE: c=3000,e=4660,p=0,cr=0,cu=61,tim=1448363565009692
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1448363565009738
WAIT #0: nam='SQL*Net message from client' ela= 3308 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1448363565013077
ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • Can you please paste a complete example of the code? It turns out that calling the function is very intensive HDD operation? – Peter Penzov Nov 16 '15 at 20:04
  • yes it might be.check database session trace, if HDD i/o is really preformed or not. Database is multi user environment, so it simply can not give you all the RAM for CLOB creation. – ibre5041 Nov 16 '15 at 20:25
  • looking at my code where I need to add freetemporary()? How I ca do session trace? I'm a very basic Oracle user. – Peter Penzov Nov 16 '15 at 21:21
  • If I could upvote multiple times, I would do so. Perfect example of a session trace. – peter.hrasko.sk Nov 25 '15 at 08:22