1 2 Previous Next 15 Replies Latest reply: Mar 6, 2013 6:25 AM by meghavee RSS

    VArray type for type declaration

    meghavee
      I am looking, for passing VArray type as a data type in variable declaration. We have a table with VArray type column and we have various procedures which query this VArray,however all the variable declarations are hardcoded to the type and precision of this VArray, Is there a way to use Type attribute on the type of VArray.I have been trying to find solution to this problem for 2 days now.
        • 1. Re: VArray type for type declaration
          rp0428
          Welcome to the forum!

          Whenever you post provide your 4 digit Oracle version
          >
          I am looking, for passing VArray type as a data type in variable declaration. We have a table with VArray type column and we have various procedures which query this VArray,however all the variable declarations are hardcoded to the type and precision of this VArray, Is there a way to use Type attribute on the type of VArray.I have been trying to find solution to this problem for 2 days now.
          >
          You need to provide some sample data and a better explanation of what you are trying to do with it.

          VARRAYS have a fixed size so it makes sense for them to be 'hardcoded' for that.

          See the PL/SQL Language doc for how to define and use VARRAYs
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#CJAEJIAI
          >
          Choosing Between Nested Tables and Varrays
          Varrays are a good choice when:

          •The number of elements is known in advance.

          •The elements are usually accessed sequentially.

          When stored in the database, varrays keep their ordering and subscripts.

          A varray is stored as a single object. If a varray is less than 4 KB, it is stored inside the table of which it is a column; otherwise, it is stored outside the table but in the same tablespace.

          You must store or retrieve all elements of a varray at the same time, which is appropriate when operating on all the elements at once. However, this might be impractical for large numbers of elements.
          • 2. Re: VArray type for type declaration
            odie_63
            however all the variable declarations are hardcoded to the type and precision of this VArray
            Not sure I understand.
            Can't you use the type name directly, or the %type attribute ?
            SQL> create type my_varray is varray(5) of number(3);
              2  /
             
            Type created
             
            SQL> create table my_table (my_id integer, my_varray_col my_varray);
             
            Table created
             
            SQL> insert into my_table values(1, my_varray(1,2,3));
             
            1 row inserted
             
            SQL> set serveroutput on
            SQL> 
            SQL> declare
              2    arr    my_table.my_varray_col%type;
              3  begin
              4    select my_varray_col
              5    into arr
              6    from my_table
              7    where my_id = 1;
              8  
              9    for i in 1..arr.count loop
             10      dbms_output.put_line(arr(i));
             11    end loop;
             12  end;
             13  /
             
            1
            2
            3
             
            PL/SQL procedure successfully completed
             
            SQL> 
            SQL> declare
              2    arr    my_varray;
              3  begin
              4    select my_varray_col
              5    into arr
              6    from my_table
              7    where my_id = 1;
              8  
              9    for i in 1..arr.count loop
             10      dbms_output.put_line(arr(i));
             11    end loop;
             12  end;
             13  /
             
            1
            2
            3
             
            PL/SQL procedure successfully completed
             
            • 3. Re: VArray type for type declaration
              meghavee
              Hi Olgie,Thanks for the reply...we need to retrieve the type of elements varray stores and use those for variable declaration across the system. So if the precision or scale changes we need minimal code changes.
              For example we have Varray of number(9,5) and we need to declare variables of number(9,5), retrieve varray of type and use that for variable declaration
              example
              create type my_varray is varray(20) of number(9,5);
              create table my_table (my_id integer, my_varray_col my_varray);
              temp my_table.my_varray.column_type? something like this...is there any way we can retrieve the elements stored by the varray and use them for variable declaration?

              Edited by: 991698 on Mar 4, 2013 7:43 PM
              • 4. Re: VArray type for type declaration
                JustinCave
                How is this different than the example odie_63 posted with the
                my_table.my_varray_col%type;
                declaration?

                Justin
                • 5. Re: VArray type for type declaration
                  meghavee
                  Justin, my_table.my_varray_col%type; this will give me varray type not type of varray. I want to retrieve Varray of type number(9,5), i want number(9,5) as type not varray.....
                  • 6. Re: VArray type for type declaration
                    meghavee
                    Justin, my_table.my_varray_col%type; this will give me varray type not type of varray. I want to retrieve Varray of type number(9,5), i want number(9,5) as type not varray.....
                    • 7. Re: VArray type for type declaration
                      rp0428
                      Network hiccup.
                      • 8. Re: VArray type for type declaration
                        Billy~Verreynne
                        991698 wrote:
                        Justin, my_table.my_varray_col%type; this will give me varray type not type of varray. I want to retrieve Varray of type number(9,5), i want number(9,5) as type not varray.....
                        Parameter numeric types are typically defined as number - and cannot be constrained via number size or precision. So exactly why do you need number(9,5) as data type?

                        E.g.
                        SQL> declare
                          2          subtype small_num is number(2,0);
                          3          n       number(4,2);
                          4  
                          5          procedure Foo( n small_num ) is
                          6                  n1      small_num;
                          7                  n2      number;
                          8          begin
                          9                  dbms_output.put_line( 'Foo(): parameter n='||n );
                         10                  n2 := n * 1;
                         11                  dbms_output.put_line( 'Foo(): used parameter n in calculcation' );
                         12  
                         13                  n1 := n;        --// exception here - on variable n1 and not parameter n
                         14          exception when OTHERS then
                         15                  dbms_output.put_line( 'Foo(): '||SQLERRM(SQLCODE) );
                         16          end;
                         17  
                         18  begin
                         19          n := 99.99;
                         20          Foo( n );
                         21  end;
                         22  /
                        Foo(): parameter n=99.99
                        Foo(): used parameter n in calculcation
                        Foo(): ORA-06502: PL/SQL: numeric or value error: number precision too large
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL>
                        So for example, even if you define the parameter date type to number(9,5) via a subtype, it does not constrain the parameter values passed.
                        • 9. Re: VArray type for type declaration
                          meghavee
                          Billy,olgie,justin, all i want to do is 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)
                          • 10. Re: VArray type for type declaration
                            JustinCave
                            Since you asked for a workaround...

                            Why are you creating a table with a VARRAY column in the first place? It would generally make far more sense to create a separate table for READINGS with a foreign key that lets you relate the rows back to the INTERVALS table. You could then easily enough declare columns of type READINGS.COLUMN_NAME%TYPE.

                            Collections are wildly useful in PL/SQL. I've never seen a case where they improved on a standard normalized approach to data modeling. I have seen multiple cases where incorporating collections into your data model ends up making your data model harder to work with and your code harder to write and maintain.

                            Justin
                            • 11. Re: VArray type for type declaration
                              meghavee
                              Thanks Justin, cannot change the tables, because its in production with this design...thanks for your suggestions...
                              • 12. Re: VArray type for type declaration
                                Billy~Verreynne
                                991698 wrote:
                                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)
                                Not supported as far as I know....

                                Assuming a sane design and approach (which I agree with Justin, does not seem to be the case), I would use a custom PL/SQL package for defining subtypes for the array elements. E.g.
                                create or replace package VarrayType as
                                  subtype Consumption_Intervals_Readings is number(25,9);
                                  .. etc ..
                                end;
                                PL/SQL code then need to reference this package, as they would have referenced built-in compiler macros types like +%Type+ or +%RowType+.

                                The problem is of course with the package approach, you have a static approach. And not a dynamic one like compiler macros provide.

                                Which means you need another package called BuildVarrayTypes that dynamically recreates the VarrayType package, based on the existing metadata of that schema. It detects tables with varray data types, and for each varray column, generate a unique subtype for PL/SQL code to use - and generate the subtype using a standard naming convention that makes it easy for developers to refer to a the type for a specific column in a specific table.

                                Hmm.. what would be "nice" (depends on how one views it), would be for existing PL/SQL compiler instructions to support SQL and PL/SQL statements to dynamically define substitution keywords... :-)
                                • 13. Re: VArray type for type declaration
                                  meghavee
                                  Hi Billy, sounds interesting..
                                  Which means you need another package called BuildVarrayTypes that dynamically recreates the VarrayType package, based on the existing metadata of that schema. It detects tables with varray data types, and for each varray column, generate a unique subtype for PL/SQL code to use - and generate the subtype using a standard naming convention that makes it easy for developers to refer to a the type for a specific column in a specific table.
                                  How would i dynamically recreate VArray type package? Can you please give me some sample code?

                                  Edited by: 991698 on Mar 5, 2013 1:02 PM
                                  • 14. Re: VArray type for type declaration
                                    Billy~Verreynne
                                    Simplistic example:
                                    SQL> create or replace type TMoneyValues as table of number(9,2);
                                      2  /
                                    
                                    Type created.
                                    
                                    SQL> create or replace type TLowValues as table of number(3,0);
                                      2  /
                                    
                                    Type created.
                                    
                                    SQL> 
                                    SQL> create or replace type TLabels as table of varchar2(30);
                                      2  /
                                    
                                    Type created.
                                    
                                    SQL> 
                                    SQL> create table testtab(
                                      2          id      number primary key,
                                      3          n1      TMoneyValues,
                                      4          n2      TLowValues,
                                      5          s1      TLabels
                                      6  )
                                      7  nested table n1 store as testtab_n1
                                      8  nested table n2 store as testtab_n2
                                      9  nested table s1 store as testtab_s1
                                     10  /
                                    
                                    Table created.
                                    
                                    SQL> 
                                    SQL> 
                                    SQL> create or replace package TypeLibManager as
                                      2  
                                      3          function CreatePackageDDL return varchar2;
                                      4  
                                      5          procedure CreatePackage;
                                      6  end;
                                      7  /
                                    
                                    Package created.
                                    
                                    SQL> 
                                    SQL> create or replace package body TypeLibManager as
                                      2  
                                      3  TAB                     constant varchar2(1) := chr(9);
                                      4  LF                      constant varchar2(1) := chr(10);
                                      5  
                                      6  
                                      7  PACKAGE_TEMPLATE        constant varchar2(32767) :=
                                      8  'create or replace package TypeLib as
                                      9  $COMMENTS
                                     10  $TYPE_DEFINITIONS
                                     11  end;';
                                     12  
                                     13  
                                     14  
                                     15  function CreatePackageDDL return varchar2 is
                                     16          ddl             varchar2(32767);
                                     17          typeDefs        varchar2(32767);
                                     18  begin
                                     19          ddl := replace( PACKAGE_TEMPLATE, '$COMMENTS', '-- Automated generation on '||to_char(sysdate,'yyyy/mm/dd hh24:mi') );
                                     20  
                                     21          for c in (
                                     22                  select
                                     23                          c.table_name,
                                     24                          c.column_name,
                                     25                          c.data_type,
                                     26                          t.elem_type_name,
                                     27                          t.precision,
                                     28                          t.length,
                                     29                          t.scale
                                     30                  from    user_tab_cols c, user_coll_types t
                                     31                  where   c.data_type = t.type_name
                                     32                  order by 1,2
                                     33          ) loop
                                     34                  case c.elem_type_name
                                     35                          when 'NUMBER' then
                                     36                                  typeDefs := typeDefs || TAB ||
                                     37                                          'subtype '||c.table_name||'_'||
                                     38                                          c.column_name||' is number('||
                                     39                                          c.precision||
                                     40                                          ','||c.scale||');' || LF;
                                     41                          when 'VARCHAR2' then
                                     42                                  typeDefs := typeDefs || TAB ||
                                     43                                          'subtype '||c.table_name||'_'||
                                     44                                          c.column_name||' is varchar2('||
                                     45                                          c.length||');'||LF;
                                     46                  else
                                     47                          typeDefs := typeDefs || TAB || '-- no support for '||
                                     48                                  c.table_name||' '||c.column_name||' '||c.data_type||LF;
                                     49                  end case;
                                     50          end loop;
                                     51  
                                     52          ddl := replace( ddl, '$TYPE_DEFINITIONS', typeDefs );
                                     53          return( ddl );
                                     54  end;
                                     55  
                                     56  procedure CreatePackage is
                                     57  begin
                                     58          execute immediate CreatePackageDDL;
                                     59  end;
                                     60  
                                     61  
                                     62  end;
                                     63  /
                                    
                                    Package body created.
                                    
                                    SQL> 
                                    SQL> select TypeLibManager.CreatePackageDDL as DDL from dual;
                                    
                                    DDL
                                    ------------------------------------------------------
                                    create or replace package TypeLib as
                                    -- Automated generation on 2013/03/06 00:00
                                            subtype TESTTAB_N1 is number(9,2);
                                            subtype TESTTAB_N2 is number(3,0);
                                            subtype TESTTAB_S1 is varchar2(30);
                                    
                                    end;
                                    
                                    
                                    SQL> 
                                    SQL> exec TypeLibManager.CreatePackage
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    SQL> 
                                    SQL> declare
                                      2          n1      TypeLib.TESTTAB_N1;
                                      3          s1      TypeLib.TESTTAB_S1;
                                      4  begin
                                      5          n1 := 123.12;
                                      6          s1 := 'EMP_NAME';
                                      7  end;
                                      8  /
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    SQL> 
                                    1 2 Previous Next