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

Posted on 2011-05-12
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
    LVL 16

    Accepted Solution

    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

    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

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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now