Re: Is there a limit to the number of parameters being passed to a stored procedure?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/25
Message-ID: <35192842.6635851@192.86.155.100>#1/1

A copy of this was sent to "Thor HW" <thor_at_Echidna.net> (if that email address didn't require changing) On Tue, 24 Mar 1998 20:33:35 -0800, you wrote:

>Yes there is. I believe that it is 32 or something fairly close in Oracle7.
>You can use arrays (PL/SQL tables) if you are passing in similar items.
>
>Thor HW
>Cathal Bolster wrote in message
><01bd5753$ac4be900$40757dc2_at_test064.msc.ie>...
>>Hi folks,
>>
>>anyone out there know if there's a limit to the number of parameters being
>>passed to a stored procedure?
>>
>>Thx in adv.
>>Cathal.
>

There is no documented limit that I am aware of and I've passed upto 255 in/out parameters in v7 of the database. If you use the following script, which you can run like:

SQL> @test 750

It will generate a procedure that takes 750 in/out parameters. It then sets up 750 bind variables in sqlplus and invokes the procedure. I ran it in 7.1, 7.2, 7.3, and 8.0 and all worked (so the limit, if one exists is >750)....

• begin of test.sql ------------------------- set serveroutput on size 1000000

set feedback off
set termout off
set trimspool on
set linesize 80
set verify off

spool tmp.sql
declare

    l_sep varchar2(1) default null;
begin

    dbms_output.put_line( 'create or replace procedure test_many_inputs (');     for i in 1 .. &1 loop

        dbms_output.put_line( l_sep || ' p_' || ltrim(to_char(i)) ||
                             ' in out varchar2' );
        l_sep := ',';

    end loop;

    dbms_output.put_line(') ' );
    dbms_output.put_line( 'as' );
    dbms_output.put_line( 'begin' );
    dbms_output.put_line( 'dbms_output.put_line( ''Hello'' );' );
    dbms_output.put_line( 'dbms_output.put( &1 );' );
    dbms_output.put_line( 'dbms_output.put_line( '' inputs succeeded'' );' );
    dbms_output.put_line( 'end;');
    dbms_output.put_line( '/' );

end;
/
spool off

set feedback on
set termout on
@tmp

set feedback off
set termout off
set trimspool on
set linesize 80
set verify off

spool tmp.sql
declare

    l_sep varchar2(1) default null;
begin

    for i in 1 .. &1 loop

        dbms_output.put_line( 'variable p_' || ltrim(to_char(i)) ||
                              ' varchar2(20) ' );

        dbms_output.put_line( 'exec :p_' ||ltrim(to_char(i)) ||' := '|| i );

    end loop;

    dbms_output.put_line( 'set termout on' );
    dbms_output.put_line( 'set feedback on' );
    dbms_output.put_line( 'begin' );
    dbms_output.put_line( 'test_many_inputs ( ' );
    for i in 1 .. &1 loop
        dbms_output.put_line( l_sep || ' :p_' || ltrim(to_char(i))  );
        l_sep := ',';

    end loop;

    dbms_output.put_line( ');' );
    dbms_output.put_line( 'end;' );
    dbms_output.put_line( '/' );

end;
/
spool off

@tmp

• EOF test.sql ------------------------------------

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Mar 25 1998 - 00:00:00 CST