Space allocated to PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Space allocated to PL/SQL

  1. #1
    Join Date
    Feb 2001
    Posts
    100
    Hi All,

    I run a PL/SQL block which invokes a script file (which contains 15 thousands of SQL statements). Th
    I got this error message
    ERROR at line 118:
    ORA-06550: line 672, column 224:
    PLS-00123: program too large

    I increased the Shared_pool_size to 80M but no way. There seems that it is a limitation of the PL/SQL compiler.
    I need to execute this large script and I need to recuperate the error message if one statement fails and do a rollback. The script is well executed when directly invoked in SQL*PLUS. But If I launch it from the PL/SQL block I got the error. Is there a way to overcome this obstacle. Is there a way to handle errors in SQL*PLUS.
    Please Help

    Thanks in advance


  2. #2
    Join Date
    Dec 2005
    Posts
    2
    Is there any solution for this?????

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    i'm guessing he found one after nearly 5 years

  4. #4
    Join Date
    Dec 2005
    Posts
    2
    Probably we all seraching for the same solution..for 5 years...I can sence you have no answers...

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you spent 5 years on looking a solution for this....?!

  6. #6
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    cant u use resumable space allocation?

    or reduce your blocks by less scripts per block? Will the function of your scripts allow this?
    Able was I ere I saw Elba

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    http://download-west.oracle.com/docs....htm#PLS-00123

    PLS-00123: program too large
    Cause: PL/SQL was designed primarily for robust transaction processing. One consequence of the special-purpose design is that the PL/SQL compiler imposes a limit on block size. The limit depends on the mix of statements in the PL/SQL block. Blocks that exceed the limit cause this error.
    Action: The best solution is to modularize the program by defining subprograms, which can be stored in an Oracle database. Another solution is to break the program into two sub-blocks. Have the first block INSERT any data the second block needs into a temporary database table. Then, have the second block SELECT the data from the table.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width