3 Replies Latest reply: Jul 23, 2013 6:07 PM by gaverill RSS

    how to select value from nested object type

    user8879206

      CREATE OR REPLACE TYPE A1 AS OBJECT

      (METERING_DATA_ID NUMBER

      ,PURPOSE NUMBER

      ,MDV B1

      ,STATUS VARCHAR2(8)

      )

      /

       

      CREATE OR REPLACE TYPE B1 AS TABLE OF C1

      /

       

      CREATE OR REPLACE TYPE C1 AS OBJECT

      (VALUE NUMBER

      ,VALUE_START_TIME VARCHAR2(25)

      )

      /

       

      Can some one let me know how to display value of MDV which is nested object inside A1? I am new to object type and getting issue while displaying values of MDV.

       

      I used A1.MDV.VALUE but it did not work while A1.MDV.COUNT is working.

       

      Thanks in advance.

        • 1. Re: how to select value from nested object type
          gaverill

          "Display" is an ambiguous concept, however, assuming you're using SQL*Plus, you can allow it to display the contents of your nested table without much work. Say you have a object table (or view), T1, of A1 instances, then you can use a couple approaches. The first will display the value of the MDV nested table as a column in the SELECT:

           

          select  a1.MDV

          from    T1 a1;

           

          while the second "unnests" the nested table and displays the elements of the table as a column in the SELECT:

           

          select  value(e)

          from    T1 a1

          ,           table(a1.MDV) e;

           

          If you're using PL/SQL, then you'll need to loop thru the elements of the nested table and display the individual attributes of the object type (C1) elements of MDV, something like:

           

          declare

              o  A1;

          begin

              -- Somewhere get an instance of A1...

           

              select  value(a1)

              into     o

              from    T1 a1

              where  ....;

           

              if o.MDV is not null then

                  for i in 1..o.MDV.count

                  loop

                      dbms_output.put_line(o.MDV(i).VALUE);

                      dbms_output.put_line(o.MDV(i).VALUE_START_TIME);

                  end loop;

              end if;

          end;

          • 2. Re: how to select value from nested object type
            user8879206

            Could you please give me a live example with data as I am not able to select it they way you have suggested.

            • 3. Re: how to select value from nested object type
              gaverill

              You can test your object model easily with an object view, as such:

               

              create or replace view V_A1
              of A1
              with object identifier (METERING_DATA_ID)
              as
              select trunc(DBMS_RANDOM.VALUE(1,9999)) METERING_DATA_ID
              , trunc(DBMS_RANDOM.VALUE(0,9)) PURPOSE
              , B1(
                new C1(trunc(DBMS_RANDOM.VALUE(0,100), 2), to_char(sysdate - DBMS_RANDOM.VALUE(0,365), 'YYYY-MM-DD HH24:MI:SS'))
               , new C1(trunc(DBMS_RANDOM.VALUE(0,100), 2), to_char(sysdate - DBMS_RANDOM.VALUE(0,365), 'YYYY-MM-DD HH24:MI:SS'))
               , new C1(trunc(DBMS_RANDOM.VALUE(0,100), 2), to_char(sysdate - DBMS_RANDOM.VALUE(0,365), 'YYYY-MM-DD HH24:MI:SS'))
               ) MDV
              , 'INACTIVE' STATUS
              from DUAL
              union all
              select trunc(DBMS_RANDOM.VALUE(1,9999)) METERING_DATA_ID
              , trunc(DBMS_RANDOM.VALUE(0,9)) PURPOSE
              , B1(
                new C1(trunc(DBMS_RANDOM.VALUE(0,100), 2), to_char(sysdate - DBMS_RANDOM.VALUE(0,365), 'YYYY-MM-DD HH24:MI:SS'))
               , new C1(trunc(DBMS_RANDOM.VALUE(0,100), 2), to_char(sysdate - DBMS_RANDOM.VALUE(0,365), 'YYYY-MM-DD HH24:MI:SS'))
               , new C1(trunc(DBMS_RANDOM.VALUE(0,100), 2), to_char(sysdate - DBMS_RANDOM.VALUE(0,365), 'YYYY-MM-DD HH24:MI:SS'))
               ) MDV
              , 'ACTIVE' STATUS
              from DUAL
              

               

              Then, within SQL*Plus, simply SELECT from it and let it do the displaying:

               

              --
              -- Select the collection as a whole
              --
              select A1.MDV
              from V_A1 A1
              /
              
              MDV(VALUE, VALUE_START_TIME)
              --------------------------------------------------------------------------------
              B1(C1(83.58, '2012-09-13 14:26:30'), C1(82.81, '2013-05-13 01:48:35'), C1(1.45, '2013-04-01 09:58:30'))
              B1(C1(19.77, '2013-04-13 10:58:26'), C1(30.83, '2012-11-02 12:22:04'), C1(43.64, '2013-07-23 12:12:42'))
              
              2 rows selected.
              

               

              --
              -- Select the collection elements
              --
              select value(e)
              from V_A1 A1
              , table(A1.MDV) e
              /
              
              VALUE(E)(VALUE, VALUE_START_TIME)
              --------------------------------------------------------------------------------
              C1(71.65, '2012-11-21 20:15:49')
              C1(70.6, '2012-08-20 23:25:22')
              C1(53.94, '2013-03-05 18:48:05')
              C1(92.39, '2012-11-04 01:01:57')
              C1(26.46, '2012-09-09 17:01:33')
              C1(65.75, '2013-04-22 11:26:31')
              
              6 rows selected.
              

               

              (Not sure if I got the formatting right, but) I hope that helps.

               

              Gerard