| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: If EXISTS before insert clause
"JPike" <jpike_at_welcom.com> wrote in message
news:1107797036.060954.289540_at_o13g2000cwo.googlegroups.com...
> ok. After adding all the Execute Immediate's I just get this one error
> when I select to execute statement:
>
> The following error has occurred:
>
> ORA-06550: line 0, column 0:
> PLS-00123: program too large
>
> If I select the "Execute all of content window as script" button in
> Toad, I get a file riddled with errors.
>
> Any suggestions?
>
J,
If you haven't done so already, you really need to download and review the PL/SQL manual (from http://tahiti.oracle.com) so that you have good understanding of the language. Otherwise, you will continue to run into show stoppers based on incorrect usage.
Regarding your TOAD problems -- hard to help on that without know the contents of your window. If you're intending to create a production upgrade/installation script, I suggest you switch over to SQL*Plus for testing, as that is the environment where most of your clients will be executing the script. I would also suggest downloading and reviewing the SQL*Plus manuals so that you get an adequate understanding of that environment as well. While you're at it, download the Oracle Errors manual. In it you'll find useful information such as:
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.
Although I'm sure many would disagree with the 2nd suggestion given under 'Action'.
Another approach you might want to take is to take advantage of SQL*Plus's WHENEVER SQLERROR command -- which you can use to abort your installation/upgrade script if certain preconditions are not met.
++ mcs Received on Mon Feb 07 2005 - 12:15:20 CST
 |
 |