Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unanswered: Problem with inserting into a blob field

    Hi all,

    I'm having problems with inserting into blob field.
    I need to insert a string (eg.'020070304001A8A0D0101656416200000000070000000 00000000100000002101913346506564162065641622156416 20000000007=1212605510000002POS00686300123456789KL MNOPQRSTUVWXYZ67893600120123456789ABCDEF0300123456 789KLMNOPQRSTUVWXYZ6789'
    which including char & numeric) into a blob field.
    Below is the table structure, SQL statement and error message.

    SQL> desc tbl_audit;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    RAWDATA BLOB


    insert into tbl_audit(rawdata) Values ('020070304001A8A0D0101656416200000000070000000000 00000100000002101913346506564162065641622156416200 00000007=1212605510000002POS00686300123456789KLMNO PQRSTUVWXYZ67893600120123456789ABCDEF0300123456789 KLMNOPQRSTUVWXYZ6789');

    ERROR at line 1:
    ORA-01465: invalid hex number


    How can i resolve this? Thanks!

    Best Regards,
    Jason

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    BLOB is a Binary LOB. It looks like you want to use a Character LOB, or CLOB.

  3. #3
    Join Date
    Dec 2003
    Posts
    8

    Thanks!

    Thanks Ivon! I will try to switch to CLOB. by the way, if in future my data will consists other than characters, can i use BLOB for both character data & bitmap data?

    Warmest Regards,

    Jason

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    BLOB is a Binary LOB. It looks like you want to use a Character LOB, or CLOB.
    Beware, CLOBs are for character data : a clob column will be converted between server and client character sets. It is NOT to be used for storing pure binary data such as images or pdf docs... On the contrary, a BLOB column is always considered "as is" and will always be copied byte to byte between client and server, without any charset converting, so it is what should be used for images and so on.

    CLOBs follow CHARACTER semantics whereas BLOB follows BYTE semantics, that is why, among other things, you can define a domain index (for Full-Text Searches) on a CLOB but not on a BLOB.

    In short, if you want to store TEXT data use a CLOB and if you want to store BINARY data use a BLOB.

    insert into tbl_audit(rawdata) Values ('020070304001A8A0D0101656416200000000070000000000 00000100000002101913346506564162065641622156416200 00000007=1212605510000002POS00686300123456789KLMNO PQRSTUVWXYZ67893600120123456789ABCDEF0300123456789 KLMNOPQRSTUVWXYZ6789');

    ERROR at line 1:
    ORA-01465: invalid hex number
    AFAIK there is currently no function that can convert char to raw, so the string you pass is considered as an Hexadecimal value and is silently converted to raw with HEXTORAW, but as your value is not a correct hexadecimal value, it fails (that is what I think is happening, though not 100% sure). It should work if you put the value into a file and then read it in binary mode with UTL_FILE so as to put it in a raw variable, and it will work fine through a client API such as OCI, OCCI or Java, but you I think you won't be able to insert raw data into a blob from SQL*Plus, or at least I've not found how to do so .

    HTH and Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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