I want to manually insert an ASCII stream into a column of type BLOB in oracle

Is there a command that will convert ASCII into blob?

I have the following table:

sample_table:
-------------
id : NUMBER 
type : NUMBER 
version : NUMBER 
data : BLOB

When doing the following command:

insert into sample_table 
values 
   (1, 0, 1, '<?xml version="1.0" encoding="UTF-8开发者_JAVA技巧"><Test><buffer><A></buffer></Test>' );

I'm getting the following error: ORA-01465: invalid hex number.

Okay, so what is happening is this:

SQL> insert into t42
  2      (id, version, type, data)
  3      values (2, 0, 2, '<?xml version="1.0" encoding="UTF-8"><Test><buffer><A></buffer></Test>')
  4  /
    values (2, 0, 2, '<?xml version="1.0" encoding="UTF-8"><Test><buffer><A></buffer></Test>')
                     *
ERROR at line 3:
ORA-01465: invalid hex number


SQL>

This is because you are inserting ASCII text in a binary field. Binary fields expect Hexadecimal characters, and things like <. ?, = , etc ain't hex. What you need to do is cast it into hex, like this:

SQL> insert into t42
  2      values (2, 0, 2, utl_raw.cast_to_raw('<?xml version="1.0" encoding="UTF-8"><Test><buffer><A></buffer></Test>'))
  3  /

1 row created.

SQL> select * from t42
  2  /

        ID       TYPE    VERSION
---------- ---------- ----------
DATA
--------------------------------------------------------------------------------
         2          0          2
3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223E3C5465
73743E3C6275666665723E3C413E3C2F6275666665723E3C2F546573743E


SQL>

This works easily because I am using Oracle 11g. If you are on a much earlier version of Oracle - definitley 8, possibly 9 - then you might have to use a two-step process. First insert a marker like this:

SQL> insert into t42
  2      values (1, 0, 1, empty_blob())
  3  /

1 row created.

SQL>

You can then populate the BLOB column like this:

SQL> declare
  2      b blob;
  3  begin
  4      b := utl_raw.cast_to_raw(rpad('FF',32767,'FF'));
  5      update t42
  6      set data = b
  7      where id = 1;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from t42
  2  /

        ID       TYPE    VERSION
---------- ---------- ----------
DATA
--------------------------------------------------------------------------------
         1          0          1
46464646464646464646464646464646464646464646464646464646464646464646464646464646
46464646464646464646464646464646464646464646464646464646464646464646464646464646

SQL>

Incidentally, if you're storing XML data, why store it as a BLOB rather than either as character data (CLOB) or from 9i onwards the native XMLType type? Well, one reason for not using XMLType is that your text is not well-formed XML, but I presume that is just an oversight ;)

SQL> alter table t42 add xdata xmltype;

Table altered.

SQL> insert into t42
  2      (id, version, type, xdata)
  3      values (4, 0, 2, xmltype('<?xml version="1.0" encoding="UTF-8"><Test><buffer><A></buffer></Test>'))
  4  /
    values (4, 0, 2, xmltype('<?xml version="1.0" encoding="UTF-8"><Test><buffer><A></buffer></Test>'))
                     *
ERROR at line 3:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00254: invalid XML declaration
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1


SQL> insert into t42
  2      (id, version, type, xdata)
  3      values (4, 0, 2, xmltype('<?xml version="1.0" encoding="UTF-8"?><Test><buffer>A</buffer></Test>'))
  4  /

1 row created.

SQL> select * from t42
  2  /


        ID       TYPE    VERSION
---------- ---------- ----------
DATA
--------------------------------------------------------------------------------
XDATA
--------------------------------------------------------------------------------
         4          2          0


<?xml version="1.0" encoding="UTF-8"?><Test><buffer>A</buffer></Test>

SQL>