Re: Help on Stored procedures(Urgent)

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/10
Message-ID: <8hu8s0$9ba$1@nnrp1.deja.com>#1/1

In article <394279C4.B47BFD41_at_india.hp.com>,   "Ramesh S." <rameshs_at_india.hp.com> wrote:
> I want to know whether we can declare an array of varchar(64), as
 an
> out parameter in a stored procedure. if yes, how should i do it. what
 is
> the syntax for declaring such a stored procedure parameter and how
 will
> i load or get values from that array.
>
> The requirement is to return a list of strings, dates, numbers(out
> parameters) from a stored procedure, which can be retrieved as Array
> objects through JDBC from java.
>
> i am new to PL/SQL and stored procedures. please mail me if u have
> the answer.
>
> thanks in advance
> ramesh
>
>

You can do one of two things (and both require the use of objects). You cannot use PLSQL table types as JDBC cannot bind to this type -- we must use OBJECT Types.

You can read:
http://technet.oracle.com/doc/java.815/a64685/oraext7.htm#1040060

which is the chapter in the JDBC guide about working with arrays and has sections:

o Retrieving an Array and its Elements

o Passing an Array to a Prepared Statement

o Passing an Array to a Callable Statement

o Using a Type Map to Map Array Elements

Another way is to use a result set and "select * from plsql_function". It could look like this:

ops$tkyte_at_8i> create or replace type myTableType as table of varchar2 (64);
  2 /

Type created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace

  2 function demo_proc2( p_rows_to_make_up in number )   3 return myTableType
  4 as
  5 l_data myTableType := myTableType();   6 begin

  7      for i in 1 .. p_rows_to_make_up
  8      loop
  9          l_data.extend;
 10          l_data(i) := 'Made up row ' || i;
 11      end loop;
 12      return l_data;

 13 end;
 14 /

Function created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select *
  2 from the ( select cast( demo_proc2(5) as mytableType )

  3                 from dual );

COLUMN_VALUE

---

Made up row 1
Made up row 2
Made up row 3
Made up row 4
Made up row 5

So, your JDBC program would just run the query to get the data. If the function "demo_proc2" cannot be called from SQL for whatever reason (eg: it calls an impure function in another piece of code or it itself tries to modify the database via an insert or whatever), you'll just make a package like:

ops$tkyte_at_8i> create or replace package my_pkg   2 as
  3

  4          procedure Make_up_the_data( p_rows_to_make_up in number );
  5          function Get_The_Data return myTableType;

  6 end;
  7 /

Package created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package body my_pkg   2 as
  3
  4 g_data myTableType;
  5
  6 procedure Make_up_the_data( p_rows_to_make_up in number )   7 as
  8 begin

  9          g_data := myTableType();
 10      for i in 1 .. p_rows_to_make_up
 11      loop
 12          g_data.extend;
 13          g_data(i) := 'Made up row ' || i;
 14      end loop;

 15 end;
 16
 17
 18 function get_the_data return myTableType  19 is
 20 begin
 21 return g_data;
 22 end;
 23
 24 end;
 25 /

Package body created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> exec my_pkg.make_up_the_data( 3 );

PL/SQL procedure successfully completed.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select *
  2 from the ( select cast( my_pkg.get_the_data as mytableType )

  3                 from dual );

COLUMN_VALUE

---

Made up row 1
Made up row 2
Made up row 3

And you'll call the procedure followed by a query to get the data...

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat Jun 10 2000 - 00:00:00 CDT