Oracle fetch XML from BLOB

Question

I have tried lots of methods and still can't get my full XML document from DB. What I want to achieve is displaying the XML in Oracle Apex (Display only element) but I can't manage to get the full XML out from my blob.

SELECT 
    utl_raw.cast_to_varchar2(dbms_lob.substr(<blob_column>, 2000, 1))
FROM 
    <my_table> 
WHERE <some_id> = 123

Also tried to fetch it with mimetype but had no luck. Thank you.

You're only retrieving 2000 bytes from your blob column. Why not just do something like select xmltype(<blob_column>) from <your_table> ...? — Boneist
– Boneist, Commented Jul 16, 2018 at 13:23

Ilia Maskov · Accepted Answer · 2018-07-16 13:30:47Z

2

First, you shouldn't convert it to varchar on the server side since Oracle SQL has a 4K limitation on a varchar string size. You can utilize a PL\SQL block for retrieving your data but in this case you will have a limitation in 32K. There is a special way how to get round this issue: http://mayo-tech-ans.blogspot.com/2013/06/displaying-large-clobs-in-oracle-apex.html .

answered Jul 16, 2018 at 13:30

Ilia Maskov

1,91816 silver badges27 bronze badges

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

1 Comment

Adi Over a year ago

Didn't worked for me because I had Blob not Clob and I converted from blob to clob. Thanks for the Hint . I posted my answer down.

Tajinder · Accepted Answer · 2018-07-16 14:17:15Z

1

Hoping, I understood the question correctly.

I think, below query will help you.

First, convert the blob column to XMLTYPE, this will also help to check, if XML is valid or not. http://www.dba-oracle.com/t_convert_blob_to_xml_type.htm

Then, use EXTRACTVALUE to fetch the data from the XML.

select EXTRACTVALUE(xml_data,'/note/to')
from
(
select XMLTYPE.createXML('<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Test body</body>
</note>') xml_data from dual
)
;

edited Jul 16, 2018 at 14:17

answered Jul 16, 2018 at 13:59

Tajinder

2,3484 gold badges38 silver badges58 bronze badges

3 Comments

Adi Over a year ago

Didn't knew the element names and this is why I haven't used it like this .

Tajinder Over a year ago

@AdrianNicolae Ok just posted the query in case you also want to extract data from the XML column.

Adi Over a year ago

Thank you @Tajinder , I will also use your answer for a further step .

Adi · Accepted Answer · 2018-07-17 10:43:29Z

In order to get full BLOB content:

Converted BLOB into CLOB

create or replace FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
     v_clob    CLOB;
     v_varchar VARCHAR2(32767);
     v_start      PLS_INTEGER := 1;
     v_buffer  PLS_INTEGER := 32767;
BEGIN
     DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

     FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
     LOOP

        v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));

           DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);

          v_start := v_start + v_buffer;
     END LOOP;

   RETURN v_clob;

END blob_to_clob;

In Oracle Apex created Display Only (element) called ex: P5_XML
PLSQL code

declare
    v_clob    clob;
begin
    SELECT blob_to_clob(<blob_column>) INTO v_clob FROM <table> WHERE <some_column> = <something>;
    :P5_XML := v_clob;
end;

Collectives™ on Stack Overflow

Oracle fetch XML from BLOB

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related