APEX

Hi,

I’m running 11g (11.2.0.1.0)with an APEX 4.1.1.00.23 front end.
I’m having problems with the following code:

PROCEDURE PR_EXTRACT_FILE(PN_HIDLEN_ID IN ldb_50_hidden_file_handler.sysid%TYPE)
  IS

    lb_blob BLOB;
    ln_start NUMBER := 1;
    ln_byte_length NUMBER;
    ln_blob_len NUMBER;
    lr_raw_data RAW(32760);
    ln_temp_byte_length NUMBER;

    ls_database_dir VARCHAR2(255) := 'DATA_PUMP_DIR';
    ls_new_file_name VARCHAR2(255);
    l_output utl_file.file_type;
    
    ln_hidlen_sysid ldb_50_hidden_file_handler.sysid%TYPE := pn_hidlen_id;


  BEGIN

    ls_new_file_name := MY_NEW_FILE_' || ln_hidlen_sysid || '.DMP';

    -- define output directory -wb is required due to the possible size
        l_output := utl_file.fopen(ls_database_dir, ls_new_file_name,'WB', 32760); 
    
    ln_start := 1;
    ln_byte_length := 32000;

    -- get length of blob
    SELECT dbms_lob.getlength(hidfil.blob_content)
    INTO ln_blob_len
    FROM ldb_50_hidden_file_handler hidfil
    WHERE hidfil.sysid = ln_hidlen_sysid;
   
    -- save blob length
    ln_temp_byte_length := ln_blob_len;

    -- select blob into variable
    SELECT hidfil.blob_content
    INTO lb_blob
    FROM ldb_50_hidden_file_handler hidfil
    WHERE hidfil.sysid = ln_hidlen_sysid;

    -- if small enough for a single write
    IF ln_blob_len < 32760 
    THEN
      utl_file.put_raw(l_output,lb_blob, TRUE);
      
    ELSE -- write in pieces

      ln_start := 1;

      WHILE ln_start < ln_blob_len and ln_byte_length > 0
      LOOP
        dbms_lob.read(lb_blob,ln_byte_length,ln_start,lr_raw_data);        

        utl_file.put_raw(l_output,lr_raw_data, TRUE);
        
        -- set the start position for the next cut
        ln_start := ln_start + ln_byte_length;

        -- set the end position if less than 32760 bytes
        ln_temp_byte_length := ln_temp_byte_length - ln_byte_length;
        
        IF ln_temp_byte_length < 32000 
        THEN
          ln_byte_length := ln_temp_byte_length;
        END IF;
      END LOOP;
    END IF;  

    utl_file.fclose(l_output);
   
  EXCEPTION
    …
  END PR_EXTRACT_FILE;

I’m at a complete loss as to why I’m getting an “ORA-29285: file write” error but only when it is called from within APEX.

When I call the procedure from within SQL Plus or from PL/SQL Developer,signed in as either Anonymous or the procedure owner, I don’t see an error. When I call it from within APEX, using an identical call and data, it falls over at the

 utl_file.put_raw(l_output,lr_raw_data, TRUE) 

line. It doesn’t matter whether the file is a small (1kb), text based test file or a large, binary, data pump file, the error is the same and always on the first attempt to call put_raw. The file is created, but is empty. I can see that it is picking up the data on route, so I know it is picking up the blob.

Any thoughts would be appreciated.

Regards and many thanks for your time in advance,

Danny

Edited by: DannyC on Aug 9, 2012 10:38 AM