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

Question

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

Suppose I have table X inside my database.

Suppose also that I have the following variables

• a( X%ROWTYPE) representing a row of table X
• b( VARCHAR2) containing the column name of table X
• c( VARCHAR2) containing what I want to keep inside ab

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

I came up with something like this:

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

This is apparently not the right way. I get message ORA-0095: missing keyword .

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;

Answer 1

, !

:

PL/SQL (.. PL/SQL, , EXECUTE IMMEDIATE)

[1] PLSQL , . , - :

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

, theString PL/SQL. , PL/SQL "" , .

[2] ", , / PL/SQL, ?". , , , : SQL, /! , True PL/SQL, myTable%rowtype, PL/SQL. , hmmftg :

-- 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)

tRow MyTable% ROWTYPE, SQL PL/SQL.

, ? [1], PL/SQL. , !

, kingPackage, :

tempVariable  myTable%ROWTYPE;

:

( )

-- 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;

, !

Answer 2

:

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;

, EXECUTE IMMEDIATE tRow MyTable%ROWTYPE ,

using.