How many parameters I can have in a stored procedure built in Oracle 8i deployed on AIX server

alcsoft
alcsoft used Ask the Experts™
on
Hi,

I am wondering if someone can tell me up to how many parameters I can have in an Oracle 8i stored procedure.

This database 8i installed on AIX / server.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
65536  (64K)



http://download.oracle.com/docs/cd/A87860_01/doc/index.htm


the OS platform is irrelevant

Author

Commented:
Can you please be more specific and show me where I can find the document which talks about this? I need to read more about that!
Most Valuable Expert 2011
Top Expert 2012

Commented:
65536 - I can't be more specific than that,  that's the number

that link takes you not just to the document, but to the exact section that lists all of the limits

Most Valuable Expert 2011
Top Expert 2012

Commented:

Author

Commented:
Thanks,
So I am little confuse!! in the document it said
"In the shared pool, a package spec, object type spec, stand-alone subprogram, or anonymous block is limited to 64K DIANA nodes. The nodes correspond to tokens such as identifiers, keywords, operators, and so on. The m-code is limited to 64K compiler-generated temporary variables."

I am not sure how this can be related to the number of parameter the SPROC can have? Can you please explain.
Most Valuable Expert 2011
Top Expert 2012

Commented:
keep reading, it's in the table


parameters passed to a function or procedure   64K
Most Valuable Expert 2011
Top Expert 2012

Commented:
or even easier..

simply create a procedure with 65536 parameters, try to compile it

then modify it to accept one more, try to compile it
Most Valuable Expert 2011
Top Expert 2012
Commented:
I guess more important that the actual number in the table is this line


"Ordinarily, however, the DIANA size limit is exceeded before any of those limits. "

For example, I just tried what I suggested above.  I created a procedure with 65536 parameters
and I got a PLS-123  Program too large error.

So, even though I was within the limit I couldn't compile.  The reason, as stated above is the underlying DIANA limits.

You don't have control over those.  If you create one entity (procedure,variable, operator, etc)  there might be one DIANA object (node) for it, there might be more than one.  Since I have 65536 parameters plus the procedure itself, plus the block block, plus maybe some other internal stuff I won't know about,  I've got too much.

If you are looking for an "exact" number that will apply to all procedures, there isn't one because you can't control the diana nodes.

bigproc.txt
Most Valuable Expert 2011
Top Expert 2012

Commented:
I was able to create a procedure with only 8188 parameters then it failed if I added one more because I had exhausted diana nodes

The procedure was the same as above except the parameters are only number p1 to p8188

That doesn't mean that's the absolute maximum, but it's probably fairly close since there isn't very much to change

Author

Commented:
Thanks allot.. that's help allot!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial