I could easily do this, but it would be a disservice to you and your database.

APEX (the software used to build asktom here for example) is the same as you describe - but it doesn't need to resort to "tricks" like this.


You have no datatypes - you only have strings if you do this.
You'll be doing dynamic SQL all over the place.
You could be subject to SQL Injection pretty easily.

You'd need to pass more than a tablename, you need a way to FIND the row.

What about lost updates??? Aren't you worried about that?

Generic code is "cool"
Generic code is generally pretty poor performing code, non-scalable code.


Here is an approach, I don't like it (can you tell by the name :)

I've started saying for the last couple of years - if you are doing dynamic sql and not binding (cannot bind identifiers for example), then you need to submit your code to at least 5 people that do not like you and have them try to break it with sql injection.

I spent a couple of minutes thinking about how this could be injected - and the trouble spots would be in the identifiers - the tablename, primary key name, and column names. I'm using a query against user_tab_columns to validate that the tablename and primary key column name are "valid" (we'll get no_data_found and the routine will bail if they do not exist). I'm using dbms_assert to verify that the column names are valid to be used as identifiers (doesn't mean they are column names - just that they could be column names - and hence are not rouge SQL bits)

Hey everyone, try to break it (please)

ops$tkyte%ORA9IR2> create or replace type Array as table of varchar2(4000)
  2  /

Type created.

ops$tkyte%ORA9IR2> create or replace context my_ctx using bad_idea
  2  /

Context created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace
  2  function bad_idea
  3  ( p_tname in varchar2,
  4    p_pkname in varchar2,
  5    p_pkval in varchar2,
  6    p_cnames   in array,
  7    p_new_data in array
  8  )
  9  return number
 10  as
 11     l_data_type varchar2(30);
 12     l_stmt      long;
 13  begin
 14     select decode( data_type, 'DATE', 'TO_DATE(', 'NUMBER', 'TO_NUMBER(', '(' )
 15       into l_data_type
 16       from user_tab_columns
 17      where table_name = p_tname
 18        and column_name = p_pkname;
 19
 20     l_stmt := 'update ' || p_tname || ' set ';
 21     for i in 1 .. p_cnames.count
 22     loop
 23        l_stmt := l_stmt || ' ' ||
 24                  dbms_assert.SIMPLE_SQL_NAME( p_cnames(i) ) ||
 25                '=sys_context(''my_ctx'',''' || p_cnames(i) || ''' ),';
 26        dbms_session.set_context( 'my_ctx', p_cnames(i), p_new_data(i) );
 27     end loop;
 28     l_stmt := rtrim(l_stmt,',') || ' where ' ||
 29                dbms_assert.SIMPLE_SQL_NAME( p_pkname ) ||
 30                '=' || l_data_type || 'sys_context(''my_ctx'',''' || p_pkname || ''' ))';
 31     dbms_session.set_context( 'my_ctx', p_pkname, p_pkval );
 32
 33     dbms_output.put_line( l_stmt );
 34     execute immediate l_stmt;
 35     return sql%rowcount;
 36  end;
 37  /

Function created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table t ( x int primary key, y varchar2(4), z date );

Table created.

ops$tkyte%ORA9IR2> insert into t values ( 1, 'x', sysdate );

1 row created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> alter session set nls_date_format = 'yyyymmddhh24miss';

Session altered.

ops$tkyte%ORA9IR2> select * from t;

         X Y    Z
---------- ---- --------------
         1 x    20070719141152

ops$tkyte%ORA9IR2> declare
  2     l_new_data array := array('y',to_char(sysdate+1/24,'yyyymmddhh24miss') );
  3  begin
  4     dbms_output.put_line( bad_idea( 'T', 'X', 1, array('y','z'), l_new_data ) );
  5  end;
  6  /
update T set  y=sys_context('my_ctx','y' ), z=sys_context('my_ctx','z' ) where
X=TO_NUMBER(sys_context('my_ctx','X' ))
1

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> select * from t;

         X Y    Z
---------- ---- --------------
         1 y    20070719151152