A simple newbie question with select
Date: Thu, 8 Jan 2004 17:36:51 +0100
Message-ID: <btk0v0$gcq$1_at_garrison.globalnet.hr>
I have following table and a stored procedure in
which I want to select rows from the table
whose LOCAL_ID is one of the ids I give
it in a_local_ids table which is input parameter
and return them via cursor in/out parameter.
It should be called as:
local_ids := local_id_array(1, 2, 3, 4, 5);
call mappings_find_remote_ids(1, local_ids, remote_ids_ref)
How can I write that since statement below yields this error: ORA-00932: inconsistent datatypes: expected NUMBER got SYSTEM.LOCAL_ID_ARRAY. Hope you understood the question, excuse my poor english. Thank you in advance, Tomy.
CREATE TABLE SYNCML_MAPS (
IDN_ID NUMBER(11) DEFAULT 0 NOT NULL,
LOCAL_ID NUMBER(11) DEFAULT 0 NOT NULL,
REMOTE_ID VARCHAR2(32) NOT NULL,
UNIQUE(IDN_ID, LOCAL_ID),
UNIQUE(IDN_ID, REMOTE_ID),
FOREIGN KEY(IDN_ID) REFERENCES SYNCML_IDNS(ID) ON DELETE CASCADE
);
CREATE OR REPLACE TYPE local_id_array AS TABLE OF NUMBER(11);
PROCEDURE mappings_find_remote_ids (
a_db_idn IN SYNCML_MAPS.IDN_ID%Type, a_local_ids IN local_id_array, a_remote_ids IN OUT data_cursor
)
as
BEGIN
OPEN a_remote_ids FOR SELECT local_id, remote_id FROM SYNCML_MAPS WHERE idn_id = a_db_idn AND local_id IN a_local_ids;END IF;
END; Received on Thu Jan 08 2004 - 17:36:51 CET