文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >ORA-22275:指定的LOB定位器无效

问ORA-22275:指定的LOB定位器无效
EN

Stack Overflow用户
提问于 2015-11-17 03:04:44
回答 1查看 43.4K关注 0票数 3

我有一个巨大的Oracle函数，用于计算6个表的数据。

代码语言：javascript
运行
复制
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;

我使用下面的Java代码来调用函数：

代码语言：javascript
运行
复制
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);
}

我试着在不使用下面这行代码的情况下调用函数：

代码语言：javascript
运行
复制
DBMS_LOB.CREATETEMPORARY(LINE,true);

但我得到了错误：

代码语言：javascript
运行
复制
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.

你知道为什么在没有DBMS_LOB.CREATETEMPORARY(LINE，true)；的情况下会出现这个错误吗？

oracle
plsql
oracle11g
oracle10g
EN

回答 1

Stack Overflow用户

发布于 2015-11-17 03:57:54

CLOB有点像文件句柄。因此，Oracle有时将其称为lob定位器。它必须指向表的数据段(lob段)或临时表空间。LOB最大可以有176TB，因此它们不能保存在数据库服务器RAM中，也不能保存在Java JVM堆中。

有时候，从VARCHAR2到CLOB的隐式转换是可行的，所以看起来CLOB和其他任何东西一样都是可变的。您应该始终检查文本超过32KB的CLOB代码。

PS:你也应该在正确的地方显式调用freetemporary()。Java GC不与数据库引擎通信，因此数据库不知道何时可以释放临时空间。

UPDATE1:在处理来自被调用过程的数据后，应调用oracle.sql.CLOB.freetemporary()或java.sql.BLOB.free或BEGIN DBMS_LOB.CREATETEMPORARY(:CLOB,true); END;。您还可以为每个调用重用定位器(除非您在连接上使用提交)。我记得我在使用JDBC API释放通过PL/SQL分配的LOB定位器时遇到了一些问题。

UPDATE2:您可能需要DBA privs以及对db服务器的访问权限才能启动会话跟踪。要启动和停止会话跟踪，请执行以下操作：

代码语言：javascript
运行
复制
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);

其中X和Y是v$session视图中的会话标识符SID和SERIAL#。

这就是创建大小为65KB的临时lob并将参数cache设置为false时会话跟踪的外观

代码语言：javascript
运行
复制
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

正如您所看到的，它包含写入临时表空间的直接路径。下面是将缓存设置为true时的外观。

代码语言：javascript
运行
复制
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
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/33742687

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档