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?

It seems like a record of %rowtype is not the datatype to use, but I can't tell without knowing more of what you are trying to do and why. Maybe an assosiative array would be more suitable: type <name> is table of <type> index by varchar2(30); — llayland
– llayland, Commented Mar 19, 2012 at 2:48
Eventually that's what I came up with as well. I will pay the price of loading from a rowtype all data into an associative array of ANYDATA and pass the array into a common routine and be able to refer to column by name. — dreamfly
– dreamfly, Commented Mar 19, 2012 at 15:51

Jon Heller · Accepted Answer · 2021-12-22 17:23:48Z

4

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

edited Dec 22, 2021 at 17:23

answered Mar 19, 2012 at 5:22

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dreamfly Over a year ago

What would the common update routine's parameter list? Wouldn't you also need to "hard code" the specific type? e.g. Create or replace Procedure UpdateByColumn(rec tbl_type, colName varchar2) is... Thus each type would require a separate similar looking routine?

Jon Heller Over a year ago

You're right. I updated the code to use ANYDATA, so only one procedure is needed. The new code is based on: oracle-developer.net/display.php?id=218#1c1f6

dreamfly Over a year ago

The 'using p_anydata' will also be a problem because the number of columns to update may vary. Thus binding is not possible in native dynamic sql. My final implementation consists of 2 arrays, one is an associative array of ANYDATA where I loaded all data from a rowtype. The other is a list of columns to update. The associative is indexed by column Names so I can refer to the actual data by column name. This way I can even do binding using DBMS_SQL. It's rather disappointing that there's still no way to refer to a rowtype data by literal like rowdata('colname') where rowdata is %rowtype

Collectives™ on Stack Overflow

Oracle PL/SQL Dynamic SQL not so dynamic?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related