11 Replies Latest reply: Mar 11, 2013 6:38 AM by padders RSS

    subtype declaration from column values

    meghavee
      Is there a way, i can declare subtype with values from columns in a table...
      for example I am looking for
      select element_type_name,precision,scale
      from all_coll_types where type_name='READINGS'
      i want to use element_type_name, precision and scale from table all_coll_types for subtype declaration....
        • 1. Re: subtype declaration from column values
          APC
          You don't need to use sub types. You can just reference the columns when declaring variables e.g.
          declare
              l_sal emp.sql%type;
          The variable {font:courier new}l_sal{font} inherits the datatype, scale and precision of the EMP table SAL column.

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

          Having scanned the threads which RP links to (below) I understand why this answer is not right for the OP's particular situation. Which just underlines the importance of keeping to a single thread rather than starting fresh ones without sufficient context. Otherwise you're justing wasting everybody's time.


          Cheers, APC

          Edited by: APC on Mar 7, 2013 4:29 PM
          • 3. Re: subtype declaration from column values
            meghavee
            Yes , it is...trying to solve the same problem
            • 4. Re: subtype declaration from column values
              riedelme
              meghavee wrote:
              Is there a way, i can declare subtype with values from columns in a table...
              for example I am looking for
              select element_type_name,precision,scale
              from all_coll_types where type_name='READINGS'
              i want to use element_type_name, precision and scale from table all_coll_types for subtype declaration....
              Not really. You have to declare the subtype declarations at compile time and you can't read them from a table.

              You might be able to pull this off using a dynamic PL/SQL block - look up the values you need and use the definitions when you define the block - but I strongly reccomend against using dynamic PL/SQL.
              • 5. Re: subtype declaration from column values
                rp0428
                >
                Yes , it is...trying to solve the same problem
                >
                But you have never told us in either thread what your 'problem' really is. You just keep saying you want to use a solution that I have never seen a need for in over twenty years.

                So either you have a very unusual set of circumstances or you are wanting to use the wrong solution for your problem.

                Dynamic code is rarely needed and when it is needed it is often misused. You should avoid it if at all possible.

                If what you were trying to do was really a common and viable use case Oracle would have implemented a solution for it.

                Since they haven't, it isn't.

                If you explain what problem you are trying to solve, instead of focusing on the solution you want to use, we might be able to help.
                • 6. Re: subtype declaration from column values
                  meghavee
                  Yes its an unusual requirement...We have table design which contains a table(INTERVALS) with collection type columns(VARRAY,NESTED TABLE) to store data which is sensitive to precision/scale...The batch process(in store procedure) retrieves values from these columns , changes them and updates them with new values. We always need zero precision when we are updating with new values so we need to match the precision on the columns(in INTERVALS) in our procedures...

                  The precision on the array columns can change and so we need to change our procedures for the change....The store procedures are retrieving values, performing some mathematical calculations and updating them, so we need to preserve precision while calculating new values.

                  In short we are looking at minimal code changes whenever array column precision/scale changes and deployment has lesser hassles...

                  I do not know, if I could explain it clearly enough...
                  • 7. Re: subtype declaration from column values
                    padders
                    If you used a nested table rather than a varying array you could anchor your declaration to the nested table's storage table.
                    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 TYPE type_name
                      2  AS
                      3     TABLE OF NUMBER (3, 1);
                      4  /
                    
                    Type created.
                    
                    SQL> CREATE TABLE table_name (column_name type_name)
                      2     NESTED TABLE column_name STORE AS nested_table_name;
                    
                    Table created.
                    
                    SQL> DECLARE
                      2     variable_name nested_table_name.column_value%type;
                      3  BEGIN
                      4     variable_name := 123123;
                      5  END;
                      6  /
                    DECLARE
                    *
                    ERROR at line 1:
                    ORA-06502: PL/SQL: numeric or value error: number precision too large
                    ORA-06512: at line 4
                    
                    SQL>
                    If the precision and scale really do vary frequently then you should probably look at storing precision and scale as attributes and managing rounding in your application code.
                    • 8. Re: subtype declaration from column values
                      meghavee
                      padders wrote:
                      If you used a nested table rather than a varying array you could anchor your declaration to the nested table's storage table.
                      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 TYPE type_name
                      2  AS
                      3     TABLE OF NUMBER (3, 1);
                      4  /
                      
                      Type created.
                      
                      SQL> CREATE TABLE table_name (column_name type_name)
                      2     NESTED TABLE column_name STORE AS nested_table_name;
                      
                      Table created.
                      
                      SQL> DECLARE
                      2     variable_name nested_table_name.column_value%type;
                      3  BEGIN
                      4     variable_name := 123123;
                      5  END;
                      6  /
                      DECLARE
                      *
                      ERROR at line 1:
                      ORA-06502: PL/SQL: numeric or value error: number precision too large
                      ORA-06512: at line 4
                      
                      SQL>
                      Thanks Padders. However, Can we do the same if we have NESTED TABLE of OBJECT type..Your example shows NESTED TABLE of NUMBER(3,1)...Can we take the same approach for NESTED TABLE of OBJECT...I haven't been able to do variable_name nested_table_name.column_name%type;
                      • 9. Re: subtype declaration from column values
                        rp0428
                        >
                        Can we do the same if we have NESTED TABLE of OBJECT type
                        >
                        No - not for the attributes of the object.
                        • 10. Re: subtype declaration from column values
                          padders
                          Can we do the same if we have NESTED TABLE of OBJECT type..
                          Well I would say yes but unless you post clear examples it is possible may be talking about slightly different configurations.
                          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 TYPE type_name AS OBJECT (
                            2     attribute_name NUMBER (3, 1));
                            3  /
                          
                          Type created.
                          
                          SQL> CREATE TYPE collection_type_name AS TABLE OF type_name;
                            2  /
                          
                          Type created.
                          
                          SQL> CREATE TABLE table_name (
                            2     column_name collection_type_name)
                            3     NESTED TABLE column_name
                            4        STORE AS nested_table_name;
                          
                          Table created.
                          
                          SQL> DECLARE
                            2     variable_name nested_table_name.attribute_name%TYPE;
                            3  BEGIN
                            4     NULL;
                            5  END;
                            6  /
                          
                          PL/SQL procedure successfully completed.
                          
                          SQL>
                          In fact there is another option here too for object types with named attributes.
                          SQL> DECLARE
                            2     type_variable_name type_name;
                            3     variable_name type_variable_name.attribute_name%TYPE;
                            4  BEGIN
                            5     NULL;
                            6  END;
                            7  /
                          
                          PL/SQL procedure successfully completed.
                          
                          SQL>
                          • 11. Re: subtype declaration from column values
                            padders
                            No - not for the attributes of the object.
                            Actually I think you can - see my reply to OP.