Forum Stats

  • 3,782,437 Users
  • 2,254,645 Discussions
  • 7,880,078 Comments

Discussions

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

444787
444787 Member Posts: 21
edited Oct 23, 2008 3:47AM in SQL & PL/SQL
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
Tagged:

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Oct 22, 2008 3:47PM
    I assume one of the variables you have declared is not large enough for it's assignment.

    Here's an example.
    ME_XE?create or replace procedure test_size(plarge in out varchar2 )
    2 is
    3 begin
    4 plarge := rpad('a', 32000, 'a');
    5 end;
    6 /

    SP2-0804: Procedure created with compilation warnings

    Elapsed: 00:00:00.03
    ME_XE?
    ME_XE?declare
    2 my_var varchar2(32767);
    3 begin
    4 test_size(my_var);
    5 dbms_output.put_line(length(my_var));
    6 end;
    7 /
    32000

    PL/SQL procedure successfully completed.

    --NOTE here how the declared variable is 500 characters, but the procedure will try to assign it over 32,000 characters...no dice
    Elapsed: 00:00:00.00
    ME_XE?
    ME_XE?declare
    2 my_var varchar2(500);
    3 begin
    4 test_size(my_var);
    5 dbms_output.put_line(length(my_var));
    6 end;
    7 /
    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at "TUBBY.TEST_SIZE", line 4
    ORA-06512: at line 4


    Elapsed: 00:00:00.04
    Edited by: Tubby on Oct 22, 2008 12:47 PM
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    Change you parameter with clob in place of varchar2 and see what happens.

    Regards.

    Satyaki De.
  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    edited Oct 22, 2008 4:10PM
    There is no limit of 255 characters in pl/sql varchar2 parameters. Here's an example with 32000 characters:
    SQL> create or replace
      2  procedure testparam (p1 in varchar2) as
      3  begin
      4     dbms_output.put_line('length of parameter is ' || length(p1));
      5  end;
      6  /
    
    Procedure created.
    
    SQL> declare
      2     v_test  varchar2(32000);
      3  begin
      4     v_test := rpad('x',32000,'x');
      5
      6     testparam (v_test);
      7  end;
      8  /
    length of parameter is 32000
    
    PL/SQL procedure successfully completed.
    I'm using 10.2.0.4. I don't remember what the limits were for older versions.
  • 444787
    444787 Member Posts: 21
    Thanks you guys.

    It was my fault, I did not have my variable declared large enough.

    Thanks again.
  • BluShadow
    BluShadow Member, Moderator Posts: 41,623 Red Diamond
    [email protected] wrote:
    Thanks you guys.

    It was my fault, I did not have my variable declared large enough.
    Yes, the limit on PL/SQL varchar2 parameters is 32K (32767 characters) which is the size limit of varchar2 itself.
    DBMS_OUTPUT.PUT_LINE has a limit of 255 characters in database versions prior to 10g (and in 10g XE version I believe), but from 10g onwards it would output up to 32K.

    Of course if those parameters are assigned to local variables with a smaller size then you get the error you experienced.

    ;)
This discussion has been closed.