CODEWITHSUNDEEP
plsqlexecute-immediate
Backslash36
Backslash36

Reputation: 805

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

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

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;

Upvotes: 6

Views: 4542

Answers (3)

Pluto
Pluto

Reputation: 3026

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;

Upvotes: 0

Backslash36
Backslash36

Reputation: 805

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

Upvotes: 4

hmmftg
hmmftg

Reputation: 1754

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.

Upvotes: 1

Related Questions