Re: Pro C/Java returning PLSQL Tables

How about in the PRO-C program if I have the PLSQL Table defined as:

   type loan_data_tab_type is table of loans%type

        index by binary_integer;

   function get_loan_row( loan_in in loans.loan_loan_id%type ) return

            loan_data_tab_type;

How would I reference in the pro-C program?

Thanks,

Kathy
In article <NNQiOEerbYnp2kXOyO8wL0IhLj94_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> 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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 05 1999 - 11:17:17 CST