[Last Call] Learn how to a build a cloud-first strategyRegister Now


Toad - Out of Memory error when printing results for a stored procedure

Posted on 2011-05-12
Medium Priority
Last Modified: 2013-12-12

I am trying to run a stored procedure in Toad and print the results in the output window to save as a text file. The procedure executes but gives me an "Out of Memory" error before finishing the print results portion. I tried spooling via SQL*Plus but the output doesn't look anything like it's supposed to.  Please see my syntax below...

--Original syntax... Executed in Toad but received a 'out of memory' error while printing results
Variable Results refcursor;
Execute db_name.p_GetHostMatchStage (:Results);
Print Results;

-- Executed in SQL*Plus using this syntax to spool the results but output is incorrect
Spool c:\output.txt
Variable Results refcursor;
Execute db_name.p_GetHostMatchStage (:Results);
Print Results;
Spool off

Question by:cassie5643
  • 2
LVL 16

Accepted Solution

Milleniumaire earned 2000 total points
ID: 35747661
There are some Toad settings that control caching of returned rows, which, if very wide, can cause out of memory errors.

Try reducing the cache parameter in Toad as follows:

View -> Toad Options

In the Options window, expand Oracle and click General.

Try reducing OCI Array Buffer size and Max VARRAY size as this will reduce the amount of memory used by Toad.  Mine are both set to 200 due to similar issues.

Author Comment

ID: 35748328
It progressed even farther thanks to that adjustment but still got the error. I went down as a far a 30 and a high as 500 just to be sure. I got a 'out of memory expanding memory stream' when I went down to 50 ad 30. Any additional suggestions?

Author Comment

ID: 35748533
I reduced the OCI Array Buffer size down to 10 and it worked.... thank you!

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month17 days, 18 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question