Oracle PL / SQL Dynamic SQL is not that dynamic?
I am trying to write a generic data transformation routine that is table driven, but runs into a fundamental data binding problem. I am stuck on how to reference data in a column in order to link or build a dynamic SQL statement. Specifically, I am using rowtype as my data store. 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 am facing is an easy way, even in the DBMS_SQL package, to retrieve any row data by name. Whereas Oracle seems to be trying to add a lot of dynamic functionality like ANYDATA, ANYTYPE, Piplined, but none of them can find that allows this simple data manipulation. In particular, "Oracle Dynamic SQL Method 4" does not seem feasible in PL / SQL, where the binding data comes from any column data in a table row. Of course, you can bind to the "hardcoded" column name, but then it is no longer dynamic.
Also, the fact that I have to write multiple UpdateByColumn methods for each table because I cannot pass ONE rowtype to a generic method that will accept any rowtype is another limitation. I need to use rowtype because the data was preprogrammed by the caller and possibly some data changed.
Or am I missing something?
source to share
I think %ROWTYPE
there is a dead end here. As far as I know, there is a way to extract useful metadata about a PL / SQL variable.
But it's different if you can use an abstract data type (ADT or "object"). It is more powerful and similar to %ROWTYPE
. But this is not very convenient and will make your source code a little more complex. You must pre-define the objects and use them in your SQL.
For example, replace your code with the following:
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's okay, 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
exists only in PL / SQL, it is not possible to pass or convert these values. But you can store the entry as a package variable and then pass the name and type of that variable to your function. A function can refer to a record using dynamic PL / SQL and then can convert it to a value to be used by SQL.
(This does not apply to the problem of changing multiple columns at the same time; this is just a demo of a dynamic use case %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;
/
source to share