Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: PL/SQL Collections

    I have questions regarding PL/SQL Collections:

    1. Is there an easy way to concatenate two like-typed collections together? I have to use a FOR LOOP which may not be efficient.

    2. Is there an anyway to put a collection into an IN clause? For instance, if I have a collection of varchar2 objects, and I want to execute a query like SELECT * from aTable where identifier IN <collection> Is there an efficient way to do this?

    sincerely,

    paul@paulrowe.com

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PL/SQL Collections

    Originally posted by boxhead609
    I have questions regarding PL/SQL Collections:

    1. Is there an easy way to concatenate two like-typed collections together? I have to use a FOR LOOP which may not be efficient.

    2. Is there an anyway to put a collection into an IN clause? For instance, if I have a collection of varchar2 objects, and I want to execute a query like SELECT * from aTable where identifier IN <collection> Is there an efficient way to do this?

    sincerely,

    paul@paulrowe.com
    No, you would have to use server-defined Types for this:

    Code:
    SQL> create or replace type num_tab as table of number;
      2  /
    
    Type created.
    
    SQL> var rc refcursor
    
    SQL> declare
      2    nt num_tab := num_tab();
      3  begin
      4    nt.extend(3);
      5    nt(1) := 100;
      6    nt(2) := 200;
      7    nt(3) := 300;
      8    open :rc for select * from table( cast( nt as num_tab ) );
      9  end;
     10  /
    
    PL/SQL procedure successfully completed.
    
    SQL> print rc
    
    COLUMN_VALUE
    ------------
             100
             200
             300
    You could then use UNION or UNION ALL to combine 2 collections into one.

Posting Permissions

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