Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Fareed.

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

Last updated: May 28, 2020 - 8:45 am UTC

Version: 12c

Viewed 1000+ 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 Chris 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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Fareed Akhlaq, May 28, 2020 - 12:42 am UTC

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
May 28, 2020 - 8:45 am UTC

Glad you got it resolved.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

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