Hello,
I'm working on a page with a list of buttons with different values. When a button is pressed the DBMS executes a SQL procedure DWNLD_PRC that uses the button value and download the result of that query as a csv file to the user's computer. I used to open a modal page on click that would launch the procedure but I was asked to change the button behavior so that no modal page opens. I changed the button behavior so that instead of opening a modal page it changes the value of an hidden item P_Hidden. That change is caught by a dynamic action that launches the SQL procedure DWNLD_PRC with P_Hidden in input.
Clicking my buttons with this setup display a pop up with message: "An unexpected internal application error has occurred. Please get in contact with your system administrator and provide reference #918 for further investigation.". When I go into debug mode I see that the cause of the error is "Error: Ajax call returned server error ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 for Execute PL/SQL Code.".
The error backtrace:
"error_backtrace: ORA-06512: at "SYS.DBMS_LOB", line 991
ORA-06512: at "SCH.DWNLD_PRC", line 44
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1707
ORA-06512: at "APEX_180200.WWV_FLOW_DYNAMIC_EXEC", line 2450
ORA-06512: at "APEX_180200.WWV_FLOW_DYNAMIC_EXEC", line 1476
ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN_UTIL", line 2975
ORA-06512: at "APEX_180200.WWV_FLOW_DYNAMIC_ACTION_NATIVE", line 475"
Here is my procedure:
create or replace PROCEDURE DWNLD_PRC (v_tr_code IN VARCHAR2) AS
v_length NUMBER;
v_line VARCHAR2(3000);
v_query_res CLOB;
v_blob BLOB;
l_dest_offset PLS_INTEGER := 1;
l_src_offset PLS_INTEGER := 1;
l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
l_warning PLS_INTEGER := DBMS_LOB.warn_inconvertible_char;
BEGIN
FOR x in(
SELECT
businessunit_id,
businessunit_name,
businessunit_status,
dept_lvl,
dept_name,
dept_id,
dept_status,
parent_id,
parent_name,
tr_code
FROM
tbl
WHERE
tr_code = v_tr_code
START WITH
parent_id IS NULL
CONNECT BY
parent_id = PRIOR dept_id)
LOOP
v_line:=x.businessunit_id||','||x.businessunit_name||','||x.businessunit_status||','||x.dept_id||','||x.dept_lvl||','||x.dept_name||','||x.dept_status||','||x.parent_id||','||x.parent_name||','||x.tr_code||CHR(10);
v_query_res:=concat(v_query_res,v_line);
END LOOP;
--Convert CLOB to BLOB
DBMS_LOB.createtemporary(
lob_loc => v_blob,
cache => TRUE);
DBMS_LOB.converttoblob(
dest_lob => v_blob,
src_clob => v_query_res,
amount => DBMS_LOB.lobmaxsize,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
blob_csid => DBMS_LOB.default_csid,
lang_context => l_lang_context,
warning => l_warning);
v_length := dbms_lob.getlength(v_blob);
owa_util.mime_header(nvl('text/csv', 'application/octet'), false);
-- set the size so the browser knows how much to download
htp.p('Content-length: ' || v_length);
-- the filename will be used by the browser if the users does a save as
htp.p('Content-Disposition: attachment; filename="test"');
-- close the headers
owa_util.http_header_close;
-- download the BLOB
wpg_docload.download_file(v_blob);
END;
s
I would like to understand why my procedure goes into error now and how to fix it.
Thank you for reading.