Associative array as inbound parameter

sybrandb · *nix forums beginner Joined: 13 Jul 2006 Posts: 22

ds_lewis@hotmail.com · *nix forums beginner Joined: 01 Jun 2006 Posts: 2

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
)

Google