4 Replies Latest reply: Oct 14, 2012 10:47 AM by 320438 RSS

    Oracle xml view - alternative for using rownum

    320438
      OpenVMS V8.3 on node WXDV11
      Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
      With the Partitioning and Data Mining options

      Hi

      I need some help with this view. Becasue of the ORDER CLAUSE I cannot use the rownum for 'Pos' tag. Hence I have put the select in an in-line query as below. This would work but I can't reference the columns returned by the call to the function TABLE(pkg_spc_xml.get_cbb_prices_out(ic.datetime_from)) cbb

      Is there XML function I can use to order the 'Pos' values 1, 2, 3 to n.

      Will provide more info if needed.

      Thanks

      James


      CREATE OR REPLACE VIEW v_ng_eg_rbid_xml of XMLtype
      XMLSCHEMA "reserve-bid-document.xsd" Element "ReserveBidDocument"
      with object id  (substr(extractvalue(object_value,'/ReserveBidDocument/DocumentIdentification'),1,128))
      as
      select XMLelement("ReserveBidDocument",XMLATTRIBUTES('3' as "DtdVersion",'0'  as "DtdRelease"),
                       XMLelement("DocumentIdentification",xmlattributes(to_char(ic.datetime_from,
                                                '"EW_NG_"yyyymmdd') as "v")) 
                      ,XMLelement("DocumentVersion",xmlattributes(ic.documentversion as "v")) 
                      ,XMLelement("DocumentType",xmlattributes('A37' as "v"))
                      ,XMLelement("SenderIdentification",xmlattributes('A01' as "codingScheme", '10X1001A1001A515' as "v")) 
                      ,XMLelement("SenderRole",xmlattributes('A04' as "v"))
                      ,XMLelement("ReceiverIdentification",xmlattributes('A01' as "codingScheme", '10X1001A1001A531' as "v")) 
                      ,XMLelement("ReceiverRole",xmlattributes('A04' as "v"))
                      ,XMLelement("CreationDateTime",xmlattributes(to_char(ic.creation_datetime,'yyyy-mm-dd"T"hh24:mi:ss"Z"') as "v"))
                      ,XMLelement("ReserveBidTimeInterval",
                               xmlattributes(to_char(bg_time.to_gmtdatetime(ic.datetime_from+1/4,null),
                                        'yyyy-mm-dd"T"hh24:mi"Z"')||'/'||
                               to_char(bg_time.to_gmtdatetime(ic.datetime_from+5/4,null),'yyyy-mm-dd"T"hh24:mi"Z"') as "v"))
                      ,XMLelement("Domain",xmlattributes('A01' as "codingScheme", '10YDOM-1010A025W' as "v" ))
                      ,XMLelement("SubjectParty",xmlattributes('A01' as "codingScheme", '10X1001A1001A531' as "v")) 
                      ,XMLelement("SubjectRole",xmlattributes('A04' as "v"))
                     ,(select XMLAGG(
                      XMLelement("ReserveBidTimeSeries",
                                               XMLelement("ReserveBidIdentification", 
                                                      xmlattributes(to_char(cbb.datetime_from,'"EW_NG_"yyyymmdd_hh24mi_')||
                                                              to_char(rownum) as "v"))
                                              ,XMLelement("AuctionIdentification", 
                                                      xmlattributes(to_char(cbb.datetime_from,
                                                              '"EW_PERIOD_"yyyymmdd_hh24mi')  as "v"))
                                              ,XMLelement("BusinessType", xmlattributes('A10' as "v"))
                                              ,XMLelement("InArea",
                                                    xmlattributes('10YDOM-1010A025W' as "v", 'A01' as "codingScheme"))
                                              ,XMLelement("OutArea",
                                                    xmlattributes('10YGB----------A' as "v", 'A01' as "codingScheme"))
                                              ,XMLelement("MeasureUnitQuantity", xmlattributes('MAW' as "v"))
                                              ,XMLelement("Currency", xmlattributes('EUR' as "v"))
                                              ,XMLelement("MeasureUnitPrice", xmlattributes('MAW' as "v"))
                                              ,XMLelement("Divisible", xmlattributes('A02' as "v"))
                                              ,XMLelement("BlockBid", xmlattributes('A02' as "v"))
                                              ,XMLelement("Direction", 
                                                    xmlattributes(DECODE(cbb.direction,'Up','A01','A02') as "v" ))
                                              ,XMLelement("MinimumActivationQuantity", 
                                                          xmlattributes(cbb.price_count * cbb.power as "v"))
                                              ,XMLelement("MeasureUnitEnergyPrice", xmlattributes('MWH' as "v"))
                                              ,XMLelement("Period",
                                                              XMLelement("TimeInterval",
                                                                 xmlattributes(to_char(cbb.datetime_from,'yyyy-mm-dd"T"hh24:mi"Z"')
                                                                              ||'/'||
                                                                    to_char(cbb.datetime_from +1/24,'yyyy-mm-dd"T"hh24:mi"Z"') as "v"))
                                                              ,XMLelement("Resolution",xmlattributes('PT60M' as "v")) 
                                                              ,(SELECT XMLagg(XMLelement("Interval",
                                                                        XMLelement("Pos",xmlattributes(rownum as "v")) 
                                                                        ,XMLelement("Qty",xmlattributes(ca.power as "v")) 
                                                                        ,XMLelement("EnergyPrice",xmlattributes(ca.price_euros as "v")) 
                                                                                        )  /*ele*/
                                                                             )  /*agg */
                                                                FROM (SELECT * FROM cbb_prices c1
                                                                       WHERE c1.datetime_from = cbb.datetime_from
                                                                         AND c1.direction     = cbb.direction
                                                                         AND c1.interconnector= 'EWIC'
                                                                         AND c1.so_id = 'NGC'
                                                                         AND c1.price_euros is NOT NULL    
                                                                       ORDER BY DECODE(c1.direction,'Up',c1.price_euros) ASC,
                                                                                DECODE(c1.direction,'Down',c1.price_euros) DESC) ca
                                                               ) /*   select */
                                                         ) /* xml ele - period   */
                                            )  /* ele reserve bid series */
                                          )  /* xmlagg */
                                              FROM TABLE(pkg_spc_xml.get_cbb_prices_out(ic.datetime_from)) cbb 
                                ) /* middle select */                                                                     
      ).extract('/*')
      as RBid            
      from ic_Submissions ic
      where ic.doctype = 'RBID'
        and ic.originator = 'NGC'
        and ic.destination = 'EirGrid'
        and ic.status  = 'REQUESTED'
      /
        • 1. Re: Oracle xml view - alternative for using rownum
          320438
          Apologies for not being clear.
          Question is for this portion of the view: In this case, XMLelement("Pos",xmlattributes(rownum as "v")) which relies on the correct ordering of rownum will work. However, in the in-line select, I cannot do the join to the package 'cbb' elements, example the join 'AND c1.direction = cbb.direction'

          This view with this part of the code fails to compile.
                                                                  ,(SELECT XMLagg(XMLelement("Interval",
                                                                            XMLelement("Pos",xmlattributes(rownum as "v")) 
                                                                            ,XMLelement("Qty",xmlattributes(ca.power as "v")) 
                                                                            ,XMLelement("EnergyPrice",xmlattributes(ca.price_euros as "v")) 
                                                                                            )  /*ele*/
                                                                                 )  /*agg */
                                                                    FROM (SELECT * FROM cbb_prices c1
                                                                           WHERE c1.datetime_from = cbb.datetime_from
                                                                             AND c1.direction     = cbb.direction
                                                                             AND c1.interconnector= 'EWIC'
                                                                             AND c1.so_id = 'NGC'
                                                                             AND c1.price_euros is NOT NULL    
                                                                           ORDER BY DECODE(c1.direction,'Up',c1.price_euros) ASC,
                                                                                    DECODE(c1.direction,'Down',c1.price_euros) DESC) ca
                                                                   ) /*   select */
                                                             ) /* xml ele - period   */
                                                )  /* ele reserve bid series */
                                              )  /* xmlagg */
                                                  FROM TABLE(pkg_spc_xml.get_cbb_prices_out(ic.datetime_from)) cbb 
                                    ) /* middle select */ 
          However if I re-write without the in-line as follows, the view complies. But the rownum does not appear in the order 1,2,3... n.
                                                                  ,(SELECT XMLagg(XMLelement("Interval",
                                                                            XMLelement("Pos",xmlattributes(rownum as "v")) 
                                                                            ,XMLelement("Qty",xmlattributes(ca.power as "v")) 
                                                                            ,XMLelement("EnergyPrice",xmlattributes(ca.price_euros as "v")) 
                                                                                            )  /*ele*/
                                                                                            ORDER BY DECODE(ca.direction,'Up',ca.price_euros) ASC,
                                                                                                      DECODE(ca.direction,'Down',ca.price_euros) DESC
                                                                                 )  /*agg */
                                                                    FROM  cbb_prices ca
                                                                           WHERE ca.datetime_from = cbb.datetime_from
                                                                             AND ca.direction     = cbb.direction
                                                                             AND ca.interconnector= 'EWIC'
                                                                             AND ca.so_id = 'NGC'
                                                                             AND ca.price_euros is NOT NULL    
                                                                 ) /*   select */
                                                             ) /* xml ele - period   */
                                                )  /* ele reserve bid series */
                                              )  /* xmlagg */
                                                  FROM TABLE(pkg_spc_xml.get_cbb_prices_out(ic.datetime_from)) cbb 
                                    ) /* middle select */ 
          want to use later code segment. As the rownum is not appearing in the sequential order, is there an alternative to using rownum?
          • 2. Re: Oracle xml view - alternative for using rownum
            320438
            removed duplicate

            Edited by: jscomputing1 on Oct 12, 2012 1:32 PM
            • 3. Re: Oracle xml view - alternative for using rownum
              odie_63
              It's kind of a catch-22 situation.

              We need a subquery to first order the result set, but then the correlation is one level too deep.
              On the other hand, using the XMLAgg ORDER-BY clause and ROWNUM at the same level doesn't give the expected result because ORDER BY is always applied last, hence the necessity of ordering first.

              Here's a similar test case :
              SQL> select xmlserialize(document
                2           xmlelement("deptlist",
                3             xmlagg(
                4               xmlelement("dept",
                5                 xmlattributes(d.deptno as "id")
                6               , (
                7                   select xmlagg(
                8                            xmlelement("emp",
                9                              xmlattributes(v.empno as "id", v.ename as "name", rownum as "pos")
               10                            )
               11                          )
               12                   from (
               13                     select e.deptno, e.empno, e.ename
               14                     from scott.emp e
               15                     where e.deptno = d.deptno
               16                     order by e.ename desc
               17                   ) v
               18                 )
               19               )
               20             )
               21           )
               22           indent
               23         )
               24  from scott.dept d
               25  where d.deptno in (10,20)
               26  ;
                                 where e.deptno = d.deptno
                                                  *
              ERROR at line 15:
              ORA-00904: "D"."DEPTNO": invalid identifier
              ROW_NUMBER() fails too :
              SQL> select xmlserialize(document
                2           xmlelement("deptlist",
                3             xmlagg(
                4               xmlelement("dept",
                5                 xmlattributes(d.deptno as "id")
                6               , (
                7                   select xmlagg(
                8                            xmlelement("emp",
                9                              xmlattributes( e.empno as "id"
               10                                           , e.ename as "name"
               11                                           , row_number() over(order by e.ename desc) as "pos" )
               12                            )
               13                          )
               14                   from scott.emp e
               15                   where e.deptno = d.deptno
               16                 )
               17               )
               18             )
               19           )
               20           indent
               21         )
               22  from scott.dept d
               23  where d.deptno in (10,20)
               24  ;
                               select xmlagg(
                                      *
              ERROR at line 7:
              ORA-30483: window  functions are not allowed here
              The solution is to use an ordered subquery without correlation predicate (to avoid the first error) and rely on an optimizer feature : the Filter Push-Down transformation (FPD) :
              SQL> select xmlserialize(document
                2           xmlelement("deptlist",
                3             xmlagg(
                4               xmlelement("dept",
                5                 xmlattributes(d.deptno as "id")
                6               , (
                7                   select xmlagg(
                8                            xmlelement("emp",
                9                              xmlattributes(v.empno as "id", v.ename as "name", rownum as "pos")
               10                            )
               11                          )
               12                   from (
               13                     select deptno, empno, ename
               14                     from scott.emp
               15                     order by ename desc
               16                   ) v
               17                   where v.deptno = d.deptno
               18                 )
               19               )
               20             )
               21           )
               22           indent
               23         ) as result
               24  from scott.dept d
               25  where d.deptno in (10,20)
               26  ;
              
              RESULT
              --------------------------------------------------------------------------------
              <deptlist>
                <dept id="10">
                  <emp id="7934" name="MILLER" pos="1"/>
                  <emp id="7839" name="KING" pos="2"/>
                  <emp id="7782" name="CLARK" pos="3"/>
                </dept>
                <dept id="20">
                  <emp id="7369" name="SMITH" pos="1"/>
                  <emp id="7566" name="JONES" pos="2"/>
                  <emp id="7902" name="FORD" pos="3"/>
                </dept>
              </deptlist>
              
              
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 4288323250
              
              ---------------------------------------------------------------------------------
              | Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
              ---------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT      |         |     1 |     3 |     1   (0)| 00:00:01 |
              |   1 |  SORT AGGREGATE       |         |     1 |    20 |            |          |
              |   2 |   COUNT               |         |       |       |            |          |
              |   3 |    VIEW               |         |     4 |    80 |     4  (25)| 00:00:01 |
              |   4 |     SORT ORDER BY     |         |     4 |    52 |     4  (25)| 00:00:01 |
              |*  5 |      TABLE ACCESS FULL| EMP     |     4 |    52 |     3   (0)| 00:00:01 |
              |   6 |  SORT AGGREGATE       |         |     1 |     3 |            |          |
              |   7 |   INLIST ITERATOR     |         |       |       |            |          |
              |*  8 |    INDEX UNIQUE SCAN  | PK_DEPT |     2 |     6 |     1   (0)| 00:00:01 |
              ---------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 5 - filter("DEPTNO"=:B1)
                 8 - access("D"."DEPTNO"=10 OR "D"."DEPTNO"=20)
              The predicate info #5 confirms that the correlation predicate has effectively been moved and directly applied on the child table.
              That is also confirmed with a 10053 CBO trace :
              FPD:   Following are pushed to where clause of query block SEL$3 (#0)
              "EMP"."DEPTNO"=:B1
              FPD: Considering simple filter push in query block SEL$3 (#0)
              "EMP"."DEPTNO"=:B1
              try to generate transitive predicate from check constraints for query block SEL$3 (#0)
              finally: "EMP"."DEPTNO"=:B1
              Hope that helps.
              • 4. Re: Oracle xml view - alternative for using rownum
                320438
                To avoid full table scan of emp table, in my case cbb_prices which has hundereds of thousand records, I took an old approach of writing another pipelined function to which I pass the cbb.datetime_from and cbb.direction as implemented in the function as below:
                  TYPE cbb_series_r IS  RECORD    
                              ( 
                                position       number,
                                power          cbb_prices.power%TYPE,
                                price_euros    cbb_prices.price_euros%TYPE
                              );
                  TYPE cbb_series_t IS TABLE OF cbb_series_r;
                
                
                   function get_cbb_price_series( p_datetime_from   IN DATE,
                                                  p_direction       IN VARCHAR2) RETURN
                                                                  pkg_spc_xml.cbb_series_t PIPELINED IS
                
                
                  cbb_series      cbb_series_t;
                  p_cbb_series_r  pkg_spc_xml.cbb_series_r;
                  i_count         BINARY_INTEGER;
                  num_recs        NUMBER;
                BEGIN
                
                   cbb_series := cbb_series_t();
                
                   SELECT rownum,
                          v1.power,
                          v1.price_euros BULK COLLECT
                     INTO cbb_series
                     FROM (SELECT cb.power,
                                  cb.price_euros
                             FROM cbb_prices cb
                            WHERE cb.interconnector = 'EWIC'
                              AND cb.so_id = 'NGC'
                              AND cb.price_euros IS NOT NULL
                              AND cb.datetime_from = p_datetime_from
                              AND cb.direction = p_direction
                            ORDER BY DECODE(cb.direction,'Up',cb.price_euros) ASC,
                                     DECODE(cb.direction,'Down',cb.price_euros) DESC) v1;
                
                         dbms_output.put_line('Number of recs = ' || cbb_series.count);
                
                
                   num_recs := cbb_series.count;
                
                   FOR i_count IN 1..num_recs LOOP
                      cbb_series.extend;
                      dbms_output.put_line(cbb_series(i_count).position || chr(9) ||
                                           cbb_series(i_count).power     || chr(9) ||
                                           cbb_series(i_count).price_euros);
                      p_cbb_series_r.position    := cbb_series(i_count).position;
                      p_cbb_series_r.power       := cbb_series(i_count).power;
                      p_cbb_series_r.price_euros := cbb_series(i_count).price_euros;
                 
                      PIPE ROW(p_cbb_series_r);
                   END LOOP;
                
                   RETURN;
                
                END;
                Changed the view as follows
                .....
                
                                                                     ,XMLelement("Resolution",xmlattributes('PT60M' as "v")) 
                                                                        ,(SELECT XMLagg(XMLelement("Interval",
                                                                                  XMLelement("Pos",xmlattributes(rownum as "v")) 
                                                                                  ,XMLelement("Qty",xmlattributes(ca.power as "v")) 
                                                                                  ,XMLelement("EnergyPrice",xmlattributes(ca.price_euros as "v")) 
                                                                                                  ) ORDER BY ca.position /*ele*/
                                                                                       )  /*agg */
                                                                          FROM TABLE(pkg_spc_xml.get_cbb_price_series(cbb.datetime_from,
                                                                                                                      cbb.direction)) ca 
                                                                         ) /*   select */
                                                                   ) /* xml ele - period   
                
                
                ....
                I am sure your approach is simpler and advanced way of doing it - thanks