Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Fareed.

Asked: May 27, 2020 - 2:51 am UTC

Answered by: Chris Saxon - Last updated: May 28, 2020 - 8:45 am UTC

Category: Database Development - Version: 12c

Viewed 100+ times

You Asked

I am copying XML data from Operating System file into CLOB and then casting as XMLTYPE for extracting information into relational database.
This solution is working for years but I received a business requirement to process big XML files ranging from 80-500MB. If simply follow the process it complains about out of memory issue whilst casting CLOB as XMLType.

CREATE TABLE table_with_xml_column (filename VARCHAR2(64), xml_document XMLType);

BEGIN

INSERT INTO /*+append nologging*/table_with_xml_column (filename, xml_document)
  VALUES ('ProcessFile.xml',
          XMLType(bfilename('theDIR', 'ProcessFile.xml'),
          nls_charset_id('AL32UTF8')));
END;
Error report -
ORA-27163: out of memory
ORA-06512: at line 3
27163. 0000 -  "out of memory"
*Cause:    The program ran out of memory when allocating a temporary
           data structure.
*Action:   Increase the amount of memory on the system.


and we said...

Sounds like you need more memory ;)

That said there are a couple of bugs related to this error and XML processing. Check on MOS to see if these are affecting you.

Also split the process up to see if you can pinpoint exactly what's going on:

- Load the file into a temporary table with a CLOB
- Copy from here to the XMLType column

If you think you've got enough memory available to process the documents and are still hitting the error, contact support.

and you rated our response

  (1 rating)

Reviews

May 28, 2020 - 12:42 am UTC

Reviewer: Fareed Akhlaq from Australia

Thanks for the response. What I found out was XML parser in 12.1 has some issues, so I forced it to use older version as following to resolve

ALTER SESSION SET EVENTS '31156 trace name context forever, level 0x400'
Chris Saxon

Followup  

May 28, 2020 - 8:45 am UTC

Glad you got it resolved.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.