Procedure woks in DB, not in APEX

sven_hub

Hello,

I am really new to APEX 3.1.2 and DBs (11g), but trying hard to succeed!
I built a stored procedure that is reading several BLOBs from my DB, and concatenates them into a single file. The input params buid up a SQL request.
When running this procedure in SQL Developer, it works fine. When running it in APEX (through a process or SQL Workshop>SQL Commands), it fails.
Here is the procedure:
----------------------------
create or replace PROCEDURE PRC_BLOBS_TO_FILE (p_prn_doc_nr number, p_prn_job_id_nr VARCHAR2, p_prn_eunr_von number, p_prn_eunr_bis number) AS
v_err varchar2(200) := 'OK';
v_bfile bfile;
v_blob blob;
v_row RAW(32000);
v_dir varchar2(200) := 'POC_DATA_DIR'; -- name der db-directory
v_start integer := 1;
v_len INTEGER := 32000;
v_bfile_size INTEGER := 0;
v_file utl_file.file_type;
v_file_name varchar2(100) := 'PRN_FROM_DB_CLUSTER_'||substr(CURRENT_TIMESTAMP,1,15)||'.prn';
BEGIN

if utl_file.is_open (v_file)
then utl_file.fclose (v_file);
end if;
dbms_output.put_line(p_prn_doc_nr||p_prn_job_id_nr||p_prn_eunr_von||p_prn_eunr_bis);
v_file := utl_file.fopen(v_dir, v_file_name, 'w', 32760);

for doc_rec in
(select job_files.jd_doc_nr, job_files.data_blob
from job_files, job_document
where (job_files.jd_doc_nr = job_document.doc_nr AND job_document.status <> 0 AND ((job_document.eu_nr > p_prn_eunr_von and job_document.eu_nr < p_prn_eunr_bis) or (job_document.doc_nr = p_prn_doc_nr) or (job_document.jc_job_id_nr = p_prn_job_id_nr))))
loop
select dbms_lob.getlength(doc_rec.data_blob), doc_rec.data_blob
into v_bfile_size, v_blob
from job_files
where jd_doc_nr = doc_rec.jd_doc_nr;

if v_bfile_size < 32760 then
utl_file.put_raw(v_file,v_blob);
utl_file.fflush(v_file);
else
while v_start < v_bfile_size loop
dbms_lob.read(v_blob,v_len, v_start, v_row);
utl_file.put_raw(v_file, v_row);
v_start := v_start + v_len;
if v_start + v_len > v_bfile_size then
v_len := v_bfile_size - v_start;
end if;
end loop;
end if;
v_len := 32000;
update job_document set status = '2' where doc_nr = doc_rec.jd_doc_nr;
commit;
end loop;
utl_file.fclose(v_file);

END PRC_BLOBS_TO_FILE;
----------------------------

And here is how I call it, trough SQL Developer and APEX:

----------------
BEGIN
PRC_BLOBS_TO_FILE('','PRN_GHLTE04_8','','');
END;
----------------

When running it on SQL Devloper, the file is properly generated. When running it through APEX (in the app or in the SQL Workshop), I get the following error:

---------------------------
ORA-29285: file write error
---------------------------

Any comment wuld be greatly appreciated, and sorry if the mistake was obvious.

Regards,
Sven

Roel Hartman

Hello Sven,

In APEX the procedure is executed as (probably) another user than you used in SQL Dev. Maybe there is a privileges issue.
Or your date format within APEX is different, so the CURRENT_TIMESTAMP contains some slashes (/) and so your filename is messed up....

Regards,
Roel

http://roelhartman.blogspot.com/
http://www.bloggingaboutoracle.org/
http://www.logica.com/

sven_hub

Thanks Roel.

I have another procedure doing almost the same (only one BLOB instead f several BLOBs), and it works fine in APEX. Therefore I think it is not related to the user.
The filename doesn't seem to be the issue either as the file gets created but stays at 0kb.

I'll have a look on the rights though.
Cheers,
Sven

sven_hub

Hi again,

I could not solve my issue. We traced it down with an expert to the "utl_file.put_raw(v_file,v_blob);" line, but nothing special there.
A workaround for me was to use Carsten Czarski's "LOB_WRITER_PLS" package. It's a helper package that uses just pure PL/SQL to achieve file writing.

[http://plsqlexecoscomm.sourceforge.net/|http://plsqlexecoscomm.sourceforge.net/]

Cheers,
Sven