Results 1 to 6 of 6

Thread: doubt on blobs

  1. #1
    Join Date
    Dec 2011
    Posts
    7

    Unanswered: doubt on blobs

    how to insert(load) a file from UNIX into a column having BLOB as the datatype..
    please reply...

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Use SQL Loader

  3. #3
    Join Date
    Dec 2011
    Posts
    7
    sql loader cannot be used,, do you have a different solution ?? kindly share plz

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >sql loader cannot be used,, do you have a different solution ?? kindly share plz

    what can you use?

    does file reside om DB Server?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2011
    Posts
    7
    i have done it for clobs but dont know how to do it the same way for blobs.
    below is the approach for CLOBS :
    ++++++++++++++++++++++++++++++++++++++++
    CREATE OR REPLACE PROCEDURE load_clob_data AS
    var_file_handle utl_file.file_type;
    file_location VARCHAR2(100);
    text VARCHAR2(5676);
    var_clob CLOB;
    BEGIN
    file_location := '/eb/allib/crtb';
    var_file_handle := utl_file.fopen(file_location, 'test.xml', 'r');
    LOOP
    BEGIN
    utl_file.get_line(var_file_handle, text);
    DBMS_OUTPUT.put_line(text);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    EXIT;
    END;
    var_clob := var_clob ||text || CHR(13);
    END LOOP;
    INSERT INTO clob_tab(datetime_inserted,datetime_start,data) VALUES(to_date('12/12/2011 13:11:15','dd/mm/yyyy hh24:mi:ss'),to_date('12/12/2011 13:10:10','dd/mm/yyyy hh24:mi:ss'),var_clob);
    commit;
    utl_file.fclose(var_file_handle);
    END;

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Smile

    The following script demonstrates reading a binary from the filesystem and inserting it into a table.
    Code:
    dayneo@RMSD> create table tbl_of_files (
      2    filename varchar2(255),
      3    filedata blob
      4  )
      5  /
    
    Table created.
    
    dayneo@RMSD> 
    dayneo@RMSD> declare
      2  
      3  	     l_location varchar2(32767);
      4  	     l_filename varchar2(32767);
      5  	     l_filedata blob;
      6  	     l_hnd	utl_file.file_type;
      7  	     l_buf	raw(32767);
      8  
      9  begin
     10  
     11  	     l_location := 'EXPDP_DIR';
     12  	     l_filename := 'mybin.exe';
     13  
     14  	     insert into tbl_of_files
     15  			 values(l_filename, EMPTY_BLOB)
     16  	     returning filedata into l_filedata;
     17  
     18  	     l_hnd := utl_file.fopen(l_location, l_filename,
     19  				     'rb', 32767);
     20  	     begin
     21  		     loop
     22  	      utl_file.get_raw(l_hnd,
     23  					      l_buf, 32767);
     24  	      dbms_lob.writeappend(l_filedata,
     25  						  utl_raw.length(l_buf),
     26  										     l_buf);
     27  		     end loop;
     28  	     exception
     29  		     when NO_DATA_FOUND then
     30  			     utl_file.fclose(l_hnd);
     31  		     when OTHERS then
     32  			     utl_file.fclose(l_hnd);
     33  			     raise;
     34  	     end;
     35  
     36  end;
     37  /
    
    PL/SQL procedure successfully completed.
    
    dayneo@RMSD> 
    dayneo@RMSD> column filename format a25
    dayneo@RMSD> column bytes    format 9,999,999,999 JUSTIFY right
    dayneo@RMSD> select filename, length(filedata) bytes
      2    from tbl_of_files
      3  /
    
    FILENAME                           BYTES
    ------------------------- --------------
    mybin.exe                         67,072
    
    dayneo@RMSD> 
    dayneo@RMSD> drop table tbl_of_files
      2  /
    
    Table dropped.
    
    dayneo@RMSD>
    The difference between reading text and binary is really quite simple. Firstly, you must use 'rb' for the open_mode in the call to UTL_FILE.FOPEN. To read data from the file, you will need to use UTL_FILE.GET_RAW and pass it a variable of RAW datatype. You can then use DBMS_LOB.WRITEAPPEND to append the RAW variable buffer onto your BLOB variable. It is pretty much identical to reading from a text file.

Posting Permissions

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