BLOB -- Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: BLOB -- Help

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Angry

    hi all,

    can u walk me thru this code?
    i dont under standanything :(

    CREATE TABLE blob_table(id number, src BLOB);
    ----------------
    CREATE DIRECTORY util AS '/opt/oracle/utldir';
    ----------------
    declare
    LobRef BLOB;
    FilRef BFILE := bfilename('UTIL','Acct.txt');
    NextId number := 1;
    begin
    insert into blob_table values (NextId,empty_blob());
    select src into LobRef from blob_table where id = NextId;
    dbms_lob.open(LobRef,dbms_lob.lob_readwrite);
    dbms_lob.fileopen(FilRef, dbms_lob.file_readonly);
    dbms_lob.loadfromfile(LobRef,FilRef,dbms_lob.getlength(FilRef));
    dbms_lob.fileclose(FilRef);
    dbms_output.put_line( 'Len='||dbms_lob.getlength(LobRef));
    dbms_lob.close(LobRef);
    end;
    Cheers!
    OraKid.

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    OK.

    - You create a table containing a BLOB and a directory object that points to the location of your file.

    - You create a reference to an external file as a bfile.

    - You insert a new record with an empty BLOB, then return the reference (locator) of the BLOB. This could be done in one go using the following syntax:

    insert into blob_table values (NextId,empty_blob()) return src into LobRef;

    - The external file is opened and it's whole contents is read into the LOB referred to by the LobRef locator.

    - The external file is then closed.

    This is the standard whay to load a BLOB or CLOB.

    See:

    http://www.oracle-base.com/Articles/8i/ImportBlob.asp

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    thnx a lot Tim
    geme some Oracle Proc*C examples.
    Rgds,
    Cheers!
    OraKid.

  4. #4
    Join Date
    Oct 2002
    Posts
    2

    Inserting pdf files in a blob column

    In this example you have inserted empty_blob() in the blob column. how do i insert a value in it. say the value which is stored in FilRef BFILE := bfilename('UTIL','Acct.txt');
    i.e, i want to insert the ACCT.txt & also retriev it. how can I retrieve it.

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