当前位置：theitroad>oracle ORA-22275: 指定的 LOB 定位器无效

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

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/33742687/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-09 00:59:14 　来源:igfitidea点击:

ORA-22275: invalid LOB locator specified

oracleplsqloracle11goracle10g

提问by Peter Penzov

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

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

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:

我使用此 Java 代码来调用该函数：

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);?

你知道为什么我会在没有 DBMS_LOB.CREATETEMPORARY(LINE,true); 的情况下收到这个错误吗？

回答by nop77svk

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

你知道为什么我没有得到这个错误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.

是的。LOB 是指向内存/磁盘存储的指针/引用。您需要先“memalloc()”（...初始化）存储，将指针/引用分配给您的 LOB 变量。这dbms_lob.createTemporary()就是为了。除非您使用有效的 LOB 定位器初始化 LOB 变量，否则对该 LOB 变量的所有操作都将失败并显示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_cursorcursor. Do not reuse the cursor, use your own! :-))

增强功能：对您的 PL/SQL 函数进行一些重构：（请注意，我对last_60_cpu_cursor游标使用了虚拟查询。不要重用游标，使用您自己的！:-)）

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.
  1. 您不需要打开+获取+关闭游标。常规的游标循环就可以了（如果不是更好的话，这要归功于幕后的隐式批量获取）。
  2. 将临时 LOB 显式声明为缓存（cache => true; 正如您已经拥有的那样）。这可确保将数据块添加到内存中的 LOB，而不是添加到磁盘 ( cache => false)。
  3. 连接要附加到 LOB 的字符串，以尽量减少对dbms_lob.append().
  4. dbms_output.put_line()从您的函数中删除。如果 LOB 内容大于 32K，无论如何这都会引发异常。

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.)

此外，在完成将 LOB 传送回 Java 环境后，释放临时 LOB。（我不是 Java 人，无法自己编写 Java 代码片段。）

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.)

此外，您的 Java 代码中存在概念错误；将函数的返回注册Types.VARCHAR为错误。您应该使用Oracle 的专用 CLOB 类型。（我在 C# 中看到过这些，Java 也必须有。）

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 an out nocopyparameter. The stored proc would then look like this:

此外，您的解决方案存在一个性能问题。您的函数返回一个 LOB。在 PL/SQL 中，每个函数值都作为内部值的深层副本返回给其调用者。因此，如果您从函数返回 LOB，则 LOB 内容会在后台使用新的 LOB 定位器（/pointer/reference）复制。你应该使用您可以考虑使用存储过程而不是函数并将 LOB 作为out nocopy参数传递给 Java 。存储过程将如下所示：

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.

您的 Java 调用看起来如何，取决于您和JDBC 文档；但是，可以肯定的是，以这种方式返回的 LOB 意味着没有后台内容复制。当然，释放分配的临时 LOB 的需要仍然适用。

回答by ibre5041

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.

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

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.

有时从/到 VARCHAR2 的隐式转换有效，所以看起来 CLOB 与其他任何变量一样是可变的。你总是应该检查超过 32KB 的文本的 CLOB 代码。

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.

PS：您还应该在正确的位置显式调用 freetemporary() 。Java GC 不与数据库引擎对话，因此数据库不知道何时可以释放 TEMP 空间。

UPDATE1: You should call either oracle.sql.CLOB.freetemporary()or java.sql.BLOB.freeor 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.

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

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

UPDATE2：您可能需要 DBA 权限并访问 db 服务器以启动会话跟踪。要启动和停止会话跟踪，请执行：

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.

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

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

这是创建大小为 65KB 的临时 lob 并将参数缓存设置为时会话跟踪的样子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.

如您所见，它包含写入临时表空间的直接路径。这就是 if 在使用缓存设置为 时的样子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

相关推荐

string 在功能上用分隔符分割字符串的最佳方法是什么？

string 在功能上用分隔符分割字符串的最佳方法是什么？

string 要在 TMemoBox 中显示的字符串内的换行符

string 要在 TMemoBox 中显示的字符串内的换行符

强制 Oracle 删除全局临时表

强制 Oracle 删除全局临时表

string 如何将字符串和矩阵写入 MATLAB 中的 .txt 文件？

string 如何将字符串和矩阵写入 MATLAB 中的 .txt 文件？

相关推荐
最近更新
标签