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"><Test><buffer><A></buffer></Test>' );
I'm getting the following error: ORA-01465: invalid hex number.
Sorry, I went ahead and registered - now I don't know how to comment on this question other than through the answers...
Ok, here's the question with more details: when I said binary I meant blob.
So, I have the following table:
sample_table:
id : NUMBER
type : NUMBER
version : NUMBER
data : BLOB
Doing the following command:
insert into sample_table values (1, 0, 1, '<?xml version="1.0" encoding="UTF-8"><Test><buffer><A></buffer></Test>');
Gives me the error described in the original question.
Thanks.
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>