CODEWITHSUNDEEP
oracle-databasevariablesplsqlrecord
user1808751
user1808751

Reputation:

Oracle PL/SQL Variable Record Field Names

I have a problem with dynamic field names in a cursor. It's a bit difficult to explain, but I'll try...

There are 3 tables:

  1. T_IN: I don't know anything about this table yet. No structure, no field names, simply nothing.

  2. T_OUT: this is the the target table

  3. T_KONF: This table has 2 columns:

    C_IN VarChar2: this column contains the field names of the table T_IN

    C_OUT VarChar2: this column contains the field names of the table T_OUT

The table T_KONF tells me, what data from wich field of the table T_IN has to go in wich field of the table T_OUT.

Now i have a record

MyRec C_OUT%ROWTYPE;

and a variable with the fieldname in it

field_name VarChar2(15) := 'PRODUCT';

How can I set this field of the record to a value? Hardcoded it would look like this:

MyRec.PRODUCT := value;

But is there a way to do that dynamically?

MyRec.[field_name] := value; -- ???

I hope that was specific enough... Thanks

Upvotes: 3

Views: 3360

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

You can't assign a record field dynamically.

You could use an INDEX-BY PL/SQL Table (associative array) but its type has to be known in advance. You could use a dynamic RECORD containing all majors types and you could decide at run-time which field to use (VARCHAR2, DATE...) but that would be rather tedious.

Instead I suggest you use dynamic SQL since you know at run-time all column names and we can suppose that the column types are compatible.

Something like this should work (11gR2):

-- SETUP
-- CREATE TABLE T_KONF(C_IN VARCHAR2(30), C_OUT VARCHAR2(30));
-- INSERT INTO T_KONF VALUES ('C1', 'C2');
-- INSERT INTO T_KONF VALUES ('C2', 'C3');

SQL> DECLARE
  2     l_sql LONG;
  3  BEGIN
  4     SELECT 'INSERT INTO t_out (' ||
  5               listagg(dbms_assert.simple_sql_name('"'||t_konf.c_out||'"'),
  6                      ', ') WITHIN GROUP (ORDER BY t_konf.rowid)
  7            || ' )
  8             SELECT ' ||
  9               listagg(dbms_assert.simple_sql_name('"'||t_konf.c_in||'"'),
 10                       ', ') WITHIN GROUP (ORDER BY t_konf.rowid)
 11            || '
 12               FROM t_in
 13              WHERE 1 = 1' -- custom where clause
 14       INTO l_sql
 15       FROM t_konf;
 16     DBMS_OUTPUT.put_line(l_sql);
 17     -- EXECUTE IMMEDIATE (l_sql); -- uncomment when SQL is OK
 18  END;
 19  /

INSERT INTO t_out ("C3", "C2" )
           SELECT "C2", "C1"
             FROM t_in
            WHERE 1 = 1

Upvotes: 1

Related Questions