5 Replies Latest reply: Oct 30, 2013 6:12 AM by BluShadow RSS

    Get textual contents from BLOB in Oracle SQL

    936666

      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: