Re: Pro C/Java returning PLSQL Tables

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 17:17:17 GMT, you wrote:

>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?
>

You cannot -- you can only bind to PLSQL tables of scalars and it really is only useful for plsql tables of VARCHAR types (its not a good idea to bind to tables of numbers or dates as they will not be converted into C types -- you'll get back internal formats for these).

If I had to do the above, I would do the below (the myScalarType/myTableType example below). I would "select * FROM get_loan_row".

>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.

--
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 - 11:51:58 CST