3 Replies Latest reply on Aug 17, 2016 11:44 AM by AhmetMelih

    Accessing elements in a VARRAY column which is in a type

    AhmetMelih

      Hello. I tried to reach my varray column in a type which i created. I created my varray as

       

      SQL> desc test.vector;

                 Name                                            Null?    Type

      ----------------------------------------- -------- ----------------------------

                   x                                                      NUMBER

                   y                                                         NUMBER

                   coordinate                                        VARCHAR2(50)

       

      SQL> desc TEST.vetor_va;

      TEST.vetor_va VARRAY(10) OF TEST.VECTOR

                      Name                         Null?    Type

      ----------------------------------------- -------- ----------------------------

                  x                                                      NUMBER

                   y                                                         NUMBER

                   coordinate                                        VARCHAR2(50)

       

      I have one type which I extended from another type.

       

      SQL> desc TEST.WORKERS

      test.workers extends test.person

      Name   Null?    Type

      ----------------------------------------- -------- ----------------------------

      DESCRIPTION    VARCHAR2(4000)

      NAME    VARCHAR2(31)

      COMMENTS    CLOB

      VEKTOR    TEST.VETOR_VA

       

      METHOD

      ------

      STATIC FUNCTION INIT RETURNS PERSON

       

      METHOD

      ------

      STATIC FUNCTION INIT RETURNS PERSON

      Argument Name Type In/Out Default?

      ------------------------------ ----------------------- ------ --------

      LOCATION VARCHAR2 IN

      NAME VARCHAR2 IN

       

      METHOD

      ------

      MEMBER FUNCTION GETUPDATETIME RETURNS DATE

       

      my test table

       

      SQL> desc person_test;

      Name                       Null?    Type

      -------------------------- -------- ----------------------------

      ID                                            NUMBER

      INFORMATION                      TEST.WORKERS

       

       

      I Couldn't reach vector's values from person_test table. how can i create getX and getY and getcordinate() functions to show varray's result like that ?

       

      select t.id,

              t.information.getX() x,

              t.information.getY() y,

              t.information.getcordinate() cordinate,

               from person_test t;

       

      Example result may be;

       

      id    x     y     coordinate

      ----  ----   ---    ---------------

      1      1    2       (1,2)

                2    2        (2,2)

      2      1     1         (1,1)

        • 1. Re: Accessing elements in a VARRAY column which is in a type
          Zlatko Sirotic

          Try something like this:

           

          CREATE TYPE vector_t AS OBJECT (

            x NUMBER (4),

            y NUMBER (4)

          )

          /

           

          CREATE TYPE vector_arr_t AS ARRAY (10) OF vector_t

          /

           

          CREATE TYPE worker_t AS OBJECT (

            description VARCHAR2(100),

            name VARCHAR2(30),

            vector vector_arr_t

          )

          /

           

          CREATE TABLE person_test (

            id NUMBER PRIMARY KEY,

            information worker_t

          )

          /

           

          INSERT INTO person_test VALUES

            (1, worker_t ('DESC A', 'NAME A', vector_arr_t (vector_t (1, 1), vector_t (2, 2))));

          INSERT INTO person_test VALUES

            (2, worker_t ('DESC B', 'NAME B', vector_arr_t (vector_t (3, 3), vector_t (4, 4))));

          INSERT INTO person_test VALUES

            (3, worker_t ('DESC C', 'NAME C', NULL));

          INSERT INTO person_test VALUES

            (4, worker_t ('DESC D', 'NAME D', vector_arr_t (vector_t (5, 5))));

           

          SELECT p.id, v.x, v.y

            FROM person_test p,

                 TABLE (p.information.vector) v;

           

                  ID          X          Y

          ---------- ---------- ----------

                   1          1          1

                   1          2          2

                   2          3          3

                   2          4          4

                   4          5          5

           

          Regards,

          Zlatko

          1 person found this helpful
          • 2. Re: Accessing elements in a VARRAY column which is in a type
            Zlatko Sirotic

            A small addition:

             

            SELECT p.id, v.x, v.y

              FROM person_test p,

                   TABLE (p.information.vector) ( + ) v

            ORDER BY 1;

             

                    ID          X          Y

            ---------- ---------- ----------

                     1          1          1

                     1          2          2

                     2          3          3

                     2          4          4

                     3

                     4          5          5

             

            If you want to know how this SELECT works, see "table_collection_expression" in:

            https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#i2104990

            "The collection_expression can reference columns of tables defined to its left in the FROM clause. This is called left correlation. Left correlation can occur only in table_collection_expression."

             

            Regards,

            Zlatko