Assign value to a field of rowtype where `field name` is a string

Question

I want to assign a value to a rowtype's field but I don't know how to do it.

Suppose that I have a table X inside my database.

Suppose also that I have the following variables

a ( X%ROWTYPE ), representing a row of the table X
b ( VARCHAR2 ), containing a column name of the table X
c ( VARCHAR2 ), containing what I want to store inside a.b

What I want to do : something like a.b := c.

I've come up with something like this :

EXECUTE IMMEDIATE 'SELECT '|| c || ' INTO a.' || b || ' FROM DUAL';

Apparently, this isn't the right way to go. I get a ORA-0095: missing keyword error.

Can anyone help me with this ?

Here is the complete code :

DECLARE
    tRow            MyTable%ROWTYPE;
    col_name        VARCHAR(10) := 'Length';
    nValue          NUMBER(12,4) := 0.001;
    dynamic_request VARCHAR(300);
BEGIN 
    dynamic_request := 'SELECT '|| nValue || ' INTO tRow.' || col_name || ' FROM DUAL';
    EXECUTE IMMEDIATE dynamic_request;
END;

Why are you trying to use execute immediate? That's not going to work. — OldProgrammer, Feb 06 '14 at 16:09
Please provide more context on what you are trying to do. Where is the rest of your PL/SQL code? — OldProgrammer, Feb 06 '14 at 16:23
Sorry, still not sure what you are trying to do. Just use, "trow.length := nValue;" What is the purpose of doing an assignment to a variable dynamically? you already have the Right hand side value? Good luck. — OldProgrammer, Feb 06 '14 at 16:49
Actually, I have no idea about the value of `col_name`. Think of it as if the first three declared variables are in fact arguments of a procedure. — Backslash36, Feb 06 '14 at 16:56

Backslash36 · Accepted Answer · 2014-02-07T12:55:23.470

Ok, I solved it !

Short answer : Using a global variable does the trick

Answer Development

Let us consider two facts about dynamic PL/SQL blocks (i.e., PL/SQL blocks written as strings, to be executed trough an EXECUTE IMMEDIATE statement)

[1] There is no such thing as variable scope when you create a dynamic PLSQL block. What I mean by that is, if you do something like this :

CREATE OR REPLACE PROCEDURE DynamicVariableAssignment(
   theString IN VARCHAR2
 ) 
IS
BEGIN 
   EXECUTE IMMEDIATE 'BEGIN theString := ''test''; END; ';
END;

it will simply not work because the scope of theString is not transfered to the dynamic PL/SQL block. In other words, the dynamic PL/SQL block doesn't "inherit" of any variable, wherever it is executed.

[2] You might say "OK, no panic, I can give input/output arguments to my dynamic PL/SQL block, right ?". Sure you can, but guess what : you can only give SQL types as in/out ! True PL/SQL types on the other hand, such as a myTable%rowtype, are not accepted as an input for a dynamic PL/SQL block. So the answer of hmmftg won't work either :

-- I've reduced the code to the interesting part
dynamic_request := 'BEGIN :t_row.' || col_name || ':= 0.001; END;';
EXECUTE IMMEDIATE dynamic_request USING IN OUT tRow;
-- (where tRow is of type myTable%ROWTYPE)

since tRow is of MyTable%ROWTYPE, it is not a valid SQL type and is therefore not valid as an input to the dynamic PL/SQL block.

The Solution Who would have thought that global variables would come and save the day ? As we said in [1], we have no reference to any variable outside the dynamic PL/SQL block. BUT we can still access global variables defined in package headers !

Let us assume that I have a package kingPackage in which I define the following :

tempVariable  myTable%ROWTYPE;

Then I can do this :

FINAL CODE (body only)

-- Copy tRow into temp variable
kingPackage.tempVariable := tRow;

-- We modify the column of the temp variable
vString := 'BEGIN kingPackage.tempVariable.' || col_val || ' := ' || TO_CHAR(vNumber) ||'; END;'; 
EXECUTE IMMEDIATE vString;    

-- The column value has been updated \o/ 
tRow := kingPackage.tempVariable;

There you go, fellas ! Have a nice day

hmmftg · Answer 2 · 2014-02-07T02:50:44.373

1

try this:

CREATE OR REPLACE PROCEDURE ROW_CHANGER(
    tRow            IN MyTable%ROWTYPE,
    col_name        IN VARCHAR,
    nValue          IN NUMBER) 
   AS
    dynamic_request VARCHAR(300);
BEGIN 
    dynamic_request := 'BEGIN  :t_row.'||COL_NAME ||':= :n_value; END;';
    EXECUTE IMMEDIATE dynamic_request
         USING IN OUT  TROW, IN nValue;
END;

this is because in your EXECUTE IMMEDIATE the tRow MyTable%ROWTYPE is not defined,

so we defined it with using statement.

edited Feb 07 '14 at 02:50

answered Feb 07 '14 at 02:07

hmmftg

1,274
1
18
31

Please include explanation of what your code does and how it answers the question. If you get a code snippet as an answer, you may not know what to do with it. Answer should give the OP guidance on how to debug and fix their problem. Pointing out, what the idea behind your code is, greatly helps in understanding the issue and applying or modifying your solution. – Palec Feb 07 '14 at 02:41
I'm effrayed this doesn't work, I get an "PLS-00457: expressions have to be of SQL types" error – Backslash36 Feb 07 '14 at 10:23
Ok, in fact the `USING IN` arguments can only be of SQL type. Since `TROW` is a `ROWTYPE`, this technique is not accepted. – Backslash36 Feb 07 '14 at 10:44

score 0 · Answer 3 · answered Mar 11 '23 at 00:59

I found an option that doesn't need to use a package at all and can be executed from an anonymous block. You simply have to use a temporary variable to access the incoming record's fields from, and then assign the temporary variable back to the parameter. This at least works on 19c, and all original values are carried over.

Using your original example...

DECLARE
   v_row  MyTable%ROWTYPE;
   v_col  VARCHAR(10) := 'column2';
   v_val  VARCHAR(1)  := 'B';
   v_sql  VARCHAR(300);
BEGIN
   v_row.column1 := 'A';
   v_sql := 'DECLARE tmp MyTable%ROWTYPE := :0; BEGIN tmp.' || v_col || ' := :1; :0 := tmp; END;';
   EXECUTE IMMEDIATE v_sql using in out v_row, in v_val;

   dbms_output.put_line(v_row.column1); -- A
   dbms_output.put_line(v_row.column2); -- B
END;

Assign value to a field of rowtype where `field name` is a string

3 Answers3

FINAL CODE (body only)

Linked