Home » SQL & PL/SQL » SQL & PL/SQL » err-PLS-00123
icon9.gif  err-PLS-00123 [message #237216] Sun, 13 May 2007 08:54 Go to next message
gvsunil1
Messages: 7
Registered: April 2007
Location: hyderabad
Junior Member

hi all,
i am getting pls-00123-program too large error msg while executing my package in pl-sql developer(which consists of 4500 lines having functions and procedures). my oracle version 9.2.0.6.0. i think there is a setting r something ..can u please help me....


thanks in advance
sunil
Re: err-PLS-00123 [message #237217 is a reply to message #237216] Sun, 13 May 2007 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Split your program in several units.

Regards
Michel
Re: err-PLS-00123 [message #237219 is a reply to message #237217] Sun, 13 May 2007 10:04 Go to previous messageGo to next message
gvsunil1
Messages: 7
Registered: April 2007
Location: hyderabad
Junior Member

u mean i have to split my package into 2 or 3 packages or any thing else....can u tell me there is one setting in oracle..then v can write even 10,000 lines of package also...
Re: err-PLS-00123 [message #237221 is a reply to message #237219] Sun, 13 May 2007 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLS-00123: program too large (string)
  • 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.


No parameter exists.

Regards
Michel
Re: err-PLS-00123 [message #237222 is a reply to message #237221] Sun, 13 May 2007 10:22 Go to previous messageGo to next message
gvsunil1
Messages: 7
Registered: April 2007
Location: hyderabad
Junior Member

i have seen tat msg earlier...

http://www.mail-archive.com/oracle-l@fatcity.com/msg17066.html

can u look into this url once
Re: err-PLS-00123 [message #237227 is a reply to message #237222] Sun, 13 May 2007 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then?

Regards
Michel
Re: err-PLS-00123 [message #237873 is a reply to message #237222] Wed, 16 May 2007 01:10 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Nice article, like it Wink

Better than the rather odd temporary table suggestion, certainly.

It suggests up to around 6,000,000 lines of code should be possible in packages from 9i, and you are saying this error occurs at 4,500 lines. Are your lines very very long?

So what sort of values are you seeing in USER_OBJECT_SIZE?
Re: err-PLS-00123 [message #237980 is a reply to message #237873] Wed, 16 May 2007 04:35 Go to previous message
gvsunil1
Messages: 7
Registered: April 2007
Location: hyderabad
Junior Member

NAME	                TYPE	        SOURCE_SIZE
APPROVAL_PKG	PACKAGE	               915
APPROVAL_PKG	PACKAGE BODY	      203683


PARSED_SIZE	CODE_SIZE	ERROR_SIZE
631	        365	           0
0	        546859	           0

these are the values in USER_OBJECT_SIZE..

[Mod-edit: added code-tags for readability]

[Updated on: Wed, 16 May 2007 06:14] by Moderator

Report message to a moderator

Previous Topic: remote from SQL server to Oracle problem
Next Topic: instr help
Goto Forum:
  


Current Time: Sat Dec 03 16:19:51 CST 2016

Total time taken to generate the page: 0.04591 seconds