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

VArray type for type declaration

meghavee Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Network hiccup.
  • 8. Re: VArray type for type declaration
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Justin, cannot change the tables, because its in production with this design...thanks for your suggestions...
  • 12. Re: VArray type for type declaration
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points