Oracle PL/SQL Dynamic SQL not so dynamic?

Question

I am trying to write a generic data transformation routine that is table driven but run into a fundamental issue of binding data. I am stuck on how to refer to data in column in order to bind or construct a dynamic SQL statement. In particular, I am using rowtype as a data storage. Simplified Example:

Create or replace Procedure UpdateByColumn(rec tbl%rowtype, colName varchar2) is
  Sqlstmt varchar2(1000);
Begin
  -- this won't work because can't refer to data by column name
  Sqlstmt := 'update tbl set ' || colName || '=' || rec(colName);

  -- this will work but no longer dynamic
  Sqlstmt := 'update tbl set ' || colName || '=' || rec.MSRP;
End;

The actual problem I have is there is no easy way, even in DBMS_SQL package, to extract any row data by name. While Oracle seems to try to add a lot of dynamic features such as ANYDATA, ANYTYPE, Piplined functions but none I can find that allows this kind of simple data manipulation. In particular, “Oracle Dynamic SQL Method 4” seems to not be doable in PL/SQL where the bind data comes from any column data in a row of a table. Sure you can bind to a “hard coded” column name but then it’s no longer dynamic.

In addition, the fact that I have to write multiple UpdateByColumn method for each table because I can’t pass the rowtype to ONE generic method that will accept any rowtype is another limitation. I need to use rowtype because the data has been pre-fetched by the caller and possibly changed some of the data.

Or there is something I missed?

Answer 1

I think %ROWTYPE is a dead-end here. As far as I know, there is no way to extract useful metadata about a PL/SQL variable.

But things are different if you can use an abstract data type (ADT, or "object"). It is more powerful, and kind of similar to %ROWTYPE. But it is not quite as convenient, and will make your initial code a little more complicated. You will have to pre-define the objects, and use them in your SQL.

For example, replace code like this:

declare
    v_test tbl%rowtype;
begin
    select * into v_test from tbl;
end;
/

with this:

declare
    v_test2 tbl_type;
begin
    select tbl_type(msrp, some_other_column) into v_test2 from tbl;
end;
/

If that is acceptable, you can use dynamic PL/SQL for your updates:

--Create table, ADT, and test data
create table tbl(MSRP varchar2(100), some_other_column varchar2(100));

create or replace type tbl_type as object
(
    msrp varchar2(100),
    some_other_column varchar2(100)
);
/
insert into tbl values('1', '1');

--Convert object to ANYDATA, process with dynamic PL/SQL
declare
    my_tbl tbl_type := tbl_type('2', '3');

    procedure UpdateByColumn(p_anydata in anydata, colName in varchar2) is
        v_typename varchar2(30) := p_anydata.getTypeName;
    begin
        execute immediate '
            declare
                v_anydata2 anydata := :anydata;
                v_object '||v_typename||';
                v_dummy pls_integer;
            begin
                v_dummy := v_anydata2.getObject(v_object);
                update tbl set '||colName||' = v_object.'||colName||';
            end;
        ' using p_anydata;
    end;
begin
    updateByColumn(anyData.convertObject(my_tbl), 'MSRP');
end;
/

--Show the new data
select * from tbl;

MSRP    SOME_OTHER_COLUMN
----    -----------------
2       1

UPDATE

%ROWTYPE only exists in PL/SQL, there is no way to pass or convert those values. But you can store the record as a package variable, and then pass the name and type of that variable to your function. The function can refer to the record using dynamic PL/SQL, and then can convert it to a value to be used by the SQL.

(This does not address the issue of changing multiple columns at once, it is just a demo showing a way to dynamically use a %ROWTYPE.)

--Create table and test data
create table tbl(MSRP varchar2(100), some_other_column varchar2(100));
insert into tbl values('1', '1');
commit;

--Create another table, tbl2, that will be used to update tbl
--(The tables in this example have similar columns, but that is not 
--actually necessary.)
create table tbl2(MSRP varchar2(100), some_other_column varchar2(100));
insert into tbl2 values('2', '2');
commit;

--New function works by passing in names of global variables and 
--their types, instead of actual values.
create or replace procedure UpdateByColumn(
    p_package_and_variable_name in varchar2,
    p_rowtype in varchar2,
    colName in varchar2) is
begin
    execute immediate '
        declare
            v_rec '||p_rowtype||' := '||p_package_and_variable_name||';
        begin
            update tbl set '||colName||' = v_rec.'||colName||';
        end;
    ';
end;
/

--A test package that calls the function to update tbl.
create or replace package test_package is
    tbl2_rec tbl2%rowtype;
    procedure test_procedure;
end;
/

create or replace package body test_package is
    procedure test_procedure is
    begin
        select * into tbl2_rec from tbl2;
        UpdateByColumn('test_package.tbl2_rec', 'tbl2%rowtype', 'MSRP');
    end;
end;
/

begin
    test_package.test_procedure;
end;
/