This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Aug 13, 2012 1:16 AM by DannyC RSS

utl_file.put_raw "ORA-29285: file write” error

DannyC Newbie
Currently Being Moderated
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
  • 1. Re: utl_file.put_raw "ORA-29285: file write” error
    Joe Upshaw Journeyer
    Currently Being Moderated
    Not really an APEX issue but, I will try to help. Can you replace your exception handler with the following and then post the exception that is thrown (delcare a variable ls_ErrMsg to hold the error test):
             WHEN UTL_FILE.INVALID_PATH THEN
            
                ls_ErrMsg := 'Invalid Path Specified';
                    
                RecordEnterpriseMessage( ln_ProgramLocator, SUB_PROGRAM_NAME,
                                         ERROR_SEVERITY, 
                                         SubStr( 'Failure - ' || ls_StepName || 
                                                  Chr(32) || ls_ErrMsg, 1, 500 ) );  
                    
                RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );   
                
            WHEN UTL_FILE.INVALID_MODE THEN
            
                ls_ErrMsg := 'Invalid Mode Specified';
                    
                RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );   
    
            WHEN UTL_FILE.INVALID_FILEHANDLE THEN
            
                ls_ErrMsg :=  'Invalid File Handle';
                    
                RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );     
                
            WHEN UTL_FILE.INVALID_OPERATION THEN
            
                ls_ErrMsg := 'Invalid Operation Specified';
                    
                RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );   
                
            WHEN UTL_FILE.READ_ERROR THEN
            
                ls_ErrMsg := 'Unable to Read Specified File';
                    
                RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );   
                
            WHEN UTL_FILE.WRITE_ERROR THEN
            
                ls_ErrMsg := 'Unable to Write to Specified File';
                    
                RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );     
                
            WHEN UTL_FILE.INTERNAL_ERROR THEN
            
                ls_ErrMsg := 'Unspecified Internal Error Encountered';
                    
                RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );      
                
            WHEN VALUE_ERROR THEN
            
                ls_ErrMsg := 'Invalid Value Encountered. Perhaps ' ||
                             'an argument to a GET or PUT operation ' ||
                             'was > 32,767 Bytes';
                    
                RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );   
                                                                   
            WHEN OTHERS THEN
                
                ls_ErrMsg := SQLERRM;
                    
               RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );    
    -Joe
  • 2. Re: utl_file.put_raw "ORA-29285: file write” error
    Joe Upshaw Journeyer
    Currently Being Moderated
    Wait... you have the fopen call commented out. That is, for sure, why you are getting the error.

    >
    ORA-29285 - file write error

    Cause - Failed to write to, flush, or close a file.

    Action - Verify that the file exists, that it is accessible, and that it is open in write or append mode.
    >

    YOu don't see the error in SQL*Plus unless you set serveroutput on. If you do, you will get the error.

    -Joe
  • 3. Re: utl_file.put_raw "ORA-29285: file write” error
    DannyC Newbie
    Currently Being Moderated
    Hi,

    First off, many thanks for looking at this for me. It's much appreciated.

    Secondly, I'm afraid the commented out fopen was a slip by me when I was posting the code. (I've put it back in) I'd been hacking away trying to figure out what was going on and adding debug. I had (badly) cleaned it up to make it more readable for the forum. I'd also taken my exception handler out to save space. I'm afraid I didn't do as good a job as I could have.

    I put in your exception handler, I'd to edit a little, the invalid path exception and the value error would not compile no matter what I did.

    When it's all finished I get : AJAX call returned server error ORA-20000: Unable to Write to Specified File for Execute PL/SQL Code.

    I've moved the code around and no call a PL/SQL routine from a button that accepts a sysid rather than makes a call to the database procedure but in essence, nothing else has changed.

    What is REALLY confusing me is why this works when called from SQL PLUS. It's not just that it doesn't raise an error, it actually creates the file and populates it. If I call the exact same procedure with exactly the same parameter value using exactly the same table data from within APEX, I get the file created, it is just not populated with any data. That's why I posted it in the APEX formum. There is something different that I'm missing between the APEX call and the SQL Plus call. I thought it might be permissions but it's creating the file. I've even tried it against a record that I know is fully committed to the database, just in case it's a session issue but whatever I do, I cannot get it to get past the put_raw line. I know it's failing at that line from the previous debug.

    I'm at a complete loss.

    Thanks again for your time,

    Danny

    Edited by: DannyC on Aug 9, 2012 11:24 AM

    Edited by: DannyC on Aug 9, 2012 11:25 AM
  • 4. Re: utl_file.put_raw "ORA-29285: file write” error
    Joe Upshaw Journeyer
    Currently Being Moderated
    Danny,

    That is really, really weird about the exceptions. VALUE_ERROR is a built-in Oracle PL/SQL exception (http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS00703). INVALID_PATH is part of the UTL_FILE package specification (http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_file.htm#ARPLS70901).

    What version of Oracle are you on?

    I am not sure that you are not in fact, hitting an permissions problem. Within APEX, you are not connected as the user with which you login through the login screen. Rather, the DB conection is made, generally, by a user who's name is equivalent to the WORKSPACE name; e.g. US_RISK workspace logs in as US_RISK user/schema. In SQL*Plus, I suspect you are connecting as someone else.

    Add a line using the UTL_FILE.IS_OPEN method to confirm that the file is open for writing before you attempt to write. UTL_FILE_IS_OPEN (<file_handle>).

    Also, check the current user and schema with:

    SELECT SYS_CONTEXT( 'USERENV', 'SESSION_USER') FROM DUAL;
    SELECT SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA') FROM DUAL;

    check this values both in APEX and in SQL*PLus

    -Joe
  • 5. Re: utl_file.put_raw "ORA-29285: file write” error
    DannyC Newbie
    Currently Being Moderated
    HI,

    I'm on 11g (11.2.0.1.0). The VALUE ERROR within APEX was complaining about assigning a LONG to a LONG, the INVALID Path didn't like the RecordEnterpriseMessage procedure. I managed to get the Value Error to compile on the database version as opposed to the APEX pl/sql procedure version, which I have now switch back to.

    I debugged the user previously, it came out as ANONYMOUS; which is the same user I signed into SQL PLUS as.

    I've added in the two selects and in both cases the answers come back the same: ANONYMOUS for the user and O2B for the schema. O2B is the schema owner for both the table and the procedure. I've rechecked the permissions and synonyms.

    If I debug out the error with stage I get:

    Unable to Write to Specified File Stage: PUT RAW (S)

    The stage variable is set and reset as it passes through the variable. PUT RAW (S) is the value just before the small file utl_file.put_raw is called. If I test against a large file, the failure is at the equivalent line, first call in the loop.

    Thank you,

    Danny
  • 6. Re: utl_file.put_raw "ORA-29285: file write” error
    Joe Upshaw Journeyer
    Currently Being Moderated
    Oops! I meant to give you the canonical checks for UTL_FILE which help to isolate the error type. I cut and pasted form some code that I had used and meant to chop out all of my code. Guess I missed one. Sorry about that. The VALUE_ERROR compile is still mystifying to me but, let's move on and assume that it isn't germane.

    I am surprised to hear that the query shows ANONYMOUS. If you have TOAD or Enterprise Manager or the like, you can usually see the APEX sessions connected as HTTPD.WORKERblahblah. The user is always APEX_PUBLIC_USER.

    The LONG to LONG error can happen if your code is longer than 32K. Since you already have a working version in the database just formalize it into a named PL/SQL block, i.e. make it a procedure. Then, within APEX, just call the procedure rather than having all of the code in APEX (This is generally a better practice anyway). I bet this will get around the problem.

    IN other words, in the APEX process just have:

    BEGIN
         DANNY_FILEWRITE_PROC;
    END;
  • 7. Re: utl_file.put_raw "ORA-29285: file write” error
    DannyC Newbie
    Currently Being Moderated
    I'm calling it the way you suggested:

    BEGIN

    pkg_50_ipbs_main.pr_extract_file(pn_hidlen_id => 70);

    END;


    I've hard coded the ID in for the time being to try and reduce the number of possible causes.

    I'm using PL/SQL Developer; it's very similar to TOAD but a fraction of the price. If I sign in as system and look at the sessions, I can see my ANONYMOUS APEX session.

    Once again I'd just like to reiterate my thanks,


    Danny
  • 8. Re: utl_file.put_raw "ORA-29285: file write” error
    Joe Upshaw Journeyer
    Currently Being Moderated
    So, is it working now, Danny?
  • 9. Re: utl_file.put_raw "ORA-29285: file write” error
    DannyC Newbie
    Currently Being Moderated
    Ah, no, sorry, I should have been clear. Please forgive me.
    The error remains the same.
    Danny
  • 10. Re: utl_file.put_raw "ORA-29285: file write” error
    Arie Geller Guru
    Currently Being Moderated
    Hello Danny,

    What is the Web server you are using – OHS, EPG or APEX Listener?

    What is your local OS on the database server – Windows, Linux/Unix, other?

    Can you give us the full definition of the directory object DATA_PUMP_DIR?

    Regards,
    Arie.

    -------------------------------------------------------
    &diams; Please remember to mark appropriate posts as correct/helpful. For the long run, it will benefit us all.

    &diams; Author of Oracle Application Express 3.2 – The Essentials and More
  • 11. Re: utl_file.put_raw "ORA-29285: file write” error
    DannyC Newbie
    Currently Being Moderated
    Hi,

    Thanks for taking an interest; it's appreciated.

    I'm using APEX Listener on a Windows 7 64 bit Pro box.

    I'm not quite sure what you mean by "full definition" for the directory. I've check the folder permissions and it's fully open with regards to read/write permissions. I've actually got it set so any user has full control.

    I've made progress of sorts this morning, although given I'm still not able to write the file, I'm not sure that you can call it that.

    I have found that if I change
      l_output := utl_file.fopen(ls_database_dir, ls_new_file_name,'WB', 32760);       
     
    to
      l_output := utl_file.fopen(ls_database_dir, ls_new_file_name,'W', 32760);
     
    and (on a small file) run:
     utl_file.put_LINE(l_output,'THIS IS A TEST');
    I get a file with the "THIS IS A TEST" text in it. Given that, I know that I can write to the file. The problem still is, and for reasons that are proving to beyond my understanding, when it's run through APEX, it doesn't like WB but it does when it's called from sql Plus.

    The point I'm finally trying to get to is to copy a dump file that was generated on another database using data pump expd so that I can import it into the new database. For that to happen, the file has to be in a database directory that the end user may not have physical access to, hence the hoop jumping.

    Regards,

    Danny

    Regards,

    Danny
  • 12. Re: utl_file.put_raw "ORA-29285: file write” error
    Joe Upshaw Journeyer
    Currently Being Moderated
    Danny,

    Were you aware that you can data pump directly from DB to DB without ever dumping to files in the interim?

    Truly odd about the WB versus W open mode problem.

    We have eliminated the issue from being when the code is located in APEX which I think is important. This tells us that it must* be something environmental. My guess is that it is something to do with the NLS parameters that are set via SQL*Plus versus APEX. I would check and log these and hopefully that will identify the relevent difference. Afterward, you can force the issue with an alter session.

    -Joe
  • 13. Re: utl_file.put_raw "ORA-29285: file write” error
    DannyC Newbie
    Currently Being Moderated
    Hi,

    I didn't know about the database to database (thanks for letting know about it) but in this instance it wouldn't help. The project concept is that there is a parent database which holds all of the data and is updateable and there are a number of physically remote APEX databases with satellite users that need read only access to a subset of this data. The only way of getting the data there, in some instances, is via the physical transfer of a file. The end user will be given a disk or memory stick and they need to be able to load the dump file onto the APEX database, which may be in yet another physically remote location.

    I’m guessing that a data pump from one database to another will require links and I’m not sure that the required security for the parent database will allow that.

    I’ll have a look at the NLS parameters.

    Thank you again,

    Danny
  • 14. Re: utl_file.put_raw "ORA-29285: file write” error
    DannyC Newbie
    Currently Being Moderated
    Hi,

    I've output all of the parameters from nls_database_parameters and they are a perfect match for each other when the procedure is successfully run through SQL Plus and fails to run through APEX.

    The error is : Unable to Write to Specified File. ORA-29285: file write error

    I'm completely stumped.

    Danny
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points