Re: Pro C/Java returning PLSQL Tables

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 05 Nov 1999 08:09:45 -0500
Message-ID: <NNQiOEerbYnp2kXOyO8wL0IhLj94@4ax.com>

A copy of this was sent to kmkoehler_at_my-deja.com (if that email address didn't require changing) On Fri, 05 Nov 1999 04:18:22 GMT, you wrote:

>I'm wanting an example of a Pro C and Java program
>calling a stored package/function that returns a
>PLSQL table. The documentation I have says you
>can in Pro C but gives no example. Has anyone
>done this?
>

from the Oracle jdbc docs:

PL/SQL TABLE, BOOLEAN and RECORD Types
Oracle JDBC drivers do not support calling arguments or return values of the PL/SQL TABLE, BOOLEAN, or RECORD types.

For pro*c, it is very easy. it would look like this:

sample plsql code ------------------------- create or replace package types as type char_array is table of varchar2(10) index by binary_integer;

procedure x( y out char_array, n in out number ); end;
/

create or replace package body types
as

procedure x( y out char_array, n in out number ) is
begin

for i in 1 .. 10 loop

        y(i) := 'Hello ' || i;
    end loop;
    n := 10;
end;

end;
/

sample pro*c code ------------------------------ static int process( char * tname ) { EXEC SQL BEGIN DECLARE SECTION; varchar c[30][10]; int n; EXEC SQL END DECLARE SECTION; int i;

n = 30;
for( i = 0; i < n; i++ ) c[i].len = 10;

exec sql execute
begin

        types.x( :c, :n );
    end;
    end-exec;

for( i = 0; i < n; i++ )

printf( "%.*s\n", c[i].len, c[i].arr ); }

If you are using 8.0 and up, the easiest/most flexible way I believe to return large sets from plsql like this is to use a cursor. Instead of using PL/SQL tables -- use object table types like this:

create or replace type myTableType as table of number; /

create or replace function getMyTableType( p_nrows in number ) return myTableType
as

l_x myTableType := myTableType();
begin

for i in 1 .. p_nrows loop

        l_x.extend;
        l_x(i) := i;

end loop;
return l_x;
end;
/

REM here we go... selecting from it:

select a.column_value val
from THE ( select cast( getMyTableType( 55 ) as mytableType ) from dual ) a /

That shows how to "select * from PLSQL_FUNCTION". The plsql function need only return a SQL Table type (not a PLSQL table type -- a SQL table type, one defined at the sql layer outside of plsql). This plsql function can actually return a complex object type with >1 attribute, for example:

tkyte_at_8.0> create or replace type myScalarType as object

  2  (       x int,
  3          y date,
  4          z varchar2(25)

5 )
6 /

Type created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace type myTableType as table of myScalarType; 2 /

Type created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace function getMyTableType return myTableType
  2  as
  3          l_x     myTableType := myTableType();
  4  begin
  5          for i in 1 .. 5 loop
  6                  l_x.extend;
  7                  l_x(i) := myScalarType( i, sysdate+i, 'This is row ' || i
);
  8          end loop;
  9          return l_x;

10 end;
11 /

Function created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> REM here we go... selecting from it:
tkyte_at_8.0> 
tkyte_at_8.0> select *

2 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a 3 /

X Y Z

---------- --------- -------------------------
         1 06-NOV-99 This is row 1
         2 07-NOV-99 This is row 2
         3 08-NOV-99 This is row 3
         4 09-NOV-99 This is row 4
         5 10-NOV-99 This is row 5

Since every language can deal with a Query -- this lets you get 'tables of records' back quite easily as result sets (works in JDBC as well).

>Thanks,
>
>Kathy
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Nov 05 1999 - 07:09:45 CST