A simple newbie question with select

[Tomislav Petrovic]

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_r emote_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_r emote_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;

[Tomislav Petrovic]

Re: A simple newbie question with select

Or in other words:

How can I write this query inside stored procedure

SELECT local_id, remote_id FROM SYNCML_MAPS WHERE idn_id = a_db_idn AND
local_id IN (1, 3, 9, 17, 20001);

So that (1, 3, 9, 17, 20001) part can be specified as procedure input
parameter???

Tomy.

[Mark C. Stock]

Re: A simple newbie question with select


"Tomislav Petrovic" <t.petrovic@ine t.hrwrote in message
news:btk1n4$gna $1@garrison.glo balnet.hr...
| Or in other words:
|
| How can I write this query inside stored procedure
|
| SELECT local_id, remote_id FROM SYNCML_MAPS WHERE idn_id = a_db_idn AND
| local_id IN (1, 3, 9, 17, 20001);
|
| So that (1, 3, 9, 17, 20001) part can be specified as procedure input
| parameter???
|
| Tomy.
|
|

try constructing the statement and using EXECUTE IMMEDIATE (probably the
preferred method)

if the predicate on the LOCAL_ID column does not contribute to performance
(i.e., plenty of selectivity on the IDN_ID column), you could also try
something like

and instr( v_options, '[' || local_id || ']' ) 0

where V_OPTIONS is constructed in the proc to look something like
'[1][3][9][17][20001]'

-- mcs

[Tomislav Petrovic]

Re: A simple newbie question with select

try constructing the statement and using EXECUTE IMMEDIATE (probably

the preferred method)

Thank you. Did that, works like a charm.

if the predicate on the LOCAL_ID column does not contribute to
performance (i.e., plenty of selectivity on the IDN_ID column), you
could also try something like
>
and instr( v_options, '[' || local_id || ']' ) 0
>
where V_OPTIONS is constructed in the proc to look something like
'[1][3][9][17][20001]'

It does, has unique constraint coupled with IDN_ID etc....
So this is not an option....

One more question, since I'm Oracle newbie...
Does unique constraint work like an index (speeds up searches, etc...)
or do I have to have separate index????

Tomy.