5 Replies Latest reply: Mar 5, 2013 10:02 AM by meghavee RSS

    Retrieve VArray element type using TYPE Attribute

    meghavee
      i want to retrieve type of elements varray stores through type attribute or ANY work around.

      for example our type is defined like this CREATE TYPE "READINGS" AS VARRAY (200) OF NUMBER(21, 6);(readings is varray with elements of type number(21,6))

      READINGS is a column in a table INTERVALS. INTERVALS is a central table and we have batch processes on INTERVALS which execute sql store procedures. In store procedure we have hard coded variable declarations mapping to the READING VArray type element type which is NUMBER(21, 6); for example the store procedure has variable declarations like

      CONSUMPTION NUMBER(21, 6);

      whenever Varray definition is changed or varray is dropped and recreated with different size and precision, ex instead on number(21,6) is changed to number(25,9) we need variable declarations to change in our batch process store procedures. All i am looking for is making CONSUMPTION variable declaration, refer to element type of VArray. I want something like this

      CONSUMPTION INTERVALS.READINGS.COLUMN_TYPE%TYPE; (i want some thing like this, refer to the type of elements stored by varray)
        • 1. Re: Retrieve VArray element type using TYPE Attribute
          padders
          Was there something wrong with your previous thread or are you just more important than everyone else?
          • 2. Re: Retrieve VArray element type using TYPE Attribute
            meghavee
            sorry just trying to make my question visible....i am not able to figure out it myself so seeking help....just started using this forum, do not know if violated any rules or something...
            • 3. Re: Retrieve VArray element type using TYPE Attribute
              padders
              No problem. The forum flags threads viewers have read so you don't need to keep posting.

              In answer to your question I don't know of a direct method but it does appear to be syntactically possible to anchor it via a rather circuitous CURSOR, %ROWTYPE and %TYPE route.
              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              SQL> CREATE OR REPLACE TYPE type_name
                2  AS
                3     VARRAY (200) OF VARCHAR2 (3);
                4  /
              
              Type created.
              
              SQL>
              SQL> DECLARE
                2     CURSOR cursor_name
                3     IS
                4        SELECT COLUMN_VALUE column_name
                5        FROM   TABLE (type_name ());
                6
                7     record_name cursor_name%ROWTYPE;
                8
                9     variable_name record_name.column_name%TYPE := 'AAAA';
               10  BEGIN
               11     NULL;
               12  END;
               13  /
              DECLARE
              *
              ERROR at line 1:
              ORA-06502: PL/SQL: numeric or value error: character string buffer too small
              ORA-06512: at line 9
              
              SQL>
              However if you try this with a numeric element type the precision and scale are not preserved and you will end up with a NUMBER.

              I would just declare a matching SUBTYPE in one place, declare variables based on that and be done with it.
              • 4. Re: Retrieve VArray element type using TYPE Attribute
                rp0428
                >
                whenever Varray definition is changed or varray is dropped and recreated with different size and precision, ex instead on number(21,6) is changed to number(25,9) we need variable declarations to change in our batch process store procedures.
                >
                That isn't possible. If it were absolutely necessary then you need to create your own type that has ONE attribute of number(21,6) and then define your VARRAY and you variable declarations to use that new custom type.

                You do realize, don't you, that you will NOT be able to change that VARRAY definition when the table that uses it contains data?
                • 5. Re: Retrieve VArray element type using TYPE Attribute
                  meghavee
                  Thanks Padders and rp0428,

                  I would just declare a matching SUBTYPE in one place, declare variables based on that and be done with it.

                  maybe will have to take this approach...

                  You do realize, don't you, that you will NOT be able to change that VARRAY definition when the table that uses it contains data?

                  RP0428, the table is in production with this VArray Number(21,6). Whenever size and precision change is needed, do need to take into account data backup before executing force delete on this type. Thanks for your suggestions.