This discussion is archived
5 Replies Latest reply: Oct 30, 2013 4:12 AM by BluShadow RSS

Get textual contents from BLOB in Oracle SQL

936666 Newbie
Currently Being Moderated

how to get the text content from BLOB

 

I need to insert BLOB data as input and not as from directory what is the data type i need to use in PL/SQL or stored procedure to get the BLOB information and store towards BLOB/CLOB data type.

 

create table gh (c1 clob);

declare l_blob clob;

begin

l_blob :='ôýÔxÑ£ÖÀÝ$  $¯À';

insert into gh

Select l_blob from dual;

end;

 

select utl_raw.cast_to_varchar2(dbms_lob.substr(c1)) from gh

 

select utl_raw.cast_to_varchar2(dbms_lob.substr(c1)) from gh

Error report:

SQL Error: ORA-01465: invalid hex number

01465. 00000 -  "invalid hex number"

*Cause:   

*Action:

 

where as i create a table as BLOB data type

create table gh1 (c1 Blob)

declare l_blob Blob;

begin

l_blob :='ôýÔxÑ£ÖÀÝ$  $¯À';

insert into gh1

Select l_blob from dual;

end;

 

Not able to insert the data getting error

 

Error report:

ORA-06550: line 3, column 11:

PLS-00382: expression is of wrong type

ORA-06550: line 3, column 2:

PL/SQL: Statement ignored

06550. 00000 -  "line %s, column %s:\n%s"

*Cause:    Usually a PL/SQL compilation error.

*Action:

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points