Stored Proc - Array Param

From: Geoff <nospam_at_nospam.com>
Date: Wed, 31 May 2006 00:35:17 GMT
Message-ID: <9x5fg.52$Me2.18@newsread1.news.pas.earthlink.net>

Hello,

In the following stored proc:

SQL> set serveroutput on format wrapped

SQL> create or replace type numArray as table of number

Type created.

SQL> create or replace procedure p_array( p_data in out numArray )   as
  begin

          for i in 1 .. p_data.count
          loop
                  p_data(i) := p_data(i) * 2;
          end loop;

  end;
/

Procedure created.

SQL> declare

          l_data numArray := numArray(1,2,3);   begin

          for i in 1 .. l_data.count
          loop
                  dbms_output.put_line( l_data(i) );
          end loop;

          p_array( l_data );

          dbms_output.put_line( 'after....' );
          for i in 1 .. l_data.count
          loop
                  dbms_output.put_line( l_data(i) );
          end loop;

  end;
/

SQL> /
1
2
3
after....
2
4
6

PL/SQL procedure successfully completed.

. . . the p_data param in the stored proc is an array of numbers. I am trying to call this from OCI but what data type is 'array of number'?

I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production with the Partitioning, OLAP and Data Mining options, Release 2, on Win XP.

Thanks.

-g Received on Tue May 30 2006 - 19:35:17 CDT