1 2 Previous Next 15 Replies Latest reply: Aug 7, 2013 10:08 AM by Mike Kutz RSS

    Dynamically assign values to different ROWTYPE-Columns by ruletable

    seggione

      Hello,

       

       

      i hope you can give me an advice how to assign values from a "ruletable" to a rowtype-variable.

       

       

      The ruletable contains values for different columns in different tables.

      Now i need to assign those given values for given columns out of that ruletable to the equivalent column in a rowtype-variable.

       

          CREATE TABLE TBRULES
              (
                  TABLE     VARCHAR2(50 BYTE)
              ,    COLUMN    VARCHAR2(50 BYTE)
              ,    VALUE     VARCHAR2(200 BYTE)
              );
      
          INSERT ALL
              INTO TBRULES (TABLE, COLUMN, VALUE) VALUES ('TABLE1', 'COLUMN1', '2')
              INTO TBRULES (TABLE, COLUMN, VALUE) VALUES ('TABLE1', 'COLUMN3', 'abc')
              INTO TBRULES (TABLE, COLUMN, VALUE) VALUES ('TABLE1', 'COLUMN5', 'def')
              SELECT * FROM DUAL;
      
          CREATE TABLE TABLE1
              (   
                  COLUMN1    NUMBER
              ,    COLUMN2    VARCHAR2(50)
              ,    COLUMN3    VARCHAR2(50)
              ,    COLUMN4    VARCHAR2(50)
              ,    COLUMN5    VARCHAR2(50)
              );
      

       

      Sample:

       

      DECLARE
          vTABLE1 TABLE1 % ROWTYPE;
      BEGIN
          FOR i IN (SELECT * FROM TBRULES)
          LOOP   
              vTABLE1.? := i.VALUE;
          END LOOP;
      END;
      
      

       

       

      The result should be:

      vTABLE1.COLUMN1 = 2

      vTABLE1.COLUMN3 = 'abc'

      vTABLE1.COLUMN5 = 'def'

       

       

      BANNER                                                                       

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

      Oracle Database 11g Release 11.2.0.3.0 - 64bit Production                      

      PL/SQL Release 11.2.0.3.0 - Production                                         

      CORE    11.2.0.3.0    Production                                                       

      TNS for Linux: Version 11.2.0.3.0 - Production                                 

      NLSRTL Version 11.2.0.3.0 - Production

       

       

      Thank you very much.

       

      André

        • 1. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
          Mike Kutz

          Why would this not work for you?

           

          ALTER TABLE TABLE1 MODIFY (
             COLUMN1 default 2
            ,COLUMN3 default 'abd'
            ,COLUMN5 default 'def'
          );
          
          • 2. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
            Billy~Verreynne

            The approach you've sketched is inherently flawed.

             

            Let's say you have a 15 column table, 5 rules, and with these 5 rules applied to 10 columns.

             

            This means 5 I/Os per row to read the 5 rules. 10 rule executions per row, to apply these to the 10 columns, eating at CPU.

             

            For. Every. Single. Row.

             

            This will not perform. This will destroy scalability.

             

            The more rows and tables being run via this silly rules tables - the more the overheads in CPU and I/O.

             

            Epic Fail.

            • 3. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
              seggione

              This would not work for me because:

              In case of TABLE1.COLUMN1 = 1 i need to set the values given in the example.

              In case of TABLE1.COLUMN1 = 2 i need to set the totally different values.

              Default values wouldn't work for this demand.

              • 4. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                seggione

                As is answered to MikeKutz i can't use default values.

                My purpose is to define ruled values to process order data from different customers.

                 

                Sure, this way means n readings for every ruled column for table1.

                To avoid n writings to the pyhsical table i'm using a variable of TABLE1 % ROWTYPE. So i can write all values once.

                 

                Maybe you can advice some better approach?

                • 5. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                  Billy~Verreynne

                  A better approach would depend on the data model and business requirements.

                   

                  A rule table (i.e. rules as data and not code), points to attempting to solve this problem (what exactly is the problem?) in a dynamic way. As data implies rules are maintained by some end user interface.

                   

                  This is a complex approach. And risky and dangerous.

                   

                  The standard approach to implementing rules in Oracle is as PL/SQL code via some abstraction interface - where this provides a business function interface to the caller, and hides the rules (technical and business) that are applied, the data entities used, the SQLs created, and so on.

                   

                  For example, such an interface call can be RebateForAccount() - supporting parameters like account id, rebate type, and rebate value. This interface applies all the relevant business rules (e.g. can rebate type be applied to this account type?, etc), and technical rules (e.g. is rebate value positive and not zero?, etc). And these rules are PL/SQL modules that are applied. I.e. code (perhaps using data). And not as data that uses code.

                   

                  If you want to use data to apply rules, then there are numerous, and complex, issues to deal with. Inference rules. Backward chaining. Forward chaining. Order of rules. Conflicting rules. Etc. Etc.

                   

                  Rules as data is not suited for standard business type processing.

                  • 6. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                    seggione

                    Erm, ok! I'm far away from beeing a software architect or professional programmer. But i think i understood it almost halfway.

                     

                    We approach to create some kind of standardized converter to import orders from different customers in different formats; e. g. EDIFACT, Flatfiles, etc.

                    All needed data are already available in the database. There is no need of any interface for end users. All of configuration and converting should exist in the database in a standardized way.

                     

                    To get back to the initial point of this issue and even if it costs more ressources and is (maybe) the wrong way. How can i transfer those values from the rule-table into the variable of ROWTYPE-Type?

                    • 7. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                      Mike Kutz

                      Ok, so you are building an ETL.

                      ROWTYPE's should not be involved as it implies slow-by-slow (row-by-row) processing.

                      The application should load the data into a GTT then call a procedure to BULK PROCESS the data.

                      If there is a complex process that requires row-by-slow-row processing, THEN 'rowtype' gets involved.. but only because its implied when you LOOP.

                       

                      Most people will ask "how often do the 'defaults' change?".

                      However, I feel that the correct question should be "Why do the defaults change?".

                      Based on the answer to my question, you may discover that you want to hard-code the values.

                      • 8. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                        Billy~Verreynne

                        To summarise what I see as the requirement:

                        1. read a file
                        2. parse data read from file
                        3. insert data into relevant table

                         

                        The complexity of creating a generic framework to do this, depends on the complexity of the various file formats.

                         

                        Oracle provides, as data file loading interfaces, SQL*Loader and External Tables. These are generic (capable of supporting a wide range of file formats). And provides excellent performance. This should be the primary consideration for the above requirements.

                         

                        Assuming the file formatting is so funky that it cannot be loaded using SQL*Loader/External Tables, then you need to write a parser that has the ability to do it. My approach would be a parser that parses and reads tokens from the file - then using these tokens to create the structured data to insert into the database.

                         

                        A basic example of this approach is in Re: requesting financial data from yahoo site. It parses a web service's output, instead of an actual file. The basic principles however remain the same.

                         

                        As for default values, variant data structures and so on - I would extend the SQL classes (types) in that example to include subclassing and a number of constructors with different parameter signatures.

                         

                        I would however not consider storing code in tables as rules, that need to be read and executed dynamically at run-time.

                        • 9. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                          seggione

                          Hi Mike,

                          hi Billy,

                           

                          yes the goal is to build an ETL; never heard that term before / good to know.

                           

                          We've already realized a solution (Pyhton-based) to parse external data sources and load them into database.

                          Actually i assume to BULK Collect those defaults (as you said) into a GTT to transform values in this already mentioned ROWTYPE.

                          But i still don't know how...So please give me an advice...

                           

                          I took a look https://forums.oracle.com/message/10160148#10160148 which is a fairly good hint for upcoming data processing. Thank you!

                          • 10. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                            Mike Kutz

                            seggione wrote:

                            We've already realized a solution (Pyhton-based) to parse external data sources and load them into database.

                            Actually i assume to BULK Collect those defaults (as you said) into a GTT to transform values in this already mentioned ROWTYPE.

                            But i still don't know how...So please give me an advice...

                            Your process seems flawed.

                            From the Python side, after all the data is INSERT-ed into a table (most likely a GTT), the Python code should be calling a "process_new_data()" procedure.

                             

                            That procedure should be using INSERT ... SELECT statements to process the data as much as possible.

                            There is nothing you have stated indicating a process so complex that a row-by-row(slow-by-slow) method is the way to go.

                            You want to fill in default values?  use NVL() and variables.

                             

                            procedure process_new_data
                            as
                              l_default_c1 TABLE1.COLUMN1%TYPE; -- notice how this variable is the same type as the table
                              l_default_c3 TABLE1.COLUMN3%TYPE; -- your current table design can't guarantee that the default value for a number column is a number
                              l_default_c5 TABLE1.COLUMN5%TYPE; -- or, worst, a date is being stored as a string.
                            begin
                              -- MANY ways to get 'default' values.
                              -- you could HARD CODE the values like this
                              l_default_c1 := 2; l_default_c3 := 'abc'; l_default_c5 := 'def';
                            
                              -- OR (slightly better), create a package holding the constants
                              l_default_c1 := MyConstants.TABLE1_COLUMN1_DEFAULT;
                              l_default_c3 := MyConstants.TABLE1_COLUMN3_DEFAULT;
                              l_default_c5 := MyConstatns.TABLE1_COLUMN5_DEFAULT;
                            
                              -- OR the 'defaults' being used are actually IN parameters for this procedure
                              -- process_new_data( p_default_c1 in number, p_default_c3 in varchar2, p_default_c5 in varchar2 )
                            
                              -- your current table design for defaults is bad.
                              -- If you build a constants package, it actually makes no sense
                              -- at the very least, it should be CREATE TABLE DEFAULT_VALUES ( TABLE1_COLUM1 number, TABLE1_COLUMN3 varchar2(3), ....)
                              select TABLE1_COLUMN1, TABLE1_COLUMN3, TABLE1_COLUMN5
                                into l_default_c1, l_default_c3, l_default_c5
                              from DEFAULT_VALUES
                              where use_this_row = p_some_input_parameter_value_for_the_procedure
                              ;
                              -- again, to go from your design to this idea, look up PIVOT
                            
                              -- process data here
                              insert into TABLE1 (column1, column2, column3, column4, column5 )
                              SELECT nvl(C1, l_default_c1),C2,nvl(C3, l_default_c3),C4, nvl(C5, l_default_c5)
                              FROM STAGING_TABLE1_GTT;
                            
                              -- notice the lack of BULK COLLECT.
                              -- notice the lack of ROWTYPE
                            end;
                            
                            • 11. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                              seggione

                              MikeKutz wrote:

                               

                              Most people will ask "how often do the 'defaults' change?".

                              However, I feel that the correct question should be "Why do the defaults change?".

                              Based on the answer to my question, you may discover that you want to hard-code the values.

                              Based on your last answer and the questions (as quoted) i come to the point to be more specific.

                               

                              The defaults for different columns in different tables change by the depending customer.

                              The full structure of this "defaults"-table looks like:

                               

                              CUSTOMER_IDTABLECOLUMNVALUE
                              1TABLE1COLUMN1A
                              1TABLE1COLUMN22
                              2TABLE1COLUMN1B
                              3TABLE1COLUMN3U
                              3TABLE1COLUMN4V
                              3TABLE1COLUMN5W
                              3TABLE2COLUMN1xyz

                               

                              While processing i know which table i have to handle, but i don't know which columns of the table will be affected with this "defaults". That's why i can't hardcode those defaults.

                              For customer_id=1 i need to set values for column1 and column2. For customer_id=2 only column1, but with totally different values compared to customer=1.

                              For customer_id=3 i need to set default values for totally different column compared to customer_id 1 or 2.

                              Possibly i need to get defaults for customer_id = 3 in another table.

                               

                              So it's not just about declaring TABLE1.COLUMN1.DATA_DEFAULTs or using NVL for constant columns.

                               

                              And, not to forget, i need to assign those defaults to TABLE1 % ROWTYPE!

                               

                              That's because the relative complex logic for importing orders needs to access data in different tables which i hold in several ROWTYPE-vars. I don't want to reread those data in repeated SQL-statements.

                               

                              Thank you very much!

                              • 12. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                                padders

                                Why have you decided to model these defaults in a generic structure when a.) the remainder of your data appears to be modelled in specific structures and b.) you have no idea how to implement such?

                                 

                                Assuming you had (for example) an orders table with a foreign key to customers, why not either create an 'order_defaults' table with the same structure to store your defaults in for each customer, or store the defaults in the orders table itself, adding a column to distinguish between default and actual values. Both of these approaches would allow you to easily fetch the default values into %ROWTYPE on a per-customer basis, if your ETL is really sufficiently complex to warrant row-by-row processing.

                                • 13. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                                  seggione

                                  Hi padders,

                                   

                                  thanks for your reply.

                                   

                                  padders wrote:

                                   

                                  Why have you decided to model these defaults in a generic structure when a.) the remainder of your data appears to be modelled in specific structures and b.) you have no idea how to implement such?

                                   

                                  Assuming you had (for example) an orders table with a foreign key to customers, why not either create an 'order_defaults' table with the same structure to store your defaults in for each customer, or store the defaults in the orders table itself, adding a column to distinguish between default and actual values. Both of these approaches would allow you to easily fetch the default values into %ROWTYPE on a per-customer basis, if your ETL is really sufficiently complex to warrant row-by-row processing.

                                  I'm already having a table where i've declared general default-values for orders of any customer.

                                  By this solution (https://forums.oracle.com/thread/2556071) i'm able to transfer those defaults into ROWTYPE-var.

                                   

                                  Finally the point in my approach is:

                                  1. I'm having a "source"-table where the mapping-layer (pyhton-based) deliveres the order data.

                                  2. Depending on the delivered data we need to observe some erp-based rules for order-creating; part of the "logic"

                                  3. So we need to check if:

                                  1. Is there data in column xy the "source"-table? -> Use this source / value
                                  2. Is there a default-value in column xy for this specific customer provided? -> Use this source / value
                                  3. No initial source or no customer-based default declared? -> Use general default

                                   

                                  As you suggested i could create another table order_defaults which is configurable for each customer.

                                   

                                  In the end i need a ROWTYPE-var containing the values as described above.

                                  • 14. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
                                    seggione

                                    Based on the idea of padders, i think (hope) i'm able to solve this issue.

                                     

                                    But, isn't there any way to get the default-values out of this mentioned "ruletable"?

                                     

                                    Something like this:

                                     

                                    DECLARE  
                                      vTABLE1 TABLE1 % ROWTYPE;  
                                    BEGIN  
                                      FOR i IN (SELECT * FROM TBRULES WHERE CUSTOMER_ID = 2 AND TABLE = 'TABLE1')  
                                      LOOP     
                                      --here i need to assign the values of i.COLUMN into vTABLE1."equal_column_name"
                                        vTABLE1.i.COLUMN := i.VALUE;  
                                      END LOOP;  
                                    END; 
                                    

                                     

                                    *Sigh*

                                    1 2 Previous Next