8 Replies Latest reply: Sep 30, 2013 7:01 AM by EV259 RSS

    Rename a file while extraction of a BLOB

    EV259

      Hi All,

       

      I have a table with BLOB as a column. I need to extract the blob as a file and rename it ( probably adding some prefix to the original file name).

       

      Could you please help me on while executing this below procedure, where we can change the destination file name.

       

      Say Ex: My file name in the table is 'Test.jpg'. And I want to extract it to directory on the server with the name 'SR001-Test.jpg'. How can i acheive this.

       

      Thanks for your help.

      CREATE OR REPLACE PROCEDURE blob2file(
            pdname VARCHAR2, psname VARCHAR2, pfname VARCHAR2) IS

             vblob   BLOB;
             vstart  NUMBER := 1;
             bytelen NUMBER := 32000;
             len     NUMBER;
             my_vr   RAW(32000);
             x       NUMBER;
        l_output utl_file.file_type;     
      BEGIN
              -- define output directory
              l_output := utl_file.fopen('ORALOAD', pfname, 'WB', 32760);

              -- get length of blob
              SELECT dbms_lob.getlength(iblob)
              INTO len
              FROM pdm
               WHERE dname = pdname
              AND sname = psname
              AND fname = pfname;

              -- save blob length
              x := len;

              -- select blob into variable
              SELECT iblob
              INTO vblob
              FROM pdm
               WHERE dname = pdname
              AND sname = psname
              AND fname = pfname;

              -- if small enough for a single write
              IF len < 32760 THEN
                utl_file.put_raw(l_output,vblob);
                utl_file.fflush(l_output);
              ELSE -- write in pieces
                vstart := 1;
                WHILE vstart < len
                LOOP
                  dbms_lob.read(vblob,bytelen,vstart,my_vr);

                  utl_file.put_raw(l_output,my_vr);
                  utl_file.fflush(l_output)

                  -- set the start position for the next cut
                  vstart := vstart + bytelen;

                  -- set the end position if less than 32000 bytes
                  x := x - bytelen;
                  IF x < 32000 THEN
                    bytelen := x;
                  END IF;
                END LOOP;
              END IF;
              utl_file.fclose(l_output);
      END blob2file;

        • 1. Re: Rename a file while extraction of a BLOB
          Solomon Yakobson

          EV259 wrote:

           

          Could you please help me on while executing this below procedure, where we can change the destination file name.

           

          Say Ex: My file name in the table is 'Test.jpg'. And I want to extract it to directory on the server with the name 'SR001-Test.jpg'. How can i acheive this.

           


          UTL_FILE.FRENAME.

           

          SY.

          • 2. Re: Rename a file while extraction of a BLOB
            EV259

            Thank you SY for your input.

             

            I have tried this UTL_FILE.FRENAME after UTL_FILE.fclose(). But, there was an error.

             

            ORA-29292: file rename operation failed.

             

            utl_file.fclose(l_output);

            Begin

            UTL_FILE.FRENAME('ORALOAD',   pfname, 'ORALOAD', 'SR001'||pfname);

            End;

             

             

            Please help on where to place it.

             

            Thanks

            • 3. Re: Rename a file while extraction of a BLOB
              Solomon Yakobson

              Make sure file 'SR001'||pfname doesn't exist. FRENAME will not rename to existing file

               

              SQL> declare
                2      v_file utl_file.file_type;
                3  begin
                4      v_file := utl_file.fopen('TEMP','text.txt','WB',32760);
                5      utl_file.put_raw(v_file,utl_raw.cast_to_raw('ABC'));
                6      utl_file.fflush(v_file);
                7      utl_file.fclose(v_file);
                8      utl_file.frename('TEMP','text.txt','TEMP','test_renamed.txt');
                9  end;
              10  /

              PL/SQL procedure successfully completed.

              SQL> /
              declare
              *
              ERROR at line 1:
              ORA-29292: file rename operation failed
              ORA-06512: at "SYS.UTL_FILE", line 348
              ORA-06512: at "SYS.UTL_FILE", line 1290
              ORA-06512: at line 8


              SQL>


              In such case you need to FREMOVE file prior to renaming:

               

              SQL> declare
                2      v_file utl_file.file_type;
                3      v_file_exists boolean;
                4      v_file_length number;
                5      v_file_blocksize number;
                6  begin
                7      v_file := utl_file.fopen('TEMP','text.txt','WB',32760);
                8      utl_file.put_raw(v_file,utl_raw.cast_to_raw('ABC'));
                9      utl_file.fflush(v_file);
              10      utl_file.fclose(v_file);
              11      utl_file.fgetattr('TEMP','test_renamed.txt',v_file_exists,v_file_length,v_file_blocksize);
              12      if v_file_exists
              13        then
              14          utl_file.fremove('TEMP','test_renamed.txt');
              15      end if;
              16      utl_file.frename('TEMP','text.txt','TEMP','test_renamed.txt');
              17  end;
              18  /

              PL/SQL procedure successfully completed.

              SQL>

               

              SY.

              • 4. Re: Rename a file while extraction of a BLOB
                odie_63

                Say Ex: My file name in the table is 'Test.jpg'. And I want to extract it to directory on the server with the name 'SR001-Test.jpg'. How can i acheive this.

                I might be missing something but why not just create the file with the right name in the first place?

                 

                l_output := utl_file.fopen('ORALOAD', 'SR001-' || pfname, 'WB', 32760);


                • 5. Re: Rename a file while extraction of a BLOB
                  Solomon Yakobson

                  odie_63 wrote:

                   

                  I might be missing something but why not just create the file with the right name in the first place?

                   

                   

                   

                  It could be. But it could be (which was my assumption) this is part of some process. For example in some projects I was involed with process A was polling directory for files with certain extension to process. Process B was generating files for process A. Process B was creating files with different extension to prevent process A from grabbing incomplete files. Process B was renaming file when it was complete.

                   

                  SY.

                  • 6. Re: Rename a file while extraction of a BLOB
                    EV259

                    Thank you Odie and SY for your replies.

                    I might be missing something but why not just create the file with the right name in the first place?

                    The file is getting inserted into a BLOB column from the front end. So, I am helpless to change from first place.

                     

                    The below is my procedure. Not sure I followed your comments, but still am making some mistake.

                     

                    CREATE OR REPLACE PROCEDURE demo.save_blob_to_file (ID NUMBER, srnum VARCHAR2)
                    IS
                       v_file             UTL_FILE.file_type;
                       v_buffer           RAW (32767);
                       v_amount           BINARY_INTEGER                 := 32767;
                       v_pos              INTEGER                        := 1;
                       v_blob             BLOB;
                       v_blob_len         INTEGER;
                       v_file_name        demo_db_documents.NAME%TYPE;
                       v_file_exists      BOOLEAN;
                       v_file_length      NUMBER;
                       v_file_blocksize   NUMBER;
                    BEGIN
                       -- Get LOB locator
                       SELECT blob_content, NAME
                         INTO v_blob, v_file_name
                         FROM demo_db_documents
                        WHERE doc_id = ID;

                       v_blob_len := DBMS_LOB.getlength (v_blob);
                       -- Open the destination file.
                       v_file := UTL_FILE.fopen ('BLOB_EXTR', v_file_name, 'wb', 32767);
                       DBMS_OUTPUT.put_line ('File Opened..!!' || v_file_name);

                       WHILE v_pos < v_blob_len
                       LOOP
                          DBMS_LOB.READ (v_blob, v_amount, v_pos, v_buffer);
                          UTL_FILE.put_raw (v_file, v_buffer, TRUE);
                          v_pos := v_pos + v_amount;
                       END LOOP;

                       -- Close the file.
                       UTL_FILE.fclose (v_file);
                       DBMS_OUTPUT.put_line ('File CLOSED ...!!');

                       BEGIN
                          UTL_FILE.fgetattr ('BLOB_EXTR',
                                             'SR001 -' ||v_file_name,
                                             v_file_exists,
                                             v_file_length,
                                             v_file_blocksize
                                            );
                          DBMS_OUTPUT.put_line ('File GET ATTR ...!!');  
                          IF v_file_exists                                                    
                           THEN
                           DBMS_OUTPUT.put_line ('File exists ...!!');
                              UTL_FILE.fremove ('BLOB_EXTR', v_file_name);
                           END IF;
                          UTL_FILE.frename ('BLOB_EXTR',
                                            v_file_name,
                                            'BLOB_EXTR',
                                            'SR001 -' || v_file_name
                                           );
                       END;

                       INSERT INTO test_log
                                   (seq_num,
                                    comments, created
                                   )
                            VALUES (tl_seq.NEXTVAL,
                                    'File has been extracted...!!' || v_file_name, SYSDATE
                                   );
                    EXCEPTION
                       WHEN OTHERS
                       THEN
                          -- Close the file if something goes wrong.
                          IF UTL_FILE.is_open (v_file)
                          THEN
                             UTL_FILE.fclose (v_file);
                          END IF;

                          DBMS_OUTPUT.put_line ('File EXCEPTION ...!!');
                    END;
                    /

                     

                     

                    Error at line 1

                    ORA-29283: invalid file operation

                    ORA-06512: at "DEMO.SAVE_BLOB_TO_FILE", line 73

                    ORA-06512: at line 1

                     

                    Thanks.

                    • 7. Re: Rename a file while extraction of a BLOB
                      odie_63

                      The file is getting inserted into a BLOB column from the front end. So, I am helpless to change from first place.

                      Not what I meant.

                       

                      What's preventing you from constructing the new name while extracting the BLOB back to file, instead of creating it with the original name and renaming it in a separate step ?

                      v_file := UTL_FILE.fopen ('BLOB_EXTR', v_new_filename, 'wb', 32767);

                      • 8. Re: Rename a file while extraction of a BLOB
                        EV259

                        Hi  Odie,

                         

                        I have tried as you said, but not sure some where am missing. Even now the file is getting extracted with the same old name.

                        -- Get LOB locator
                           SELECT blob_content, 'SR-'||NAME
                             INTO v_blob, v_file_name
                             FROM demo_db_documents
                            WHERE doc_id = ID;

                           v_blob_len := DBMS_LOB.getlength (v_blob);
                           -- Open the destination file.
                           v_file := UTL_FILE.fopen ('BLOB_EXTR', v_file_name, 'wb', 32767);
                           DBMS_OUTPUT.put_line ('File Opened..!!' || v_file_name);  --- here i am getting the file name prefixed with 'SR-filename.jpg'. but the extracted file has the name as filename.jpg only

                        Another way

                         

                        -- Get LOB locator
                           SELECT blob_content, NAME
                             INTO v_blob, v_file_name
                             FROM demo_db_documents
                            WHERE doc_id = ID;

                           v_blob_len := DBMS_LOB.getlength (v_blob);
                           -- Open the destination file.
                           v_file := UTL_FILE.fopen ('BLOB_EXTR', 'SR-'||v_file_name, 'wb', 32767);
                           DBMS_OUTPUT.put_line ('File Opened..!!' || v_file_name);

                        Thanks