Results 1 to 6 of 6

Thread: Blob ??

  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Unanswered: Blob ??

    hi,

    I want an example which make use of the BLOB datatype.

    or else

    details like
    How to insert into a BLOB datatype column ,what value can i insert..


    Thanx in advance.

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI Raseena,

    consider the following table,

    desc test_lobs

    Name Null? Type
    ------- -------- ---- --
    C1 NUMBER
    C2 CLOB
    C3 BINARY FILE LOB
    C4 BLOB


    Insert some rows. The empty_<lob>() function acts as a constructor to generate a LOB locator for that column.Without a locator that LOB cannot be accessed through PL/SQL etc.

    First row - no locators.

    insert into test_lobs values (1,null,null,null) /

    Second row - "null" locators - ie locators created but point to nothing.
    Note there is not an empty locator as such for the BFILE though we could also initialise the BFILE using BFILENAME with a null directory and file, ie BFILENAME(null,null).

    insert into test_lobs values (2,EMPTY_CLOB(),null,EMPTY_BLOB()) /

    Third row - it is possible to insert data directly up to 4K.Even though you are only really accessing the locator, the data is stored as appropriate behind the scenes. When inserting directly into a BLOB either the string must be hex as an implicit HEXTORAW will be done or you can call UTL_RAW.CAST_TO_RAW('the string') to convert it for you. Note '48656C6C6F' = 'Hello'.

    insert into test_lobs values (3,'Some data for record 3.', BFILENAME(null,null),'48656C6C6F'||UTL_RAW.CAST_TO _RAW('there!')) /


    Try and select back the data.

    select * from test_lobs /

    It gives:Column or attribute type can not be displayed by SQL*Plus SQL*Plus cannot convert the data behind the locator to hex for the BLOB nor interpret a locator for a BFILE (even when null). Hence: column c2 format a60 wrap select c1, c2 from test_lobs /

    C1 C2
    ------------ -------------------------------------------------- --
    1
    2
    3 Some data for record 3.

    In the above case we are really fetching only the LOB locator. SQL*Plus will also then fetch the corresponding data. If we use a 3GL or PL/SQL we can insert data from a character string variable but not select it into one.

    For example:

    declare
    c_lob varchar2(10);
    begin
    c_lob := 'Record 4.';
    insert into test_lobs values (4,c_lob,BFILENAME(null,null), EMPTY_BLOB());
    end;
    /
    works, but:

    declare
    c_lob varchar2(10);
    begin
    select c2 into c_lob from test_lobs where c1 = 4;
    end; /

    gives: ERROR at line 4:
    ORA-06550: line 4, column 19: PLS-00385: type mismatch found at 'C_LOB' in SELECT...INTO statement
    ORA-06550: line 4, column 4: PL/SQL: SQL Statement ignored
    SATHISH .

  3. #3
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Thumbs down Blob Example

    First Create a table

    Create table pictable
    (Title varchar2(10),
    Pic Blob
    );

    Then make a form in Dev6i / 9i.
    use the built-in function 'read_image_file'
    from Hard disk drive and then save it,
    hello

  4. #4
    Join Date
    Nov 2003
    Posts
    87
    hi satish,

    while creating table...im getting the following err.

    create table test_lobs
    (c1 number,
    c2 clob,
    c3 binary file lob,
    c4 blob);

    bfile_col binary file lob)
    *
    ERROR at line 5:
    ORA-00907: missing right parenthesis



  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333
    Hi Raseena,

    Try this

    create table test_lobs (
    c1 number,
    c2 clob,
    c3 bfile,
    c4 blob ) ;


    There's no datatype as binary file lob. It's bfile.
    SATHISH .

  6. #6
    Join Date
    Nov 2003
    Posts
    87
    hi satish,

    u've written that while executing the following procedure , we will get errors..

    declare
    c_lob varchar2(10);
    begin
    select c2 into c_lob from test_lobs where c1 = 4;
    end; /


    but this got worked me fine........

    so vatz the error u've showed??

Posting Permissions

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