XML value cannot convert to BLOB

Question

When I retrieve the xml file from the front-end I try to put the data from xml into blob column in oracle table. But I get Following error;

ORA-01465: invalid hex number

Front end they sending us binary object type.

Here the XML Tags contain following details.

<Contents>MzEvMDcvMjAxNA0KMS4gRmluZCBhIHdheSB0byBzdG9wIHBvc3RiYWNrIGluIFRyZWUgR3JpZCBpbiBDYWhydCBPZiBBY2NvdW50cyBNb2R1bGUgLSBEb25lDQoyLiBJcyBEaXNidXJzZSBGb3JtdWxhIHNhbWUgYXMgRm9ybXVsYQ0KMDYvMDgvMjAxNA0KMS5DaGFuZ2UgRGViaXRDcmVkaXQgdG8gRGViaXRDcmVkaXROb3RlDQoyOS8wOC8yMDE0DQoxLlNhbGVzIC0gQXV0aGVudGljYXRpb24gcmVxdWlyZWQgZm9yIG1vcmUgb3B0aW9ucw0KMi5IaXN0b3J5IC0gT3B0aW9uIHRvIHZpZXcgaXRlbSBsaXN0DQozLkNhc2hUaWxsIC0gT3B0aW9uIHRvIHNwZWNpZnkgSW4vT3V0DQowMy8wOS8yMDE0DQooVG8gQ2FzaCBUaWxsKQ0KMS5BZGQgQ2xlYXIgQnV0dG9ucyB0byBidXR0b25zIHN0cmlwIC0gRG9uZQ0KMi5TZWFyY2ggbm90IHdvcmtpbmcNCjMuRG91YmxlIGNsaWNrIGZlYXR1cmUgdG8gaW5jcmFzZSBpdGVtcyAtIERvbmUNCjQuQWRkIENsZWFyICYgUmVtb3ZlIGJ1dHRvbiB0byBrZXkgcGFkIC0gRG9uZQ0KNS5HaXZlIGFuIG9wdGlvbiB0byBjaGFuZ2UgY29sb3IgYW5kIGltYWdlIGR5bmFtaWNhbGx5Lg0KNi5NYWtlIGRlZmFsdXQgdGhyZWUgYnV0dG9ucyBhIHJvdyAtIERvbmUNCjcuQXJyYW5nZSBCdXR0b25zDQowOS8wOS8yMDE0DQoxLk1vZGlmeSBNYXN0ZXIgVUkgYXMgZGlzY3Vzc2VkDQoyLkZpbmFsaXplIENhc2ggVGlsbCB0byBFRg0KMy5EYXRhIFBhZ2UgdG8gcHVzaCBhbmQgcHVsbCBkYXRhIEN1c3RvbWVyL0l0ZW0vTGVnYWwgVGVuZGVycw0KMTkvMDkvMjAxNA0KMS4gU3luY2hvbml6ZWQgYml0IHRvIGNoZWNrIFNhbGVzIGFucyBTYWxlcyBSZXR1cm5zIGJlZm9yZSBpbml0aWFsIGRhdGEgbG9hZCBhbmQgc2hvdyBtZXNzYWdlLg0KMi4gQWRkIHByb2dyZXNzIGJhciBhbmQgYnV0dG9ucyBmb3IgUHJvZHVjdCwgQ3VzdG9tZXIgYW5kIFByb21vdGlvbnMgU2VwZXJhdGVseS4NCjMuIEZpbmFsaXplIHNlc3Npb24gZm9yIGxvZ2luIC0gRG9uZQ0KMjIvMDkvMjAxNA0KMS4gSW50ZXJmYWNlIGZpbmFsaXphdGlvbiAtIERvbmUNCjIuIEdldCBtZXNzYWdlIGZvciB1bnN5bmNlZCBkYXRhLiBJbnZvaWNlL1NhbGVzIFJldHVybi9Wb3VjaGVyL0NvdXBvbi9PcGVyYXRvci9DYXNoVGlsbC9TZXNzaW9uSW5mb3JtYXRpb24gLSBEb25lDQozLiBTZW5kIFVuc3luY2VkIGRhdGEgdG8gc2VydmVyLiBDcmVhdGUgYSBzY3JlZW4uIFNlcGVyYXRlIGRhdGEgcGFnZSAtIERvbmUNCjIzLzA5LzIwMTQNCjEuIFZlcmlmeSBkb3dubG9hZCBkYXRhIC0gRG9uZQ0KMi4gVmVyaWZ5IGluaXRpYWxEYXRhIExvYWQgd2l0aCBtZXNzYWdpbmcNCjMuIE5ldyBvcHRpb24gdG8gZ2V0IG9wZXJhdG9ycyBzZXBlcmF0ZWx5IC0gRG9uZQ0KNC4gR2V0IHByb2R1Y3RzIHNob3VsZCBpbmNsdWRlIHByb21vdGlvbnMgLSBEb25lDQo1LiBSZW1vdmUgZGF0YSB1cGxvYWQvZG93bmxvYWQgdmFsaWRhdGlvbiAtIERvbmUNCjYuIE9wZXJ0b3IgcGFzc3dvcmQgY2hhbmdlIHNob3VsZCB3b3JrIHdpdGggc2VydmljZS4gcmVtb3ZlIGxvY2FsIHBhc3N3b3JkIHNhdmUuIC0gRG9uZQ0KMjQvMDYvMjAxNA0KMS4gU2FsZXMgUmV0dXJuIGRhdGEgdXBsb2FkIC0gRG9uZQ0KMi4gTWVzc2FnaW5nIHdpdGggc2NyZWVuIGZpbmV0dW5pbmcNCjMuIFhtbCBnZW5lcmF0aW9uIC0gRG9uZQ0KMjUvMDkvMjAxNA0KMS4gRGVmaW5lIHBybyBjbGFzcyBiYXNlIG9uIHRoZSBzYWxlcyBncmlkLiAtIERvbmUNCjIuIEFzeW5jIHRvIFN5bmMgbWV0aG9kIGluIGxvZ2luIC0gRG9uZQ0KMy4gWG1sIHZlcmlmaWNhdGlvbiBmb3IgZGF0YSB1cGxvYWQNCjI5LzA5LzIwMTQNCjEuIA0KMi4gSW52b2ljZSBJbnNlcnQgdG8gbG9jYWwgZGF0YWJhc2UsIA0KMDEvMTAvMjAxNA0KMS4gU2VuZCBvcGVyYXRvciBzZXNzaW9uIGJlZm9yZSBkYXRhIHVwbG9hZCAtIERvbmUNCjIuIEltcGxlbWVudCBwcmV2aWxhZ2VzDQozLiBSZXR1cm4gbWV0aG9kcyBpbnRlcmZhY2UgbW9kaWZpY2F0aW9uDQoxMC8wOS8xNA0KMS4gU2Vzc2lvbiwgU2Vzc2lvbiBEZXRhaWwsIENhc2ggVGlsbCwgU2FsZXMsIFNhbGVzIFJldHVybg0KMi4gUGFzd29yZCBjYW4gYmUgY2hhbmdlZCBvbmx5IHRoZSB1c2VyIGlzIG9ubGluZS4gUGFzc3dvcmQgd2lsbCBiZSB1cGRhdGVkIG9uY2UgdGhlIHVwbG9hZCBjb25maXJtZWQgYnkgdGhlIHNlcnZpY2UuDQozLiBDYXNoIHRpbGwgbmVlZHMgdG8gcHJvdmlkZSBhIGxpc3Qgb2YgdXNlcnMgd2hvIGhhcyBhbiBhY3RpdmUgY2FzaHRpbGwuDQoxMS8wOS8yMDE0DQoxLiBDYXNodGlsbCBJc3N1ZQ0KMi4gU2hvcCBDbG9zZSBDYXNoVGlsbHMgbXVzdCBiZSBjbG9zZWQuIEFsbCB1c2VycyBtdXN0IGJlIGxvZ2dlZCBvdXQsIGFsbCBkYXRhIG11c3QgYmUgc3luY2VkLg0KMy4gRGF0YSBVcGxvYWQNCjQuIFJlbW92ZSBPQkpDRVQgd3JhcHBlciBpbiBwYXNzd29yZCBjaGFuZ2UgWE1MDQo1LiBDaGVjayB0ZXJtaW5hbCBvcGVycmF0b3JzIGluc2VydCBpbiBkYXRhIGRvd25sb2FkDQo2LiBTZXQgdG8gMTAgd2hlbiB1bmFibGUgdG8gY3JlYXRlIHNlc3Npb24NCjcuIHNhbWUgZ29lcyB0byBhcHByb3ZhbA0KMTUvMDkvMjAxNA0KMS4gU2hvdyBkaXNjdW50IHByZWNlbnRhZ2Ugb24gQmVmb3JlIEludm9pY2UgKERvY2tldCBEaXNjb3VudCwgT3ZlcnJpZGUgRGlzY291bnQpDQoyLiBJbnZvaWNlIHJldHVybiBhbW91bnQvSW52b2ljZSBEYXRlDQoyMi8xMC8yMDE0DQoxLiBEb2NrZXQgaGlzdG9yeSBwcmludGluZyAtIERvbmUNCjIuIGRvY2tldCBzY2FuIHNlYXJjaCB3aXRob3V0IHNlYXJjaCBidXR0b24gLSBEb25lDQozLiByZXR1cm4gYW1vdW50IG5vdCBjYWxjdWxhdGluZyBhZnRlciBzY2FuIC0gRG9uZQ0KNC4gY2xlYXIgdGV4dGJveCBhZnRlciBzY2FuIC0gRG9uZQ0KDQo=</Contents>

This the query. I try to retrieve.

SELECT CONTENT 
 FROM XMLTABLE ('/OBJECT/CommonFileUpload/CommonFileUploadFileList/CommonFileUploadFile'
               PASSING (SELECT XMLCOL FROM BIZZXE_V2_SCH.B)
               COLUMNS CONTENT   BLOB  PATH 'Contents') T

This is my example target table

CREATE TABLE "TEST"  

( `"CONTENT" BLOB`
   )

APC · Accepted Answer · 2015-06-02 04:17:33Z

1

Oracle throws error ORA-01465: invalid hex number when we attempt to put a string into a BLOB field. We need to convert it to the binary format first. we can do this using the UTL_RAW.CAST_TO_RAW() function. Find out more.

However, what you have here is a string representation of a binary encoding. There's no point in storing that as a BLOB. What you should do is store it as a CLOB; when the front-end queries Contents return that string as is, and let them decode it.

For the record, here is the code I used to extract the Contents element from an XMLType column and store it as a BLOB:

declare
    vc varchar2(32767);
    lb blob;
begin

    select t.content
    into vc
    from b,
         xmltable ('/OBJECT/CommonFileUpload/CommonFileUploadFileList/CommonFileUploadFile'
                   passing b.xmlcol
                   columns content   clob  path 'Contents') t;

   lb := utl_raw.cast_to_raw(vc);
   insert into tgt values (lb);
end;
/

And here is the outcome:

SQL> select * from tgt;

BCOL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
4D7A45764D4463764D6A41784E41304B4D533467526D6C755A43426849486468655342306279427A64473977494842766333526959574E7249476C75494652795A57556752334A705A43427062694244

SQL>

Not very useful.

edited Jun 2, 2015 at 4:17

answered Jun 1, 2015 at 7:06

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fiham Zuher Over a year ago

ORA-06553: PLS-306: wrong number or types of arguments in call to 'CAST_TO_RAW'

APC Over a year ago

Without seeing the actual code you wrote it is impossible for me to say why you get that error.

Collectives™ on Stack Overflow

XML value cannot convert to BLOB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related