Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Associative array as inbound parameter

Re: Associative array as inbound parameter

From: sybrandb <sybrandb_at_gmail.com>
Date: 20 Jul 2006 01:18:47 -0700
Message-ID: <1153383527.307947.54390@i42g2000cwa.googlegroups.com>

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 DBA
Received on Thu Jul 20 2006 - 03:18:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US