Phoenix Phoenix - 3 months ago 12x
SQL Question

ORA-22275 error: invalid LOB locator specified

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:

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;


/* 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.

I am new to Oracle SQL, and am unsure why my ValidID check works by returning z_lob but my case statement does not.

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


Initialise your lob with temporary first

DBMS_LOB.CREATETEMPORARY(z_lob,true); --true if you want it to be cached.