我有一个用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;
我收到的错误是：ORA-22275：指定了无效的LOB定位器.
我是Oracle sql的新手,并且不确定为什么我的ValidID检查通过返回z_lob而工作,但我的case语句没有.
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
解决方法
首先暂时初始你的吊球
DBMS_LOB.CREATETEMPORARY(z_lob,true); --true if you want it to be cached.
