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

Home -> Community -> Usenet -> comp.databases.oracle -> A simple newbie question with select

A simple newbie question with select

From: Tomislav Petrovic <t.petrovic_at_inet.hr>
Date: Thu, 8 Jan 2004 17:36:51 +0100
Message-ID: <btk0v0$gcq$1@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 - 10:36:51 CST

Original text of this message

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