DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Retriving binary file from blob column

  1. #1
    Join Date
    Oct 2002
    Location
    India
    Posts
    42

    Retriving binary file from blob column

    Hi

    I have inserted a BLOB object(a movie file) into a database column (datatype-BLOB) using the function bfilename and dbms_lob package. It was done successfully without any problems. But unfortunately I lost the physical file (.dat file) and now want to retrive that file from the database column. Is there any way using PL/SQL to read that BLOB column and write into the OS file. I am using oracle 9i release 2 on windows 2000 professional.

    Thanks
    Burzin

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    May be utl_file.put_raw will help you.

    I have not done it so far.

    Tamil

  3. #3
    Join Date
    Oct 2002
    Location
    India
    Posts
    42

    Thanks

    Hi

    Thanks a lot. I atleast got a hint. I was viewing the 9.0 documentation and there is no function as "put_raw" but I found it in the 9.2 documentation (Release 2). There is "fflush" also which may be useful. I shall try using them and get back in a day or two.

    Thanks again
    Burzin

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hope this helps:
    v_pos := 1;
    WHILE v_pos < v_blob_len LOOP
    DBMS_LOB.READ (v_blob, 32767, v_pos, v_buffer);
    UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
    v_pos := v_pos + 32767;
    END LOOP;
    UTL_FILE.FCLOSE(v_file);
    Cheers!
    OraKid.

  5. #5
    Join Date
    Oct 2002
    Location
    India
    Posts
    42

    It Does not work

    v_pos := 1;
    WHILE v_pos < v_blob_len LOOP
    DBMS_LOB.READ (v_blob, 32767, v_pos, v_buffer);
    UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
    v_pos := v_pos + 32767;
    END LOOP;
    UTL_FILE.FCLOSE(v_file);

    The code given above gives me the error
    ORA-29285: file write error
    at line 4 i.e., UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);

    I tried writing a test string into the same file using utl_file.put to check if it writes and if all permissions are given. It wrote the test string successfully. Only raw data is not being written. Here is the procedure that I have written.

    CREATE OR REPLACE PROCEDURE SaveBLOB IS
    lobd BLOB;
    fils utl_file.file_type;
    rbuf raw(32767);
    amt INTEGER := 32767;
    pos integer:=1;
    BEGIN
    fils:=utl_file.fopen('DIR1','avseq02.dat','w');
    SELECT blob_col INTO lobd FROM cust1 WHERE cust_id=0;
    dbms_lob.open(lobd,dbms_lob.lob_readonly);
    loop
    dbms_lob.read(lobd,amt,pos,rbuf);
    utl_file.put_raw(fils,rbuf,true);
    pos:=pos+32767;
    /*Write part of the lob since the length of the lob is 400m*/
    if pos>400000 then
    exit;
    end if;
    end loop;
    dbms_lob.close(lobd);
    utl_file.fclose(fils);
    dbms_output.put_line('file successfully created');
    END;
    /

    I get the same error when writing raw data. Is there something that is being missed out ?

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width