Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamically assign values to different ROWTYPE-Columns by ruletable

seggioneJul 31 2013 — edited Aug 7 2013

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é

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 4 2013
Added on Jul 31 2013
15 comments
3,246 views