Forum Stats

  • 3,780,536 Users
  • 2,254,407 Discussions
  • 7,879,374 Comments

Discussions

Procedure woks in DB, not in APEX

sven_hub
sven_hub Member Posts: 16
edited Oct 1, 2008 3:32AM in APEX Discussions
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

Answers

  • Roel Hartman
    Roel Hartman Member Posts: 2,711 Gold Trophy
    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/
    Roel Hartman
  • sven_hub
    sven_hub Member Posts: 16
    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
    sven_hub Member Posts: 16
    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
This discussion has been closed.