Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Associative array as inbound parameter
ds_lewis_at_hotmail.com wrote:
> 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
> )
IIRC the CAST operator is missing.
Tom Kyte has similar examples on http://asktom.oracle.com
Search for 'dynamic in list'
Hth
-- Sybrand Bakker Senior Oracle DBAReceived on Thu Jul 20 2006 - 03:18:47 CDT