query on a user defined type
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: query on a user defined type

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    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 02:25 PM.

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    90
    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

  4. #4
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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;
    /

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    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.

  6. #6
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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;
    /

  7. #7
    Join Date
    Dec 2003
    Posts
    90
    Quote 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 11:27 AM.

  8. #8
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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
  •  



Click Here to Expand Forum to Full Width