This problem is occurring in my Oracle APEX application, but it feels like a PL/SQL question, so I am posting it here.
Consider the following PL/SQL code, which works just fine ONCE after uploading a CLOB.
DECLARE
-- Variables for the XML data stored in :P0_XML_DATA
v1 number;
l_ctx_hdl dbms_xmlgen.ctxHandle;
-- Variables for the output
l_id number;
l_document BLOB;
l_xml_data clob;
l_report_layout clob;
l_student_name varchar2(200);
BEGIN
-- I have previously uploaded a BI Publisher layout (.rtf file) into the clob_content column in a table called uploaded_files
-- I am now retrieving it into the variable named l_report_layout
select clob_content into l_report_layout from uploaded_files where scope = '060104-100154' for update;
-- I am using the for update clause in the select statement because if I don't, I get this error message:
-- AJAX call returned server error ORA-22920: row containing the LOB value is not locked for Execute PL/SQL Code.
select '' into :p0_xml_data from dual;
for o in (select * from certs_needed where ae_key <> 0 order by ae_key desc)
loop
-- Create the XML data and store it in :P0_XML_DATA
select o.ae_key into v1 from dual;
l_ctx_hdl := dbms_xmlgen.newcontext
('select ae_key, student_name, email_address, class_name, customer, edate, training_location
from certs_needed
where ae_key = :1 ');
dbms_xmlgen.setnullhandling(l_ctx_hdl,2);
dbms_xmlgen.setbindvalue(l_ctx_hdl, '1', V1);
l_xml_data := dbms_xmlgen.getxml(l_ctx_hdl);
select l_xml_data into :p0_xml_data from dual;
-- Create the recipient's email address and student name
select o.email_address into :p0_cert_email from dual;
select o.student_name into l_student_name from dual;
l_document := APEX_UTIL.GET_PRINT_DOCUMENT (
p_report_data => :P0_XML_DATA,
p_report_layout => l_report_layout,
p_report_layout_type => 'rtf',
p_document_format => 'pdf');
l_id := APEX_MAIL.SEND(
p_to => :P0_CERT_EMAIL,
p_from => 'mark@csthompson.com',
p_subj => 'Your class completion certificate from Maverick Solutions is attached',
p_body => 'Test message...',
p_body_html => 'Test message...');
APEX_MAIL.ADD_ATTACHMENT (
p_mail_id => l_id,
p_attachment => l_document,
p_filename => 'Course Completion Certificate.pdf',
p_mime_type => 'application/pdf');
end loop;
APEX_MAIL.PUSH_QUEUE;
END;
The good:
Immediately after I upload the .rtf file and run the PL/SQL, I get an email with a PDF that looks perfect - it contains the correct fields, background watermark, and so forth.
The bad:
If I then run the PL/SQL again, I get an email with an empty PDF. It sort of feels like the highlighted select statement (above) isn't finding anything to actually select. But that's not true, because looking at the field in SQL Developer shows me the same contents that were there before the first run of the PL/SQL. But it certainly feels like the l_report_layout variable must be empty after the execution of the select statement, because the PDF returns nothing.
More information:
#1: I have even tried a commit after the process runs (a separate PL/SQL step in the dynamic action in the APEX application). Same result. Also the same with a rollback instead of commit;
#2: I tried creating a view called v_certlayout which was a simple select of the clob_content column from uploaded_tables, then changing the select statement to: select clob_content into l_report_layout from v_certlayout for update; (I still had to use the 'for update' clause, for the same reason.). Exact same result.
What am I missing? And how do I solve this problem? And why does a simple select statement require a 'for update' when I'm simply retrieving a CLOB, with no intention of updating the row? Is that an APEX thing? A PL/SQL thing? A simple select statement shouldn't require a lock on the row.