ORA-01465: invalid hex number in oracle while using BLOB

i am designing a database in oracle 11g. I have designed a table with fields,

CUST_ID, NUMBER(5) //this is a foreign key Review, BLOB //to store big strings Date, SYSDATE 

now when i'm trying to insert data in the table like-

insert into "ReviewTable" values ( 3, 'hello, this is the first review',SYSDATE) 

it gives [Err] ORA-01465: invalid hex number. If someone can help me with the error?

1 Answer

you cast your string into BLOB, you can do this via package utl_raw.cast_to_raw or convert varchar to clob via to_clob('mystring') and then use procedure DBMS_LOB.convertToBlob in your code

but if you are going to use the fields for string why don`t save them as a CLOB?

Here are 2 examples below with BLOB and CLOB fields

BLOB

create table ReviewTable( CUST_ID NUMBER(5) ,Review BLOB ,Dt Date); insert into ReviewTable values ( 3, utl_raw.cast_to_raw('hello, this is the first review'),SYSDATE); 

CLOB

create table ReviewTable2( CUST_ID NUMBER(5) ,Review CLOB ,Dt Date); insert into ReviewTable2 values ( 3, 'hello, this is the first review',SYSDATE);