ORA-22275:指定的LOB定位器无效 [英] ORA-22275: invalid LOB locator specified

问题描述

我具有强大的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调用外观如何，取决于您和

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定位器无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！