Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Procedure varchar2 parameter size limit? ORA-6502 Numeric or value error

444787Oct 22 2008 — edited Oct 23 2008
Hi ALL,

I am trying to create out parameters in a Procedure. This procedure will be called by 4 other Procedures.

PROCEDURE create_serv_conf_attn_cc_email
( v_pdf_or_text varchar2,
v_trip_number number ,
v_display_attn_for_allmodes out varchar2,
v_display_cc_for_allmodes out varchar2,
v_multi_email_addresses out varchar2,
v_multi_copy_email_addresses out varchar2
)

When I call that procedure in another Procedure I am getting following error, which is caused by one of the out parameter being more than 255 characters.
I found that out via dbms_output.put_line(ing) one of the out parameter as I increased its size.

ORA-06502: PL/SQL: numeric or value error

I thought there was no size limit on any parameters passed to a Procedure.

Any one know of this limit of 255 characters on varchar2 Procedure parameters? Is there a work around keeping the same logic?
If not I will have to take those parameters out and resort to some global varchar2s which I do not like.

Thanks,

Suresh Bhat

Comments

Günter

If your stored procedures are huge maybe a Java heap space problem ?

Billy Verreynne

Recompiling a stored proc requires an exclusive lock on the meta data object for the stored proc. Sessions using and executing a stored proc, or objects with dependencies on that stored proc, place a shared lock on the stored proc meta data object. This is to prevent runtime issues and errors to occur by changing the code of an in-use stored proc.
Thus a recompile session call can wait for an extended time for obtaining an exclusive lock for changing the stored proc.

3 people found this helpful
jflack

So, if your database is busy, and your procedures are being used a lot, let me suggest that you use edition-based redefinition to make your changes.

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 20 2008
Added on Oct 22 2008
#error, #plsql, #value
5 comments
12,207 views