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

问题描述

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

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;

我使用这个Java代码来调用函数:

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

但我得到错误:

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); 的情况下出现这个错误吗?

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

推荐答案

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

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

是的.LOB 是指向内存/磁盘存储的指针/引用.您需要先memalloc()"(...初始化)存储，将指针/引用分配给您的 LOB 变量.这就是 dbms_lob.createTemporary() 的用途.除非您使用有效的 LOB 定位器初始化 LOB 变量，否则您对该 LOB 变量的所有操作都将失败，并显示 ORA-22275: invalid LOB locator specified.

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.

增强:对您的 PL/SQL 函数进行一点重构:(请注意，我对 last_60_cpu_cursor 游标使用了一个虚拟查询.不要重复使用该游标，请使用您自己的！:-))

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

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.

此外，在将 LOB 传送回 Java 环境后，释放临时 LOB.(我不是 Java 人，无法自己编写 Java 代码片段.)

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

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

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

此外，您的解决方案存在一个性能问题.您的函数返回一个 LOB.在 PL/SQL 中，每个函数值都作为内部值的深层副本返回给其调用者.因此，如果您从函数返回 LOB，则 LOB 内容会在后台使用新的 LOB 定位器(/pointer/reference)复制.您应该使用 您可以考虑使用存储过程而不是函数，并将 LOB 作为 out nocopy 参数传递给 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 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;

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

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.

这篇关于ORA-22275: 指定的 LOB 定位器无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！