Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    Unanswered: SELECT on a user type collection?

    I define an user type collection such as:

    CREATE OR REPLACE TYPE INTEGER_ARRAY
    AS TABLE OF INTEGER;

    I would like to define a procedure that checks which id declared in the collection are not already defined in a table (CREATE TABLE mytable (Id INTEGER)). I've tried to *cast* the collection to a table, but Oracle throws an exception:

    FUNCTION myfunction(P_ItemsId IN INTEGER_ARRAY)
    RETURN INTEGER_ARRAY AS
    V_NewItemsId INTEGER_ARRAY;
    BEGIN
    SELECT Id BULK COLLECT INTO V_NewItemsId
    FROM mytable
    WHERE Id NOT IN (
    SELECT *
    FROM TABLE(P_ItemsId));

    ORA-22905: cannot access rows from a non-nested table item

    How could I convert this collection to a nested table?

    Thanks!


    Daniel

  2. #2
    Join Date
    Dec 2003
    Posts
    1

    Re: SELECT on a user type collection?

    Our off-shore team gets your exception whereas we do not in a particular case using a stored function.

    In your case I think the workaround would resemble:
    FROM TABLE(cast(P_ItemsId as ??? )));
    where ??? is the type of P_ItemsId

    A complete example would be:

    TYPE CIDSET_T AS TABLE OF VARCHAR2(32);
    declare
    x cidset_t;
    y varchar2(32);
    begin
    x := cidset_t('1','2','3');
    -- throws exception
    select * into y from table(x) where rownum < 2;
    -- does not throw exception
    select * into y from table(cast(x as cidset_t)) where rownum < 2;
    dbms_output.put_line(y);
    end;



    Originally posted by dcaune
    I define an user type collection such as:

    CREATE OR REPLACE TYPE INTEGER_ARRAY
    AS TABLE OF INTEGER;

    I would like to define a procedure that checks which id declared in the collection are not already defined in a table (CREATE TABLE mytable (Id INTEGER)). I've tried to *cast* the collection to a table, but Oracle throws an exception:

    FUNCTION myfunction(P_ItemsId IN INTEGER_ARRAY)
    RETURN INTEGER_ARRAY AS
    V_NewItemsId INTEGER_ARRAY;
    BEGIN
    SELECT Id BULK COLLECT INTO V_NewItemsId
    FROM mytable
    WHERE Id NOT IN (
    SELECT *
    FROM TABLE(P_ItemsId));

    ORA-22905: cannot access rows from a non-nested table item

    How could I convert this collection to a nested table?

    Thanks!


    Daniel

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    use CAST to cast the item to a nested table type
    SATHISH .

  4. #4
    Join Date
    Jan 2004
    Posts
    1
    Originally posted by satish_ct
    Hi,

    use CAST to cast the item to a nested table type
    satish_ct:

    Can you provide an example of this type of cast? I have been fighting this exact problem now for two days.

    Thank you
    Bryan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •