Pro*C VARRAY return from stored procedure

From: Mark <devil__disguised_at_hotmail.com>
Date: 7 Mar 2003 10:14:38 -0800
Message-ID: <77dfd4c4.0303071014.253f7a04@posting.google.com>

How do I write some Pro*C code which calls the following stored procedure returing a VARRAY?? Alternatively, if any of the SQL below needs modification to do what I want, let me know.

Thanks (hopefully)
Mark

/* database creation */

CREATE OR REPLACE TYPE MYARRAY AS VARRAY(50) OF NUMBER(6,2);
/

CREATE TABLE MY_TABLE
(

  key                          NUMBER(9)   NOT NULL,,
  mydata                       MYARRAY

);

INSERT INTO MY_TABLE

            (KEY, 
             mydata
             )
     VALUES ( 1,
             MYARRAY ( 1.00, 1.25, 1.75. 2.00)
             );

/* Procedure */

CREATE OR REPLACE PACKAGE mypackage
AS

   PROCEDURE myproc(

      t_key                  IN     my_table.KEY%type,
      t_mydata               OUT    my_table.mydata%type
      );

END mypackage;
/

CREATE OR REPLACE PACKAGE BODY mypackage AS

   PROCEDURE myproc(

        t_key                  IN     my_table.KEY%type,
        t_mydata               OUT    my_table.mydata%type
        )

   IS
   BEGIN

      SELECT mydata 
      INTO   t_mydata
      FROM   my_table
      WHERE  key = t_key;

   END myproc;

END mypackage; Received on Fri Mar 07 2003 - 12:14:38 CST