SQL ORA-22275 错误：指定的 LOB 定位器无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13978242/
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
ORA-22275 error: invalid LOB locator specified
提问by Phoenix
I have a procedure that populates a BLOB variable with a PDF document. What I am trying to do is add logic to only display the PDF document within a 60 day period from a static date. See below:
我有一个使用 PDF 文档填充 BLOB 变量的过程。我想要做的是添加逻辑以仅在静态日期的 60 天内显示 PDF 文档。见下文：
check_staticdate number(1);
function DisplayPDF (audit in number) RETURN blob is
person_id person.person_id%type;
z_lob blob;
blob_length NUMBER;
CURSOR getPDF(audit number) IS
select report
from report_table
where report_type = 'PDF'
and job_no = audit order by rec_no;
begin
/* Check Valid ID */
if not package.ValidID(person_id, check_only=>TRUE) then
return z_lob;
end if;
/* Here is the case statement.*/
select case
when exists
(
SELECT 'x' from table
where table_id = person_id
and trunc(sysdate) < trunc(table_static_date + 60)
)
then 1
else 0
end into check_staticdate
from dual;
if (check_staticdate = 0) then
return z_lob;
end if;
open getPDF(audit);
fetch getPDF into z_lob;
close getPDF;
return z_lob;
end DisplayPDF;
The error I am receiving is: ORA-22275: invalid LOB locator specified.
我收到的错误是： ORA-22275: invalid LOB locator specified.
I am new to Oracle SQL, and am unsure why my ValidID check works by returning z_lob but my case statement does not.
我是 Oracle SQL 的新手，我不确定为什么我的 ValidID 检查通过返回 z_lob 起作用，但我的 case 语句没有。
Edit: Adding full error stack
编辑：添加完整的错误堆栈
Failed to execute target procedure ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.WPG_DOCLOAD", line 51
ORA-06512: at "User.Package", line 733
ORA-06512: at line 33
回答by Suman
Initialise your lob with temporary first
首先用临时初始化你的 lob
DBMS_LOB.CREATETEMPORARY(z_lob,true); --true if you want it to be cached.
回答by Michael O'Neill
Your function is probably returning (depending on your auditparameter value) a blob with a NULL value to the SYS.WPG_DOCLOADmethod which throws the unhandled exception you see.
您的函数可能正在返回（取决于您的audit参数值）一个带有 NULL 值的 blob 到SYS.WPG_DOCLOAD抛出您看到的未处理异常的方法。
Perhaps you could modify your return z_lob;to be return nvl(z_lob, empty_blob());
也许你可以修改你的return z_lob;是return nvl(z_lob, empty_blob());
