{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 干净又极端 的问题《ORA-22275: invalid LOB locator specified》','https://www.manongdao.com/q-337590.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
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);?
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()orjava.sql.BLOB.freeorBEGIN 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:
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:As you can see it contains direct path write into temp tablespace. This is how if looks like when using cache set to
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 withORA-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_cursorcursor. Do not reuse the cursor, use your own! :-))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).dbms_lob.append().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.VARCHARis 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 useYou may consider using a stored procedure instead of a function and pass the LOB to Java as anout nocopyparameter. The stored proc would then look like this: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.