1 2 Previous Next 18 Replies Latest reply on Aug 13, 2012 8:16 AM by DannyC

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


      I’m running 11g ( an APEX 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)
          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;
          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 
            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
              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 
                ln_byte_length := ln_temp_byte_length;
              END IF;
            END LOOP;
          END IF;  
      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,


      Edited by: DannyC on Aug 9, 2012 10:38 AM
        • 1. Re: utl_file.put_raw "ORA-29285: file write” error
          Joe Upshaw
          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):
                      ls_ErrMsg := 'Invalid Path Specified';
                      RecordEnterpriseMessage( ln_ProgramLocator, SUB_PROGRAM_NAME,
                                               SubStr( 'Failure - ' || ls_StepName || 
                                                        Chr(32) || ls_ErrMsg, 1, 500 ) );  
                      RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );   
                      ls_ErrMsg := 'Invalid Mode Specified';
                      RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );   
                      ls_ErrMsg :=  'Invalid File Handle';
                      RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );     
                      ls_ErrMsg := 'Invalid Operation Specified';
                      RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );   
                      ls_ErrMsg := 'Unable to Read Specified File';
                      RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );   
                      ls_ErrMsg := 'Unable to Write to Specified File';
                      RAISE_APPLICATION_ERROR( -20000, ls_ErrMsg  );     
                      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  );    
          1 person found this helpful
          • 2. Re: utl_file.put_raw "ORA-29285: file write” error
            Joe Upshaw
            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.

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

              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,


              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

                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:


                check this values both in APEX and in SQL*PLus

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

                  I'm on 11g ( 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,

                  • 6. Re: utl_file.put_raw "ORA-29285: file write” error
                    Joe Upshaw
                    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:

                    • 7. Re: utl_file.put_raw "ORA-29285: file write” error
                      I'm calling it the way you suggested:


                      pkg_50_ipbs_main.pr_extract_file(pn_hidlen_id => 70);


                      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,

                      • 8. Re: utl_file.put_raw "ORA-29285: file write” error
                        Joe Upshaw
                        So, is it working now, Danny?
                        • 9. Re: utl_file.put_raw "ORA-29285: file write” error
                          Ah, no, sorry, I should have been clear. Please forgive me.
                          The error remains the same.
                          • 10. Re: utl_file.put_raw "ORA-29285: file write” error
                            Arie Geller
                            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?


                            &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
                            1 person found this helpful
                            • 11. Re: utl_file.put_raw "ORA-29285: file write” error

                              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);       
                                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.




                              • 12. Re: utl_file.put_raw "ORA-29285: file write” error
                                Joe Upshaw

                                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.

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

                                  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,

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

                                    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.

                                    1 2 Previous Next