Hello,
I am using Oracle APEX 20.2 and am looking for a way to use an existing interactive report to generate a BLOB .csv file and insert it into a database table. I need to do this so I can query the file for an email attachment through send_mail, so there may be a better way to perform the action where data from an interactive report populates a .csv file and attaches it to an email and sends it. How would I do this?
Vinish Kapoor
Instead of getting the BLOB from the interactive report, you can create a procedure that will return the BLOB using the same query used for IR. Below is an example of that database procedure:
Call the procedure in your email program to get the blob as below:
BENNETT
Vinish,
Thank you for your quick response. I added my own query to the middle section, and am coming across a response saying 36/17 PLS-00382: expression is of wrong type. The query itself compiles just fine when I run the code between SELECT Xmlcast ... fqa.allocation_pct desc.
The line 36 being referenced is the line containing: <o_Clobdata := l_Clob;>
Any idea what would be causing this? The query is below:
BENNETT
Vinish Kapoor
Oh yes, my bad. Please do the following change in the code:
Remove the below line from the code:
o_Clobdata := l_Clob;
Add the following line before EXCEPTION:
o_Clobdata := l_Blob;
That's it. After making this change it should work. Please let me know.
BENNETT
Vinish,
The code update worked! The procedure compiled successfully. I put it into the dynamic action I'm using to send the email, and that code compiles, but then when I run it I'm getting an error saying:
Ajax call returned server error ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 for Execute PL/SQL Code.
Here is the code for the dynamic action:
Vinish Kapoor
Add the following code just after the begin in the above code:
Please try this and let me know.
BENNETT
Vinish,
I added that section, but am still getting the same error. When I run the code block in SQL Workshop instead of through the dynamic action, I get the below.
Line 49 of the approval_csv procedure is the raise line and line 37 is the beginning of the dbms_lob.converttoblob( ) line. Line 10 is the APPROVAL_CSV(v_blob); line from the dynamic action code.
BENNETT
Vinish,
I should also add that when the attachment code is removed, the email does send successfully, so the issue lies within the blob generating procedure or the email attachment section of the PL/SQL.
Vinish Kapoor
You can test for the BLOB by storing it in a temporary table and then check the BLOB data from that table. Then you will come to know if your BLOB is ok.
And if the BLOB is ok, then the issue is with your email program.
BENNETT
Hey Vinish,
I tried storing the BLOB in a temporary table, but I'm getting the same invalid LOB locator specified error. The issue seems to then be coming from the procedure.