1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to save a Blob into local file system

Discussion in 'SQL PL/SQL' started by justlife, Jan 13, 2010.

  1. justlife

    justlife Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have a table which has a blob column in which most of the regular filetypes can be stored.

    What is the best/easiest way to select/query this table and write all the blob contents into a local file system?

    Am using oracle 10.2.

    Is there a way of somehow batch processing all the blobs and saving them to my local drive?

    Any and all help is appreciated.

    Thank you.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Not so easy .. You have to
    1. Save the the Blob data into a file in the server.
    2. Then move the file from server to local server using FTP . or Oracle inbuilt ftp package.
     
  3. justlife

    justlife Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I was able to do that using a pl/sql procedure which used DBMS_LOB and UTL_FILE.put_raw .

    It works good. Now, I just need to figure out how to pass a table name as a parameter and use that as a variable in my cursor.

    My below procedure works perfectly when the table name is specified.

    CREATE OR REPLACE procedure SAVE_TO_FILE_FILE1
    IS
    l_file UTL_FILE.FILE_TYPE;
    l_buffer RAW(32767);
    l_amount BINARY_INTEGER := 32767;
    l_pos INTEGER := 1;
    l_blob BLOB;
    l_blob_len INTEGER;
    MYFILE_NAME VARCHAR2(250);
    P_TABLENAME varchar2(100):= MYTABLE;
    P_SQL_STATEMENT VARCHAR2(1000);

    CURSOR C1 IS SELECT ID From TABLE123 Where FILE_NAME1 IS NOT NULL;

    BEGIN

    FOR I IN C1 LOOP
    -- Get LOB locator
    SELECT file1,file_name1 INTO l_blob,MYFILE_NAME FROM TABLE123 where ID= I.ID;

    l_blob_len := DBMS_LOB.getlength(l_blob);
    l_pos:= 1;
    -- Open the destination file.
    l_file := UTL_FILE.fopen('MY_FILES',MYFILE_NAME,'wb', 32767);

    -- Read chunks of the BLOB and write them to the file
    -- until complete.
    WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
    END LOOP;

    -- Close the file.
    UTL_FILE.fclose(l_file);

    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
    -- Close the file if something goes wrong.
    IF UTL_FILE.is_open(l_file) THEN
    UTL_FILE.fclose(l_file);
    END IF;
    RAISE;

    end SAVE_TO_FILE_FILE1;

    Now if I could pass a table name as a parameter and then access it, it would make my process so much more easier.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I doubt you can do what you want without some serious programming as explicit cursors cannot use dynamic constructs. Of course if you pass in the ID value along with the table name you could use native dynamic SQL to populate your PL/SQL variables. You could also use a ref cursor as an input parameter and pass in the result set you're currently generating, looping through the ref cursor and processing as you have currently coded. This might require a second procedure to open the ref cursor and pass it to this procedure.

    If you feel up to coding such a solution we'll be happy to assist you in getting it to work.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Yes .. You can if I understand your query well.

    You can try Refcursor + Open refcursor For construct in single procedure. You can pass the table name and field names as input.

    code will be like ,

    Code (SQL):

    OPEN refcur FOR 'SELECT '||field1||' From '||tablename||' Where '||filed2||' IS NOT NULL';
     
    See the referece