Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Unanswered: text blob field to varchar2

    I have a feeling, this type of thing will require a pl/sql function...but I thought it would be worth asking.....

    For a table with a blob datatype holding text, how would I capture the first 20 characters as a varchar2 type in a SELECT...

    in a perfect world...something like....

    Code:
    SELECT substr(to_varchar2(<my_blob_column>),1,20)
    FROM my_table

    thanks!
    d
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    It would work, but only with a CLOB or NCLOB columns (which have character semantics), not a BLOB one. BTW, you would not have to use To_Char (To_Varchar2 doesn't exist ), you would simply use SUBSTR(clob_column,1,20). In fact, SUBSTR works with any of these datatypes : CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

    See the "SQL Reference" doc for more info on the use of SUBSTR and other Oracle SQL functions.

    Best Regards,

    RBARAER
    Last edited by RBARAER; 01-04-05 at 12:25.

  3. #3
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    cool...

    thanks for the reply....it got my going on the right track....

    Code:
    create table test_blob
    (col1 blob);
    
    INSERT into test_blob (col1)
    select utl_raw.cast_to_raw('hello') as col1 from dual;
    
    SELECT utl_raw.cast_to_varchar2(utl_raw.substr(col1,1,5)) from test_blob;
    I'm good to go now.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I'm glad you found your answer.

    However, let me give you a piece of advice : if it is only text data that you want to put in your BLOB, I would really advise you to use a CLOB (again, a CLOB has character semantics a BLOB has not). For example, domain indexes (Oracle Text) can be used on CLOBs but not BLOBs. As you've already seen, the same applies to several SQL functions.

    To sum it up : if you want to insert files, such as video, mp3 or anything else that means nothing as a string, but is just a chain of bytes for the db, then OK for a BLOB, that's a BLOB's job. But if you want to insert text that may be (or will be) interpreted as such in the future, use a CLOB, that's why CLOBs exist.

    HTH.

    Regards,

    RBARAER

  5. #5
    Join Date
    Feb 2013
    Posts
    1

    Cool BLOB to Varchar

    HI ,

    I am trying to fecth the text from Blob , but my funtion is not returning exactly what i am looking for instead i am getting null .


    CREATE OR REPLACE function test_alert_msg(v_rowid rowid) return varchar2 is

    vblob BLOB;
    vstart NUMBER := 1;
    bytelen NUMBER := 32000;
    len NUMBER;
    my_vr RAW(32000);
    x NUMBER;


    BEGIN

    -- select blob into variable
    SELECT GDTXFT
    INTO vblob
    from jdedta.f00165 m
    where rowid = v_rowid;

    -- get length of blob
    SELECT dbms_lob.getlength(GDTXFT)
    INTO len
    from jdedta.f00165 m
    where rowid = v_rowid;

    -- save blob length
    x := len;
    vstart := 1;

    -- if small enough for a single write
    IF len < 32760 THEN
    dbms_output.put_line('TESTING');
    DBMS_LOB.READ(vblob,len,vstart,my_vr);
    dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(my_v r));

    ELSE -- write in pieces
    DBMS_LOB.READ(vblob,bytelen,vstart,my_vr);
    dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(my_v r));
    END IF;

    vstart :=vstart+bytelen;

    WHILE (vstart < len)
    LOOP
    dbms_output.put_line('vstart : ' || to_char(vstart));
    dbms_lob.read(vblob,bytelen,vstart,my_vr);
    dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(my_v r));
    -- set the start position for the next cut
    vstart := vstart + bytelen;
    -- set the end position if less than 32000 bytes
    vstart :=vstart+bytelen;


    end loop;
    return(UTL_RAW.CAST_TO_VARCHAR2(my_vr));
    dbms_output.put_line('TESTER');
    dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(my_v r));
    end test_alert_msg;

    Also from the above forum , i can this is not working in 11g

    UTL_RAW.CAST_TO_VARCHAR2(my_vr)

    SELECT utl_raw.cast_to_varchar2(utl_raw.substr(GDTXFT,1,3 00)) from JDEDTA.f00165 where rowid='AAAeoSABkAAA14ZAAD';

    I am getting no data ..

    Please kindly help...

Posting Permissions

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