-
query an associative array
I have an associative array defined as
TYPE OneDAssociativeArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
I have a Procedure that accepts a this type as an IN param and it contains IDs. I want to query those IDs from the array but i get the following:
PL/SQL: ORA-00942: table or view does not exist
Here is a sample procedure:
Procedure GetPersons (PersonIdList IN Types.OneDAssociativeArray, data_cursor OUT refcursor)
IS
BEGIN
OPEN data_cursor FOR
Select a.name
from persons a
where a.id IN (SELECT * FROM PersonIdList);
END GetPersons;
Last edited by luciffer; 01-25-2006 at 03:25 PM.
-
What you have declared is an index-by PL/SQL table. It is not the same as an Oracle table and cannot be used in queries the way you have. You could use a global temporary table to achieve what you want or write some dynamic SQL which generates the "in" list.
-
I edited the first post when i found the proper name,
associative array = index-by table
And you are correct you can't use sql against the index-by table, now i've found an example that shows if you use a nested table like this
TYPE OneDNumberTable IS TABLE OF NUMBER;
v_data OneDNumberTable := OneDNumberTable(1,2,3);
It shows that you are supposed to be able to use
SELECT * FROM TABLE(CAST(v_data AS OneDNumberTable));
But when i try it i get:
PL/SQL: ORA-00902: invalid datatype
-
CAST doesn't work on index-by tables/associative arrays. It has to be a database object type. Here is an example of the technique.
Code:
DROP TYPE TMP_TAB
/
DROP TYPE TMP_REC
/
create or replace TYPE TMP_REC IS OBJECT (DUMMY_ID INTEGER)
/
CREATE OR REPLACE TYPE TMP_TAB AS TABLE OF TMP_REC
/
DECLARE
lv_SQL VARCHAR2(30000);
v_id number(22);
TYPE REF_CURSOR IS REF CURSOR;
l_Cursor REF_CURSOR;
v_tmp_tab tmp_tab:= tmp_tab();
BEGIN
v_tmp_tab.delete;
v_tmp_tab.extend;
v_tmp_tab(1) := TMP_rec(1);
lv_sql := 'SELECT 1 from DUAL WHERE 1 IN (SELECT dummy_id FROM table(cast(:b_tmp_tab as tmp_tab)))';
OPEN l_Cursor FOR lv_sql using v_tmp_tab;
LOOP
FETCH l_Cursor into v_id;
EXIT WHEN l_Cursor%NOTFOUND;
dbms_output.pUT_line(v_id);
END LOOP;
CLOSE l_cursor;
END;
/
-
You have to create the collection type (the nested table) as a persistent type in the database using the CREATE TYPE syntax in order for SQL to recognize it as an SQL type.
-
Or you can do it without dynamic SQL:
Code:
DROP TYPE TMP_TAB
/
DROP TYPE TMP_REC
/
create or replace TYPE TMP_REC IS OBJECT (DUMMY_ID INTEGER)
/
CREATE OR REPLACE TYPE TMP_TAB AS TABLE OF TMP_REC
/
DECLARE
lv_SQL VARCHAR2(30000);
v_id number(22);
TYPE REF_CURSOR IS REF CURSOR;
l_Cursor REF_CURSOR;
v_tmp_tab tmp_tab:= tmp_tab();
cursor c_curs (p_tmp_tab tmp_tab) is
SELECT 1 AS X from DUAL WHERE 1 IN (SELECT dummy_id FROM table(cast(v_tmp_tab as tmp_tab)));
BEGIN
v_tmp_tab.delete;
v_tmp_tab.extend;
v_tmp_tab(1) := TMP_rec(1);
for rec_curs in c_curs(v_tmp_tab) loop
dbms_output.put_line(rec_curs.x);
end loop;
END;
/
-
Originally Posted by padders
You have to create the collection type (the nested table) as a persistent type in the database using the CREATE TYPE syntax in order for SQL to recognize it as an SQL type.
You are correct. It seems to be working now in Oracle but before when I was using an associative array i could pass a .NET array through an oracle parameter by setting the parameter CollectionType attribute to "PLSLQAssociativeArray".
Unfortunately the current version ofl ODP.NET doesn't seem to support nexted tables, so i'm stuck passing my .net array into an associative array, looping over it and adding elements to a typed table and then querying from that.
thanks for the help.
Last edited by luciffer; 01-26-2006 at 12:27 PM.
-
btw you only need the explicit CAST in exceptional situations ("SELECT * FROM" being one of them in 9.2). Normally you don't need it.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|