Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Marcelo.

Asked: August 17, 2000 - 2:20 pm UTC

Answered by: Tom Kyte - Last updated: May 11, 2009 - 11:37 am UTC

Category: Database - Version: 8.1.6/8.0.5

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Could you tell me more about the program too large error (PLS-00123) ?

Normally, we modularize the procedures in order to avoid this error, but I would like to know more about the internal organization/storage of a procedure.

Thanks in advance,
Marcelo

and we said...

The following support note covers this topic well...

Article-ID: <Note:62603.1>
Folder: PLSQL
Topic: General Information Articles
Title: 'PLS-123 Program too Large' - Size Limitations on PLSQL
Packages
Document-Type: BULLETIN
Impact: MEDIUM
Skill-Level: NOVICE
Server-Version: 07 to 08
Updated-Date: 13-JUN-2000 17:41:01
References:


Overview
--------

This article contains information on PL/SQL package size limitations. When limits are reached, you receive the following error:

PLS-123 Program too large


Size Limitations on PL/SQL Packages
-----------------------------------

In releases prior to 8.1.3, large programs resulted in the PLS-123 error. This occurred because of genuine limits in the compiler; not as a result of a bug.

When compiling a PL/SQL unit, the compiler builds a parse tree. The maximum size of a PL/SQL unit is determined by the size of the parse tree. A maximum number of diana nodes exists in this tree.

Up to 7.3, you could have 2**14 (16K) diana nodes, and from 8.0 to 8.1.3, 2**15 (32K) diana nodes were allowed. With 8.1.3, this limit has been relaxed so that you can now have 2**26 (i.e., 64M) diana nodes in this tree for package and type bodies.


Source Code Limits
------------------

While there is no easy way to translate the limits in terms of lines of source code, it has been our observation that there have been approximately 5 to 10 nodes per line of source code. Prior to 8.1.3, the compiler could cleanly compile up to about 3,000 lines of code.

Starting with 8.1.3, the limit was relaxed for package bodies and type bodies which can now have approximately up to about 6,000,000 lines of code.

Notes: This new limit applies only to package bodies and type bodies. Also, you may now start hitting some other compiler limits before you hit this particular compiler limit.

In terms of source code size, assume that tokens (identifiers, operators, functions, etc.), are on average four characters long. Then, the maximum would be:

Up to 7.3: 4*(2**14)=64K
From 8.0 to 8.1.3: 4*(2**15)=128K
With 8.1.3: 4*(2**25)=256M

This is a rough estimate. If your code has many spaces, long identifiers, etc., you may end up with source code larger than this. You may also end up with source code smaller than this if your sources use very short identifiers, etc.

Note that this is per program unit, so package bodies are most likely to encounter this limit.


How to Check the Current Size of a Package
------------------------------------------

To check the size of a package, the closest related number you can use is PARSED_SIZE in the data dictionary view USER_OBJECT_SIZE. This value provides the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables and is NOT the size in the shared pool.

The size of the DIANA portion of PL/SQL code (used during compilation) is MUCH bigger in the shared pool than it is in the system table.

For example, you may begin experiencing problems with a 64K limit when the PARSED_SIZE in USER_OBJECT_SIZE is no more than 50K.

For a package, the parsed size or size of the DIANA makes sense only for the whole object, not separately for the specification and body.

If you select parsed_size for a package, you receive separate source and code sizes for the specification and body, but only a meaningful parsed size for the whole object which is output on the line for the package specification. A 0 is output for the parsed_size on the line for the package body.

The following example demonstrates this behaviour:

CREATE OR REPLACE PACKAGE example AS
PROCEDURE dummy1;
END example;
/
CREATE OR REPLACE PACKAGE BODY example AS
PROCEDURE dummy1 IS
BEGIN
NULL;
END;
END;
/

SQL> start t1.sql;

Package created.


Package body created.

SQL> select parsed_size from user_object_size where name='EXAMPLE';


PARSED_SIZE
-----------
185
0


SQL> select * from user_object_size where name='EXAMPLE';

.....

Oracle stores both DIANA and MCODE in the database. MCODE is the actual code that runs, while DIANA for a particular library unit X contains information that is needed to compile procedures using library unit X.

The following are several notes:

a) DIANA is represented in IDL. The linear version of IDL is stored on disk. The actual parse tree is built up and stored in the shared pool. This is why the size of DIANA in the shared pool is typically larger than on disk.

b) DIANA for called procedures is required in the shared pool only when you create procedures. In production systems, there is no need for DIANA in the shared pool (but only for the MCODE).

c) Starting with release 7.2, the DIANA for package bodies is thrown away, not used, and not stored in the database. This is why the PARSED_SIZE (i.e. size of DIANA) of PACKAGE BODIES is 0.

--> Therefore, large procedures and functions should always be defined within packages!



and you rated our response

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Limit on Parsedsize of procedure

January 23, 2002 - 9:04 pm UTC

Reviewer: Bhawna Rajesh from Taiwan

I have a PL/SQL stored procedure with the following details in USER_OBJECT_SIZE table:

NAME : SP_OFD_FIN
TYPE : PROCEDURE
SOURCE_SIZE : 71586
PARSED_SIZE : 129977
CODE_SIZE : 74013
ERROR_SIZE : 0

Server : Oracle 8.1.7 on 64-bit IBM AIX

This procedure gets uncompiled every now and then, without any changes in the dependencies. The status of the procedure is INVALID in user_objects view. And, when the front-end (Forms) tries to recompile this procedure at the run-time, it fails to do so, and an error "Deadlock Detected" is returned by the server. If we go to SQL*Plus prompt and recompile the procedure, the front end can be used properly.

Can the status of a procedure can change automatically, due to the big size of DIANA code? What is the upper limit on parsed_size ??

If the PARSED_SIZE is hitting the upper limit, then we can change the procedure to a package, where the DIANA code is not stored in database at all.

Kindly clarify my doubts.
Thanks.

Tom Kyte

Followup  

January 23, 2002 - 10:03 pm UTC

No, the size has nothing to do with it.

Something it depends on (see DBA_DEPENDENCIES) is changing.

A procedure that is 71k in size is a pretty LARGE procedure -- I'm a modular code fan, a subroutine should fit on a screen. I would guess your routine is over 2,000 lines long -- my big screen might get 100 lines/screen. I would suggest (not for the reason you are hitting an error with) you would consider recoding that in a modular fashion just "because".

A package is stored in DIANA in the database, just like a procedure. A package can be used to break the dependency chain however, perhaps making this go away. It is my belief that ALL production (real) code should be in a package, never in a standalone procedure or function.



Related question

January 24, 2002 - 9:25 am UTC

Reviewer: Salas from Mexico

Exists a limit for a [Program Unit] size in Forms?

I have a problem opening certain PU (but only in Windows 95, 98 or 2000. Windows NT open fine the same form and PU)

Thanks

Tom Kyte

Followup  

January 25, 2002 - 8:15 am UTC

I'm sure there is a limit, please contact support for this one.

why not recompiling on its own

January 24, 2002 - 8:12 pm UTC

Reviewer: Bhawna Rajesh from Taiwan

Hi Tom,

Going by your advice, we are wrapping up this procedure into a package. It's a customization of an existing application, so rewriting of procedures in modular form will call in for additional efforts.
But, still I am not clear about why the procedure SP_OFD_FIN could not get recompiled on its own, when the front end called it. Whereas, the rest of procedures having an uncompiled state (for whatsoever reasons) get compiled automatically, as they should. Can you throw some light on this ?

Tom Kyte

Followup  

January 25, 2002 - 8:33 am UTC

Well, getting a deadlock detected on this one is a strange one -- you would need to review the trace file on the server (we generate one with deadlocks) to see what is causing the "jam up".

speaking of wrapping...

January 25, 2002 - 12:21 pm UTC

Reviewer: A reader from ca, usa

Would wrapping the code make things better or worse? I have a 141k package which after wrapping it is 365k. I don't have a non 8i database to test it on.

Tom Kyte

Followup  

January 25, 2002 - 1:54 pm UTC

it makes no difference.

February 25, 2003 - 2:15 am UTC

Reviewer: Helena Marková from Bratislava, Slovakia


pls 123

July 04, 2003 - 10:21 am UTC

Reviewer: A reader

Tom, a very strange situation to me. The same code below ran ok in Oracle 8.0, but the following error ocurred in 8.1 -- the other way around would make sense to me though. I searched Metalink but I found no reasonable explanation for that.

That aside, any improvements you would suggest ?

Thanks for you help !

ps: I replaced my "sensitive" pieces of text with X's.


SET ECHO OFF;
SET VERIFY OFF;
SET SERVEROUTPUT ON;

spool XXXXXXXXXXXXXXXXXXXXXXX.log;

declare
i number(3) :=0;
begin

dbms_output.put_line('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
dbms_output.put_line('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');

begin
delete from XXXXXXXXXXXXXXXXX;

exception
when others then
rollback;
raise_application_error (-20000,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end;

dbms_output.put_line('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
dbms_output.put_line('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');

-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

begin
i := i + 1;
insert into XXXXXXXXXXXXXXXXX(XXXXXXXXXX , XXXXXXXXXX ,XXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXXXXXX ,XXXXXXXXXX ,XXXXXXXXXXXXXXXXXXXXX ,XXXXXXXXXXXXXXXXX ,XXXXXXXXXXXXXXXX ) values ('X',XXX ,X ,X ,XXX ,X ,XXXX ,XXXXXX ,X ,XXXXXX ,X ,XXXXX ,'X' ,X ,XXXXX ,X ,X ,XXXXXXXXXX ,X ,XXXXXXXXXX ,XXXXXX ,XXXXXXXXXX ,'X' ,XXXX ,XXXX ,''); i := i + 1;
-- (Tom, here go 299 more inserts similar to the one above)

exception
when others then
rollback;
raise_application_error (-XXXXX,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end;

IF i = 300 THEN
dbms_output.put_line('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
dbms_output.put_line('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
COMMIT;
ELSE
dbms_output.put_line('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
dbms_output.put_line('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
dbms_output.put_line('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
ROLLBACK;
END IF;

end;
/
SPOOL OFF;
SET ECHO ON;
SET VERIFY ON;
exit

declare
*
ERROR at line 1:
ORA-06550: line 0, column 0:
PLS-00123: program too large


Tom Kyte

Followup  

July 04, 2003 - 11:09 am UTC

is it april 1st?

you don't seriously expect me to create table xxxxxxxx ( xxxxxxx int, xxxxxxxxxxx int, .... ) and so on?

testcase = something small, concise, yet 100% self contained that anyone can run.


(and I cannot imagine where or what you must be doing that table and column names are sensitive. Please use an example we can all understand -- like I do for you)

Same as above

July 04, 2003 - 11:47 am UTC

Reviewer: A reader

Tom, I did not intend we have a test case here or something. Just by the looks of it -- can you tell me if something is CONSPICUOUSLY wrong -- like lines sizes or number of lines, etc ?

The fact is, this program is not mine. It looks a bit awkward to me -- all those inserts... I would have written something quite different. But I cannot touch the user's code without a good reason.

Can you give me any directions here ?

Tom Kyte

Followup  

July 04, 2003 - 12:02 pm UTC

the error arises from a parse tree growing too large.

the code will have to be rewritten to be a little more modular.

April 1st ??????????????????????????????????????????????????

July 04, 2003 - 12:32 pm UTC

Reviewer: A reader

Tom, it's not April 1st, rather July 4th !!! What are you doing working today ? I gotta admit -- you're a hard nut to crack, Tom.

Tom Kyte

Followup  

July 04, 2003 - 1:07 pm UTC

gotta book to finish and I use this as a continual distraction cause almost anything is better then technical edits...

:)

What about a PL/SQL block in an Execute Immediate in 8.1.7

February 19, 2004 - 11:08 am UTC

Reviewer: Phil Stanworth from England

My Package is a generic data copy engine and works by generating a dynamic pl/sql block to validate and copy the data. It builds the dynamic block in several VARCHAR2(32767) items, and runs by using EXECUTE IMMEDIATE on a concatenation of them. When I run it for a table having 376 columns, it fails with PLS-00123. However, if I capture the generated block to a file and compile it as a Procedure, it compiles OK and show a Parsed_Size of 249367 (Source_Size is 302472 & Code_Size is 256636). My question is: Why does it behave differently when it is a dynamic block from when it is a procedure? Are the limits known to be different in these two cases?

Tom Kyte

Followup  

February 19, 2004 - 11:37 am UTC

see support note <Note:62603.1> on metalink.

I would suggest you use dbms_sql to parse code over 32k in size. You can pass a plsql table type -- with each "array element" being "a line of code".

It'll be lots safer than concatenating big strings.

And now in Oracle 9.2.0.6.0?

July 28, 2005 - 10:44 am UTC

Reviewer: Phil Stanworth from England

Tom,

First of all, belated thanks for your previous answer from February last year. I remember that I took your advice and that it did the trick. However, now I'm at a different site and on a different release, and getting the same error in different circumstances.

I am getting pls-00123 when I compile a certain package in my client's dev environment, even though the same version of the package is running fine in the live and sys test environments (both on 9.2.0.6.0 too). The PARSED_SIZE is only 2047 after the failed compilation, and there are several bigger ones which compile OK. Any ideas?

Tom Kyte

Followup  

July 28, 2005 - 11:08 am UTC

can you diff the init.oras and report any and all differences between the two?

And now in Oracle 9.2.0.6.0?

July 29, 2005 - 6:10 am UTC

Reviewer: Phil Stanworth from England

Tom

Unfortunately there are no differences, provided I've found the right init.ora in each environment.

I thought you might have a ready answer. It seems not, so I've raised the problem with my client's DBAs to sort out while I go on holiday to Spain and Portugal.

Thanks anyway.
Phil

PLS -00123 after upgrade

August 03, 2005 - 10:21 am UTC

Reviewer: Mike from USA

Tom:
All of your answer was good and very detailed. This past Sunday, I upgraded our DEV database from 9.2.0.1 to 9.2.0.6, user got the error when he was using TOAD debuger tool. I found out many answers from metalink, but no one talked about this error in 9.2.0.6 version. As the last guy posted, do you have the answer for it? The debug worked fine for 9.2.0.1 version. I will open a TAR to Oracle to see if they have the answer.
Thanks for all of your answers and I love to read your detailed solutions.

dbms_sql using table of records

August 11, 2005 - 5:28 am UTC

Reviewer: Pawan Negi from Bangalore, India

Hi Tom,

With reference to what you said more than a year back -
"I would suggest you use dbms_sql to parse code over 32k in size. You can pass a
plsql table type -- with each "array element" being "a line of code"."

would appreciate if you could provide a small working example of how to use a pl/sql table type, having lines of codes as array elements. And also, how to parse and execute them using dbma_sql package.

thanks

Tom Kyte

Followup  

August 11, 2005 - 10:02 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:41742083561301#41779834596507 <code>

you can use varchar2a or varchar2s as the array, the former is 32k line width, the latter is 256 byte line width.

pls-123 on import?

February 14, 2006 - 7:36 pm UTC

Reviewer: Rich from Manhattan Beach, CA

Tom,

While doing an import, my log is being filled with errors of the type:

. . importing table "PRDSTSEE" 4 rows imported
IMP-00017: following statement failed with ORACLE error 6540:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '46'; SREC.MAXVAL :="
" '54'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR"
"RAY(364112362448313000000000000000000000,4368045184678010000000000000000000"
"00); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET"
"_COLUMN_STATS(NULL,'"PRDSTSEE"','"ARC_IND"', NULL ,NULL,NULL,2,.5,0,srec,2,"
"2); END;"
IMP-00003: ORACLE error 6540 encountered
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-123: program too large

. . importing table "PRDSTSML" 0 rows imported
IMP-00017: following statement failed with ORACLE error 6540:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="
" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR"
"RAY(0,0); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STAT"
"S.SET_COLUMN_STATS(NULL,'"PRDSTSML"','"PRD_STATUS"', NULL ,NULL,NULL,0,0,0,"
"srec,0,2); END;"

I would think that DBMS_STATS.STATREC would not violate any size constraints, so possibly something else is going on here? The error does not occur until more than halfway through the import at table 'PRDSTSEE', and continues on seemingly eery table after that.

Table Create scripts:
CREATE TABLE PRDSTSEE
(
PRD_STATUS NUMBER(2),
PRD_STATUS_CODE CHAR(3 BYTE),
PRD_STATUS_DESC VARCHAR2(30 BYTE),
PRD_STATUS_DEFAULT CHAR(1 BYTE),
PO_IND CHAR(1 BYTE),
DWNLD_IND CHAR(1 BYTE),
RPL_IND CHAR(1 BYTE),
ORDER_BOOK_IND CHAR(1 BYTE),
ARC_IND CHAR(1 BYTE)
)

CREATE TABLE PRDSTSML
(
PRD_STATUS NUMBER(2),
PRD_STATUS_LNG CHAR(3 BYTE),
PRD_STATUS_CODE CHAR(3 BYTE),
PRD_STATUS_DESC VARCHAR2(30 BYTE)
)

import.par:
File=../pmmprod1.dmp, ../pmmprod2.dmp
Filesize=1000MB
Log=pmmprod.imp.log
Buffer=1000000
Ignore=Y
Statistics=None
Feedback=1000
FromUser=('PMM', 'PPR')
ToUser=('PMM', 'PPR')

export.par:
Owner=('PMM', 'PPR')
Buffer=1000000
Direct=Y
Compress=N
Consistent=Y
File=/u03/oradata/pmmprod/backup/export/daily/by_schema/20060213_10:29:17/pmmprod1.dmp,/u03/oradata/pmmprod/backup/export/daily/by_schema/20060213_10:29:17/pmmprod2.dmp
Filesize=1000MB
Log=/u03/oradata/pmmprod/backup/export/daily/by_schema/20060213_10:29:17/pmmprod.exp.log

The only other relevant data that I can add is that I recently changed from pfile to spfile.

Tom Kyte

Followup  

February 15, 2006 - 8:35 am UTC

see support Note 123355.1 on metalink.

Strange perfomance issue

December 05, 2006 - 6:43 am UTC

Reviewer: Bala

Hi Tom,
We are facing a strange problem. We have queries whose performance is good in sql plus and also in procedures. But If I write the query in package, the performance is pathetic. We couldn't find the reason this.
Please help us to sort this issue.

Tom Kyte

Followup  

December 05, 2006 - 10:02 pm UTC

hmm, given a procedure is to be found in a package - well, I cannot think of a reason either


maybe if you sql_trace them and compare them, it'll become more obvious?

PLS-0123 Program too large

December 16, 2006 - 9:16 am UTC

Reviewer: A reader

Hi Tom,

I searched carrefully this site for the PLS-0123 error before coming asking you about the problem I am faced to. I am asked exceptionally to update few tables in production environnement given an excel file of say 12.000 pk_ide. I have to update several tables using these pk_ide only via a oneshot sqlfile (no create package nor create table are accepted in propduction for this exceptionnal update). So I did something like this
Declare
acc is record (ide number);
tabacc is table of acc;
l_tabacc tabacc;
begin
l_tabacc(1).ide := pk_ide1; -- from the excel file
l_tabacc(2).ide := pk_ide2; -- from the excel file
l_tabacc(3).ide := pk_ide3; -- from the excel file
.../...
l_tabacc(12000).ide := pk_ide12000;
For i in l_tabacc.first..l_tabacc.last loop
--update my tables here
END LOOP;

END;

Unfortunately when executing this begin/end sql file I got the PLS-0123 Error.

I was thinking about any other solution I can use in order to avoid this PLS-0123 but I din't find anything

Is it possible to overcome this error without creating package nor table in production environnement.

Thanks a lot for your answer




Tom Kyte

Followup  

December 17, 2006 - 9:59 am UTC

why would you do that?

why not load the spreadsheet data into a table and then just do a single update?

no code.

December 17, 2006 - 1:02 pm UTC

Reviewer: A reader

Because they don't need any new table in production for thes purposes. This is an exceptionnal update.

There is no need to do such an update in futur.

Thanks a lot indeed.

Tom Kyte

Followup  

December 17, 2006 - 6:38 pm UTC

but that is the answer to your question. don't write code, that was the wrong thing to do regardless of the circumstances.

Limitation in 9.2.0.7

March 19, 2009 - 9:48 am UTC

Reviewer: Reader from USA

Hello Tom,
We have Java programs calling a PL/SQL package in a 9.2.0.7 database. The package contains 958 procedures/functions in it. Yesterday we added more procedures to the package spec and we got PLS-00123 error.

The obvious solution is to break the package into multiple packages. The Java programs use this package as a database API. They don't have a real need to break this package into multiple packages. We have a lot of global variables, lot of dependencies of procedures/functions that we are able to contain by using only one package.

Why does pl/sql compiler have such a low limit? I can't add more than 958 procedures/functions in a package?

Do you have a better solution than splitting the package?

One thing we are doing is to change TABLE_NAME.COLUMN_NAME%TYPE into NUMBER or VARCHAR2. Is that advisable?

Thanks.


Tom Kyte

Followup  

March 19, 2009 - 11:04 am UTC

... The package contains 958 procedures/functions in it ...

osh kosh by gosh.


... They don't have a real need to
break this package into multiple packages. ...

ummm, sure they do. It should be OBVIOUS that they do.


... We have a lot of global variables,
lot of dependencies of procedures/functions that we are able to contain by
using only one package.
....

ouch, now I'm really scared of your code.

... Do you have a better solution than splitting the package? ...

I cannot begin to say how backwards that question is. How utterly backwards.


... One thing we are doing is to change TABLE_NAME.COLUMN_NAME%TYPE into NUMBER or
VARCHAR2. Is that advisable?
...

I don't know what that means. You would not be touching the package in order to do that, you would be altering a table.

Can you give more explanation?

March 19, 2009 - 4:23 pm UTC

Reviewer: Reader from USA

I understand that standard design practices tell you to write modular code. In other words, create packages by functionality in the system. But the application logic is written in Java. Package is used to do heavy duty query processing and then returning results. If the Java programs are using this package as a standard API, then breaking one package into multiple packages creates more things to coordinate and more dependencies to take care of on the database side. And also the developers need to know which package to use for what. Right now they know one package and they just use whatever procedure/function they need and don't have to worry about the dependencies.

What I meant by changing TABLE_NAME.COLUMN_NAME%TYPE is that we are changing parameter definitions (IN, IN OUT and OUT parameters to procedures/functions) from defining them as %TYPE to NUMBER(5) or VARCHAR2(20) etc.

So what we could do?
Tom Kyte

Followup  

March 23, 2009 - 10:25 am UTC

no, there is application logic in plsql too. do NOT imply that plsql is "not a real language, we are not doing real things in plsql", it is code - code is code - all code deserves respect.


You can refactor your exist really bad single package.


and you will NOT be changing the types as you describe. For the simple reason that *it will not work*.


ops$tkyte%ORA10GR2> create or replace procedure p( x number ) as begin null; end;
  2  /

Procedure created.

ops$tkyte%ORA10GR2> create or replace procedure p( x number(5) ) as begin null; end;
  2  /

Warning: Procedure created with compilation errors.

ops$tkyte%ORA10GR2> show errors
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/22     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.




ugh, this is really frustrating here - you are going to take the correct approach (use %type) and try to replace it with the incorrect approach (hard coding a type) and .... in a package that I would erase immediately and tell the "programmers" (air quotes on purpose, no programmer I know would do this) to to it right in the first place. Someone is going to have to maintain this mess.

Can you give more explanation?

March 23, 2009 - 1:22 pm UTC

Reviewer: Javier Castañón from Mexico City

@Reader

Do you realize that given dependencies among procedures/functions and global variables your team already have lots of things to coordinate and lots of dependencies to take care?

"And also the developers need to know which package to use for what."

Java developers are used to thousands and thousands of classes in the JDK, and thousands of classes in Hibernate and Spring Framework, and hundreds and hundreds of namespaces ("packages") an all of them. Look, a PL/SQL package is similar to a Java class, procedures/functions within a package are as methods within a class and a global variable within a PL/SQL package is like a member variable in a Java instance. In Java there is also a limit in the max size of the code within a method. Let me generalize your post to Java/PL/SQL:

The {package|Java class} contains 958 {procedures/functions|methods} in it. Yesterday we added more {procedures|methods} to the package spec and we got {PLS-00123|javac} error.

The obvious solution is to break the {package|class} into multiple {packages|classes}. The {Java|web tier} programs use this package as a {database|application} API. They don't have a real need to break this {package|class} into multiple {packages|classes}. We have a lot of {global|instance} variables, lot of dependencies of {procedures/functions|methods} that we are able to contain by using only one {package|class}.


Tom Kyte

Followup  

March 26, 2009 - 1:18 pm UTC

I still take exception with this part of it greatly:

... They don't have a real need to break this
{package|class} into multiple {packages|classes}. ...

of course they do, any professional software developer would see the need.

Here is what I am doing...

March 23, 2009 - 3:05 pm UTC

Reviewer: Reader from USA

Tom,

I am not implying pl/sql is not a real language. The point I am making is that there is a lot more work involved in breaking the package into multiple packages than doing this as follows:

Here is a simple example:

SQL> create table t ( n number(18) );

Table created.

SQL> create or replace procedure p( x in t.n%type ) as begin null; end;
  2  /

Procedure created.

SQL> SELECT NAME, TYPE, SOURCE_SIZE, PARSED_SIZE  FROM user_object_size  WHERE name = 'P';

NAME           TYPE    SOURCE_SIZE PARSED_SIZE
------------------------------ ------------------ ----------- -----------
P          PROCEDURE     48       349

SQL> create or replace procedure p( x number ) as begin null; end;
  2  /

Procedure created.

SQL> SELECT NAME, TYPE, SOURCE_SIZE, PARSED_SIZE  FROM user_object_size  WHERE name = 'P';

NAME           TYPE    SOURCE_SIZE PARSED_SIZE
------------------------------ ------------------ ----------- -----------
P          PROCEDURE     43       268

=================================================

As you can see, the PARSED_SIZE of the second procedure is smaller than the first one. So the second one creates less DIANA nodes than the first one.

I do understand that I am cutting corners here but this works.

Tom Kyte

Followup  

March 26, 2009 - 1:33 pm UTC

I give up, you have a mess, an unmitigated mess - spaghetti code is what we call it.


You are not cutting corners

You are cutting off your nose despite your face.


Do your programmers have any sort of coding standards? Why wouldn't they use them for plsql as well as java. If they do this same thing in java, well, then I don't really care what you do - you are doomed.

Program too large (Diana nodes)

May 07, 2009 - 8:08 am UTC

Reviewer: Mohamed

Dear Tom,

I read this page about the diana nodes and have understand that the Program too large (Diana nodes) error will not occur anymore in packages in oracle version 8.1.7.4 and over.

However I am using oracle XE and I have encoutered this error in my package.

Here it is the value of my package parsed size

1  select PARSED_SIZE
  2  from USER_OBJECT_SIZE
  3* where NAME = 'MY_PACKAGE'
SQL> /

PARSED_SIZE
-----------
          0
       3244

If I re-compile my package in non debug mode (via Toad) then the Diana node error disappear.

Could you please help me solving it

Thanks in advance

Mohamed

Tom Kyte

Followup  

May 11, 2009 - 11:37 am UTC

... that the Program too
large (Diana nodes) error will not occur anymore in packages in oracle version
8.1.7.4 and over.
...

that is not true at all. You can and will still get it. If you do - it likely means you have some MONSTER CODE that really needs to be broken down and modularized.

without an example - no one can help you.