%ROWTYPE variable from table name

Question

I've got an Oracle procedure, that I want to be somehow generic. I would like to:

1. pass a table name as a varchar parameter
2. use EXECUTE IMMEDIATE to dynamically select data
3. store the result in the %ROWTYPE variable of passed type

The third point seems to be a problem. I'm not sure if i can create a type dynamically inside the procedure body. It would be great to have something like this:

procedure CHANGE_GENERIC(tableName in VARCHAR2, someOldVal in integer,
                          someNewVal in integer) is
v_sql varchar2(200);

begin   

   v_sql := 'select * from ' || tableName || 'where ID = ' || someOldVal;
   EXECUTE IMMEDIATE v_sql1 into **myDynamicRowThatIDontHave**;

   -- some other code
end;

Answer 1

You probably can't do this (at least not usefully).

You could construct an entire anonymous PL/SQL block

v_plsql := 'DECLARE ' ||
           '  l_row ' || p_table_name || '%rowtype; ' ||
           'BEGIN ' ||
           '  SELECT * ' ||
           '    INTO l_row ' ||
           '    FROM ' || p_table_name ||
           '    WHERE id = ' || p_some_old_value || ';' ||
           ...
EXECUTE IMMEDIATE v_plsql;

In general, though, long before you start resorting to dynamic PL/SQL at runtime, you really want to take a step back and assess whether there isn't an easier solution to whatever problem you have. There are any number of frameworks, for example, that dynamically generate CRUD packages for each of your tables. That's using dynamic PL/SQL but it's only doing it once as part of a build rather than doing it every time you want to update data.