Associative array as inbound parameter

From: <ds_lewis_at_hotmail.com>
Date: 19 Jul 2006 18:13:31 -0700
Message-ID: <1153358011.833440.240400@i42g2000cwa.googlegroups.com>

I really need some help. I am trying to write a procedure with one inbound parameter as associative array and return a reference cursor. I'm passing in a list of record ID's that I need to use to select the rows returned in the cursor.

I believe I have everything working except that the procedure won't compile when try to access the values in the associative array from within the procedure. What am I doing wrong? Isn't it possible to use the values from an associative in a select statement?

CREATE OR REPLACE PACKAGE Array_Package_Test AS

TYPE t_cursor

                IS REF CURSOR
      RETURN ENGINE_OIL%ROWTYPE;

TYPE number_array IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER; PROCEDURE array_procedure_test (

      enginetimes_parm   IN       number_array
	  ,out_cursor         OUT      t_cursor

);
END
;
/

CREATE OR REPLACE PACKAGE BODY Array_Package_Test AS

PROCEDURE array_procedure_test (

      enginetimes_parm   IN       number_array,
      out_cursor         OUT      t_cursor

   )
   IS
   BEGIN
      OPEN out_cursor FOR

         SELECT *
           FROM ENGINE_OIL
          WHERE engine_timeid IN (SELECT * FROM

TABLE(enginetimes_parm));

This doesn't work either - but why? --SELECT * FROM TABLE(enginetimes_parm);

END array_procedure_test;
END;
/

Here is the ddl for the engine_oil table.

CREATE TABLE ENGINE_OIL
(

  QUARTS          NUMBER(10,2)                  NOT NULL,
  ENGINENUMBER    NUMBER                        NOT NULL,
  AIRCRAFTNUMBER  NUMBER                        NOT NULL,
  ENGINEDATETIME  DATE                          NOT NULL,
  ENGINE_TIMEID   NUMBER                        NOT NULL

) Received on Wed Jul 19 2006 - 20:13:31 CDT