Error ORA-22275: Invalid LOB Locator

Question

I have a procedure that populates a BLOB variable with a PDF document. What I'm trying to do is add logic to display only a PDF document for 60 days 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; 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 get is: ORA-22275: invalid LOB locator specified.

I'm new to Oracle SQL and don't know why my ValidID check works by returning z_lob, but my case argument does not.

Edit: adding a 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 

Answer 1

Initialize your forehead with a temporary first

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

Answer 2

Your function will probably return (depending on your audit parameter value) a blob with a NULL value to the SYS.WPG_DOCLOAD method, which throws an unhandled exception that you see.

Perhaps you could change your return z_lob; as return nvl(z_lob, empty_blob());