9 Replies Latest reply: Feb 21, 2013 8:45 AM by BluShadow RSS

    select from collection?

    946279
      here is simplified example:
      declare
           type r is record( rid rowid, empno emp.empno%type, job emp.job%type, deptno emp.deptno%type );
           type t is table of r;
           v t := t();
           x number := 0;
      begin
           select rowid, empno, job, deptno
           bulk collect into v
           from emp;
      
           /* ... more code ... */
      end;
      is it possible to query the collection v later in this code in a way like (sort of pseudo code here):
           /* ... */
           select count(distinct job)
           into x
           from [...here some code transforming collection v...]
           where v.deptno = 10;
      if so, how can it be done? oracle version is 11g.

      thank you
        • 1. Re: select from collection?
          Karthick_Arp
          NO, type T is a PL/SQL collection type. You cant use it in SQL. You need to create a SQL Collection type (CREATE OR REPLACE TYPE ..) to use in SQL.

          Personally i never found a reason to store the result of a SQL query in a COLLECTION and then process it. SQL is very powerful language, know it and use it better!!
          • 2. Re: select from collection?
            946279
            thanks for the answer. really no way to do this within the example above? no use for table operator, cast operator, pipelined function...?

            thank you

            Edited by: 943276 on 2013-02-21 13:02
            • 3. Re: select from collection?
              BluShadow
              As Karthick says, yes you can do it if the collection is defined as an SQL type, rather than a PL/SQL type, but again, why are you doing this?
              It makes no sense to query the data using SQL from the database into expensive PGA memory for PL, to then try and send that data back to the SQL engine to process in an SQL query. Just combine your queries and don't move data about unnecessarily.
              • 4. Re: select from collection?
                946279
                I'm asking for educational purposes. it maybe makes no sense to do this when dealing with real problem but it makes perfect sense to me to know that it is not possible.

                thank you
                • 5. Re: select from collection?
                  Karthick_Arp
                  943276 wrote:
                  I'm asking for educational purposes. it maybe makes no sense to do this when dealing with real problem but it makes perfect sense to me to know that it is not possible.

                  thank you
                  Again NOT POSSIBLE with PL/SQL collection type.
                  • 6. Re: select from collection?
                    odie_63
                    Karthick_Arp wrote:
                    Again NOT POSSIBLE with PL/SQL collection type.
                    It may be soon ;)

                    http://nuijten.blogspot.fr/2012/10/oow-2012-is-plsql-still-alive.html
                    In the current version of the Oracle database you needed a Nested Table or Varray Type defined at schema level in order to be able to use it in the TABLE operator. This is no longer necessary, it can be a PL/SQL declared type.
                    • 7. Re: select from collection?
                      Karthick_Arp
                      odie_63 wrote:
                      Karthick_Arp wrote:
                      Again NOT POSSIBLE with PL/SQL collection type.
                      It may be soon ;)

                      http://nuijten.blogspot.fr/2012/10/oow-2012-is-plsql-still-alive.html
                      In the current version of the Oracle database you needed a Nested Table or Varray Type defined at schema level in order to be able to use it in the TABLE operator. This is no longer necessary, it can be a PL/SQL declared type.
                      Thanks, Nice to know!!
                      • 8. Re: select from collection?
                        odie_63
                        Yes, it sounds cool.

                        Reverse of the medal, door's open for all kind of abuse with PL/SQL collections.
                        • 9. Re: select from collection?
                          BluShadow
                          odie_63 wrote:
                          Yes, it sounds cool.

                          Reverse of the medal, door's open for all kind of abuse with PL/SQL collections.
                          The worst thing will be all the "newbies" who join up to the forums, dragging up all the old threads that say you can't do it, just to say "Yes you can, look..." Grrr! ;)