StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle PL/SQL Dynamic SQL not so dynamic?
text
Body
copied!<p>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:</p> <pre><code>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; </code></pre> <p>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. </p> <p>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.</p> <p>Or there is something I missed?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload