1 2 Previous Next 18 Replies Latest reply: Aug 13, 2012 3:16 AM by DannyC Go to original post RSS
      • 15. Re: utl_file.put_raw "ORA-29285: file write” error
        Arie Geller
        Hello Danny,

        Please inspect the code in the following thread and see if it can help you:

        Re: Problems with moving files to ora directory UTL_FILE.PUT_RAW - ORA-29285

        Regards,
        Arie.


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

        ♦ Author of Oracle Application Express 3.2 – The Essentials and More
        • 16. Re: utl_file.put_raw "ORA-29285: file write” error
          DannyC
          Hi Arie,

          The link that you sent me finally helped me get this thing working. I'm afraid I marked your post as helpful rather than answered and it won't let me update it.

          I've still no idea what was wrong with my original code. To fix it, I took a copy of the code in the link you sent me, ditched all of the BFile code and then changed it to run against my table and BLOB. I hit some issues but eventually got it working. I've gone through and tried to figure out the differences, but nothing obvious jumps out; the few bits that I thought might be a cause, weren't.
          This bug has caused the project to stall badly, so as much as I would like to know, it will have to wait for another time.
          Here is the final code in case you are wondering:
            PROCEDURE PR_EXTRACT_FILE (PN_HIDLEN_ID IN ldb_50_hidden_file_handler.sysid%TYPE)
            IS
            
              l_output utl_file.file_type;
              ls_database_dir VARCHAR2(255) := 'DATA_PUMP_DIR';
              ls_new_file_name VARCHAR2(255) := 'IPBSM_O2B_IMPORT.DMP';
          
              lb_blob BLOB;
              ln_start NUMBER := 1;
              lr_raw_data RAW(32000);
              ln_byte_length NUMBER := 32000;
              ln_blob_len  NUMBER;
              ln_temp_byte_length       NUMBER;
              
              ln_hidlen_sysid ldb_50_hidden_file_handler.sysid%TYPE := pn_hidlen_id;
              ls_stage VARCHAR2(255) := 'NOT SET';
          
            BEGIN
          
              ls_stage := 'BLOB LENGTH';
              -- 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;
             
              ls_stage := 'BLOB';
              -- select blob into variable
              SELECT hidfil.blob_content
              INTO lb_blob
              FROM ldb_50_hidden_file_handler hidfil
              WHERE hidfil.sysid = ln_hidlen_sysid;
              
              ls_stage := 'OUTPUT DIRECTORY';
              -- define output directory -wb is required due to the possible size
              IF NOT UTL_FILE.IS_OPEN(l_output)
              THEN  
                l_output := utl_file.fopen(ls_database_dir, ls_new_file_name,'WB', 32760);       
              END IF;  
          
              ln_start := 1;
              ln_byte_length := 32000;
          
              ls_stage := 'SIZE CHECK';
              -- if small enough for a single write
              IF ln_blob_len < 32760 
              THEN
                --ln_start := 1;
                ls_stage := 'PUT RAW (S)';
                utl_file.put_raw(l_output,lb_blob);
                utl_file.fflush(l_output);
              ELSE -- write in pieces
                ln_start := 1;
                ls_stage := 'WHILE (L)';
                WHILE ln_start < ln_blob_len 
                    AND 
                      ln_byte_length > 0
                LOOP
                  ls_stage := 'READ BLOB (L)';      
                  dbms_lob.read(lb_blob,ln_byte_length,ln_start,lr_raw_data);        
                  
                  ls_stage := 'PUT RAW (L)';
                  utl_file.put_raw(l_output,lr_raw_data);
                  
                  ls_stage := 'ADJUST START (L)';
                  -- set the start position for the next cut
                  ln_start := ln_start + ln_byte_length;
          
                  ls_stage := 'ADJUST END (L)';
                  -- 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;  
          
              ls_stage := 'CLOSE (L)';  
              
              utl_file.fclose(l_output);
             
            EXCEPTION
              WHEN NO_DATA_FOUND
                THEN
                  utl_file.fclose(l_output);        
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505037);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
                RAISE;      
              WHEN UTL_FILE.INVALID_PATH 
                THEN
                  utl_file.fclose(l_output);        
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505042);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
          
                  RAISE_APPLICATION_ERROR( -20000, pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM  );   
                      
              WHEN UTL_FILE.INVALID_MODE 
                THEN
                  utl_file.fclose(l_output);        
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505043);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
          
                  RAISE_APPLICATION_ERROR( -20000, pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM  );   
                      
              WHEN UTL_FILE.INVALID_FILEHANDLE 
                THEN
                  utl_file.fclose(l_output);        
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505044);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
          
                  RAISE_APPLICATION_ERROR( -20000, pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM  );   
                      
              WHEN UTL_FILE.INVALID_OPERATION
                THEN
                  utl_file.fclose(l_output);        
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505045);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
          
                  RAISE_APPLICATION_ERROR( -20000, pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM  );   
             
                      
              WHEN UTL_FILE.READ_ERROR 
                THEN
                  utl_file.fclose(l_output);        
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505046);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
          
                  RAISE_APPLICATION_ERROR( -20000, pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM  );   
             
                      
              WHEN UTL_FILE.WRITE_ERROR 
                THEN
                  utl_file.fclose(l_output);
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505047);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
                  RAISE_APPLICATION_ERROR( -20000, pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM  );   
             WHEN UTL_FILE.INTERNAL_ERROR 
               THEN
                  utl_file.fclose(l_output);
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505048);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
          
                  RAISE_APPLICATION_ERROR( -20000, pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM  );   
                
                      
             WHEN VALUE_ERROR 
               THEN
                  utl_file.fclose(l_output);
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505049);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
          
                  RAISE_APPLICATION_ERROR( -20000, pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM  );   
              WHEN OTHERS
                THEN
                  pls_error_text := ap_error_msg_lookup.error_message_fetch(1003081, 505036);
          
                  pkg_utilities.register_error(error_code_in => psn_error_code,
                                               error_instance_id_out => psn_error_instance,
                                               text_in => pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
                RAISE_APPLICATION_ERROR( -20000, pls_error_text || '. Stage: ' || ls_stage || '. Error: ' ||  SQLERRM);
                           
            END PR_EXTRACT_FILE;
          Many, many thanks to both you and Joe.

          Regards,
          Danny
          • 17. Re: utl_file.put_raw "ORA-29285: file write” error
            Arie Geller
            Hello Dannay,

            I’m glad things worked out for you.

            I’m pleased that you find the time to include the working code (it’s much more important than my points).

            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
            • 18. Re: utl_file.put_raw "ORA-29285: file write” error
              DannyC
              Hi,

              Thank you. Hopefully someone in the future will be able to save themselves some time by using my working version as a starting point for what they need.

              I'm pleased that you posted; it has given me the chance to give you the credit for getting me to the solution.

              Regards,

              Danny
              1 2 Previous Next