14 Replies Latest reply: Nov 22, 2012 6:35 AM by M. Neifer RSS

    Can I select element number from table collection expression?

    M. Neifer
      Hi, I know I can use COLUMN_VALUE to select column values from table collection expression. Is there any way to select the number of the element as well? (with Oracle 11.2.0.1.0)
      CREATE OR REPLACE TYPE plch_numbers_nt IS TABLE OF NUMBER;
      /
      
      DECLARE
         l_numbers   plch_numbers_nt
                        := plch_numbers_nt (10,
                                            20,
                                            30,
                                            40,
                                            50);
      BEGIN
         -- This would be nice...
         FOR rec IN (SELECT COLUMN_VALUE, VALUE_INDEX FROM TABLE (l_numbers))
         LOOP
            DBMS_OUTPUT.put_line (rec.COLUMN_VALUE, rec.VALUE_INDEX);
         END LOOP;
      END;
      /
        • 2. Re: Can I select element number from table collection expression?
          AlbertoFaenza
          Hi,

          if you cast your nested table as a table I guess what you can do is to get the ROWNUM.

          i.e.:
          SELECT COLUMN_VALUE, ROWNUM
            FROM TABLE (sys.odcinumberlist (10, 20, 30, 40, 50));
          
          COLUMN_VALUE     ROWNUM
          ------------ ----------
                    10          1
                    20          2
                    30          3
                    40          4
                    50          5
          If you want to get the index I would suggest to loop the nested table instead:
          DECLARE
             l_numbers   plch_numbers_nt
                            := plch_numbers_nt (10,
                                                20,
                                                30,
                                                40,
                                                50);
          BEGIN
             -- This would be nice...
             FOR i IN 1 .. l_numbers.COUNT
             LOOP
                DBMS_OUTPUT.put_line ('Value: '||l_numbers(i)||' index: '||i);
             END LOOP;
          END;
          / 
          
          Value: 10 index: 1
          Value: 20 index: 2
          Value: 30 index: 3
          Value: 40 index: 4
          Value: 50 index: 5
          Regards.
          Al

          Edited by: Alberto Faenza on Nov 21, 2012 5:17 PM
          Clarifications added
          • 3. Re: Can I select element number from table collection expression?
            BluShadow
            No need for SQL...
            SQL> ed
            Wrote file afiedt.buf
            
              1  DECLARE
              2     l_numbers   plch_numbers_nt
              3                    := plch_numbers_nt (10,
              4                                        20,
              5                                        30,
              6                                        40,
              7                                        50);
              8  BEGIN
              9     -- This would be nice...
             10     FOR rec IN 1..l_numbers.count
             11     LOOP
             12        DBMS_OUTPUT.put_line ('['||rec||'] :'||l_numbers(rec));
             13     END LOOP;
             14* END;
            SQL> /
            [1] :10
            [2] :20
            [3] :30
            [4] :40
            [5] :50
            
            PL/SQL procedure successfully completed.
            • 4. Re: Can I select element number from table collection expression?
              Dom Brooks
              No need for PLSQL ;)

              For a densely populated collection, this is just going to be rownum though.

              e.g.
              SQL> l
                1  SELECT rownum,t.column_value
                2  FROM   TABLE (CAST (sys.odcinumberlist(12,17,16,999,1)as sys.odcinumberlist)) t
                3* ORDER BY t.column_value
              SQL>
              SQL> /
              
                  ROWNUM COLUMN_VALUE
              ---------- ------------
                       5            1
                       1           12
                       3           16
                       2           17
                       4          999
              
              SQL>
              • 5. Re: Can I select element number from table collection expression?
                chris227
                What about another kind of loop
                DECLARE
                   l_numbers   plch_numbers_nt
                                  := plch_numbers_nt (10,
                                                      20,
                                                      30,
                                                      40,
                                                      50);
                BEGIN
                   -- This would be nice...
                --   FOR rec IN (SELECT COLUMN_VALUE, VALUE_INDEX FROM TABLE (l_numbers))
                   FOR idx in l_numbers.first..l_numbers.last
                   LOOP
                      DBMS_OUTPUT.put_line (l_numbers(idx)||': '|| idx);
                   END LOOP;
                END;
                / 
                
                10: 1
                20: 2
                30: 3
                40: 4
                50: 5
                Edited by: chris227 on 21.11.2012 08:23
                • 6. Re: Can I select element number from table collection expression?
                  BluShadow
                  Dom Brooks wrote:
                  No need for PLSQL ;)
                  LOL! opposite to myself.

                  The answer is, it depends. If the collection is getting it's data through some other PL/SQL code that is generating it, then no need to go to SQL to process the contents of that collection. However, if the contents of the collection are coming from data on database tables, then best to avoid collections altogether and just use SQL against the data.
                  • 7. Re: Can I select element number from table collection expression?
                    Solomon Yakobson
                    Alberto Faenza wrote:
                    if you cast your nested table as a table I guess what you can do is to get the ROWNUM.
                    Well, first of we need to define what element number is. I assume OP meant element index. Since nested table is sparse there is a difference:
                    SQL>  DECLARE
                      2     l_numbers   plch_numbers_nt
                      3                    := plch_numbers_nt (10,
                      4                                        20,
                      5                                        30,
                      6                                        40,
                      7                                        50);
                      8  BEGIN
                      9     l_numbers.delete(3);
                     10     FOR rec IN (SELECT COLUMN_VALUE,ROWNUM RN FROM TABLE (l_numbers))
                     11     LOOP
                     12        DBMS_OUTPUT.put_line ('Row' || rec.RN || ': ' || rec.COLUMN_VALUE);
                     13     END LOOP;
                     14  END;
                     15  / 
                    Row1: 10
                    Row2: 20
                    Row3: 40
                    Row4: 50
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> 
                    As you can see, 40 & 50 are row 3 & 4 but their index values are 4 & 5.
                    But even with assumption nested table is not sparse, there is, in general,no guarantee nested tables rows are returned in element order. It is all implementation dependent. In general, when we convert nested table to a table using TABLE operator we go from object to relational and, as you know it, there is no row order in sets unless ORDER BY was used. And that's the issue. Years back I filed enchancement request to add pseudo column for element index but... Anyway, I don't think Oracle will change implemetation but to be completely on the safe side we would need to create object and object table types:
                    CREATE OR REPLACE
                      TYPE plch_numbers_obj
                        AS OBJECT(
                                  ind NUMBER,
                                  val NUMBER
                                 )
                    / 
                    CREATE OR REPLACE
                      TYPE plch_numbers_nt
                        AS TABLE OF plch_numbers_obj
                    / 
                     DECLARE
                       l_numbers   plch_numbers_nt
                                      := plch_numbers_nt (plch_numbers_obj(1,10),
                                                          plch_numbers_obj(2,20),
                                                          plch_numbers_obj(3,30),
                                                          plch_numbers_obj(4,40),
                                                          plch_numbers_obj(5,50)
                                                         );
                    BEGIN
                       l_numbers.delete(3);
                       FOR rec IN (SELECT ind,val,ROWNUM RN FROM TABLE (l_numbers))
                       LOOP
                          DBMS_OUTPUT.put_line ('Row ' || rec.RN || ' element ' || rec.ind || ': ' || rec.val);
                       END LOOP;
                    END;
                    / 
                    Row 1 element 1: 10
                    Row 2 element 2: 20
                    Row 3 element 4: 40
                    Row 4 element 5: 50
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> 
                    SY.
                    • 8. Re: Can I select element number from table collection expression?
                      AlbertoFaenza
                      Hi Solomon,

                      thanks for the clarification.
                      I have assumed that the collection is not sparse but I got your point.
                      You are completely right.

                      Regards.
                      Al
                      • 9. Re: Can I select element number from table collection expression?
                        M. Neifer
                        How do you create these scrollable code blocks in your post? Can't find this in the FAQ.
                        • 10. Re: Can I select element number from table collection expression?
                          BluShadow
                          Markus Neifer wrote:
                          How do you create these scrollable code blocks in your post? Can't find this in the FAQ.
                          You mean how to we post formatted code? It's in the FAQ: {message:id=9360002}, where it explains how to use {noformat}
                          {noformat} tags.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                          • 11. Re: Can I select element number from table collection expression?
                            M. Neifer
                            BluShadow wrote:
                            You mean how to we post formatted code? It's in the FAQ: {message:id=9360002}, where it explains how to use {noformat}
                            {noformat} tags.
                            Yes, I have read this FAQ entry. But does this add scroll bars automatically if I post code that is very wide?
                            • 12. Re: Can I select element number from table collection expression?
                              BluShadow
                              Markus Neifer wrote:
                              BluShadow wrote:
                              You mean how to we post formatted code? It's in the FAQ: {message:id=9360002}, where it explains how to use {noformat}
                              {noformat} tags.
                              Yes, I have read this FAQ entry. But does this add scroll bars automatically if I post code that is very wide?
                              Yes it does
                              • 13. Re: Can I select element number from table collection expression?
                                M. Neifer
                                BluShadow wrote:

                                Yes it does
                                Ok. Thanks. I was asking because the post preview has no scroll bars.
                                • 14. Re: Can I select element number from table collection expression?
                                  M. Neifer
                                  First of all, I would like to thank you all for your replies. I really appreciate your help.

                                  The problem I'm trying to solve is more complicated than what I have shown so far. Right now I'm trying to decompose the problem into smaller problems. Maybe I should show you the full problem and hope that I still get a reply.

                                  I have a spatial column with collections of geometries (SDO_GTYPE 2004) and I have a VARRAY column of objects. For each row, each geometry in the geometry collection has some information in one element in the VARRAY. So I have this table
                                  -- +----+------------------+-----------------------------------------------------------------------------------+
                                  -- | ID | GEOM_COLL        | GEO_INFO                                                                          |
                                  -- +----+------------------+-----------------------------------------------------------------------------------+
                                  -- | 98 | sdo_geom(2004)_1 | ((geo_info_1_1_1; geo_info_2_1_1; ...);(geo_info_1_1_2; geo_info_2_1_2; ...) ...) |
                                  -- +----+------------------+-----------------------------------------------------------------------------------+
                                  -- | 99 | sdo_geom(2004)_2 | ((geo_info_1_2_1; geo_info_2_2_1; ...);(geo_info_1_2_2; geo_info_2_2_2; ...) ...) |
                                  -- +----+------------------+-----------------------------------------------------------------------------------+
                                  --  ...
                                  and I need this
                                  -- +----+--------------------+----------------+----------------+-----+
                                  -- | ID | SDO_GEOM           | GEO_INFO_1     | GEO_INFO_2     | ... |
                                  -- +----+--------------------+----------------+----------------+-----+
                                  -- | 98 | sdo_geom(2001)_1_1 | geo_info_1_1_1 | geo_info_2_1_1 | ... |
                                  -- +----+--------------------+----------------+----------------+-----+
                                  -- | 98 | sdo_geom(2002)_1_2 | geo_info_1_1_2 | geo_info_2_1_2 | ... |
                                  -- +----+--------------------+----------------+----------------+-----+
                                  -- ...
                                  -- +----+--------------------+----------------+----------------+-----+
                                  -- | 99 | sdo_geom(2002)_2_1 | geo_info_1_2_1 | geo_info_2_2_1 | ... |
                                  -- +----+--------------------+----------------+----------------+-----+
                                  -- | 99 | sdo_geom(2001)_2_2 | geo_info_1_2_2 | geo_info_2_2_2 | ... |
                                  -- +----+--------------------+----------------+----------------+-----+
                                  -- ...
                                  I need a flat version of the original table, if you will. My original plan was to convert the VARRAY values to rows and extract the geometries into rows. Then join both using their ROWNUMs. But this does not work as i need numbers that start at 1 for each ID and I'm not sure if ROWNUM will give me the right order. Hence my question for consecutive element numbers. I have no idea how to do it with SQL. I'm working on a PL/SQL solution right now. Sample code follows. Sorry for the length of this post.
                                  CREATE OR REPLACE TYPE geo_info AS OBJECT (
                                    just INTEGER,
                                    txt  VARCHAR2 (255)
                                  );
                                  /
                                  CREATE OR REPLACE TYPE geo_info_array AS VARRAY (10000) OF geo_info;
                                  /
                                  CREATE TABLE geometries (
                                    id NUMBER (38) PRIMARY KEY,
                                    geom_coll SDO_GEOMETRY,
                                    geo_info geo_info_array
                                  );
                                  /
                                  INSERT INTO geometries (id, geom_coll, geo_info) VALUES (
                                    98,
                                    sdo_geometry (2004, NULL, sdo_point_type (0, 0, 0), 
                                                  sdo_elem_info_array (1, 1, 1, 3, 2, 1, 7, 1, 1, 9, 1, 1),
                                                  sdo_ordinate_array (5408562.081181, 5696027.239777, 5408563.398, 5696028.369, 5408568.192, 5696028.132, 5408562.643029, 5696038.558968, 5408565.328059, 5696027.0783)),
                                    geo_info_array (geo_info (7, '10.9'),
                                                    NULL,
                                                    geo_info (7, '0.0'),
                                                    geo_info (7, '-4.8-'))
                                  );
                                  /
                                  INSERT INTO geometries (id, geom_coll, geo_info) VALUES (
                                    99,
                                    sdo_geometry (2004, NULL, sdo_point_type (0, 0, 0),
                                                  sdo_elem_info_array (1, 2, 1, 5, 1, 1, 7, 2, 1, 11, 1, 1, 13, 1, 1),
                                                  sdo_ordinate_array (5408575.262, 5696017.841, 5408575.255, 5696017.341, 5408573.318578, 5696016.67559, 5408575.255, 5696017.341, 5408574.155, 5696017.357, 5408573.359578, 5696017.97359, 5408572.012251, 5696017.299678)),
                                    geo_info_array (NULL,
                                                    geo_info (7, '0.5'),
                                                    NULL,
                                                    geo_info (7, '0.0'),
                                                    geo_info (7, '-1.1-'))
                                  );
                                  /