%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.

Answer 2

Have a look at DBMS_SQL Here is an example:

DECLARE
    YourTable VARCHAR2(30) := 'MY_TABLE';
    cur SYS_FERCURSOR;
    curid NUMBER;
    desctab DBMS_SQL.DESC_TAB;
    colcnt NUMBER; -- total number of columns
    res NUMBER;

    namevar VARCHAR2(4000);
    numvar NUMBER;
    datevar DATE;
    ... more if needed
BEGIN

    OPEN cur FOR 'SELECT * FROM '||YourTable;               
    curid := DBMS_SQL.TO_CURSOR_NUMBER (cur);

    DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
    FOR i IN 1..colcnt LOOP -- loop over all columns
        IF desctab(i).col_type = 1 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, namevar);
        ELSIF desctab(i).col_type = 2 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
        ELSIF desctab(i).col_type = 12 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
            .......
        ELSE
            DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 25);
        END IF;         
    END LOOP;
    -- Fetch Rows
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
        FOR i IN 1 .. colcnt LOOP
           IF (desctab(i).col_type = 1) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
           ELSIF (desctab(i).col_type = 2) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
           ELSIF (desctab(i).col_type = 12) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
              ....
          END IF;
          --> do here something else with namevar or numvar or datevar or ...
       END LOOP;
    END LOOP;    
    DBMS_SQL.CLOSE_CURSOR(curid);

END;

But as the other replies already mentioned, check carefully if you really need this "full" dynamic. You did not tell us what you like to do with retunrned variable values. Perhaps you don't need them explicitly. You can also create complex statements dynamically and run them.

See here a simple example (well, not that simple) I made some time ago. It updates several tables with nested tables of different object type without any variable usage.

CREATE OR REPLACE PROCEDURE UpdateNestedTables IS

    sqlstr VARCHAR2(1000);

    CURSOR NestedTables IS
    SELECT POCL_TABLE_NAME, FTC_NT_TABLE_NAME, PAR_COLUMN_NAME,
        FTC_MO_STRUCT_CONSTRUCTOR AS NT_COLUMN_NAME,
        FTC_MO_STRUCT_TYPE AS NT_OBJ_TYPE
    FROM T_PAR_OBJ_CLASSES
        JOIN T_PARAMETERS ON PAR_POCL_ID = POCL_ID
        JOIN T_FDN_TABLE_COLUMNS ON FTC_PAR_ID = PAR_ID;

BEGIN

    FOR aTab IN NestedTables LOOP
        sqlstr := 'UPDATE '
            || '(SELECT /*+ USE_HASH(a b) */ '||aTab.PAR_COLUMN_NAME||', FULL_DISTINCT_NAME '
            ||'  FROM '||SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')||'.'||aTab.POCL_TABLE_NAME||' a '
            ||'  WHERE '||aTab.PAR_COLUMN_NAME||' IS NULL '
            ||'   AND FULL_DISTINCT_NAME =ANY '
            ||'    (SELECT FULL_DISTINCT_NAME FROM '||SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')||'.'||aTab.FTC_NT_TABLE_NAME||' b)) wt '
            || 'SET ('||aTab.PAR_COLUMN_NAME||') = '
            || ' (WITH t AS ('
            || '    SELECT m.FULL_DISTINCT_NAME,'
            || '      CAST(MULTISET('
            || '        SELECT '||aTab.NT_COLUMN_NAME
            || '        FROM '||SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')||'.'||aTab.FTC_NT_TABLE_NAME||' nt '
            || '        WHERE nt.FULL_DISTINCT_NAME = m.FULL_DISTINCT_NAME)'
            || '      AS '||aTab.NT_OBJ_TYPE||') AS MO_REF'
            || '    FROM '||SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')||'.'||aTab.POCL_TABLE_NAME||' m'
            || '    GROUP BY FULL_DISTINCT_NAME)'
            || '  SELECT MO_REF '
            || '  FROM t '
            || '  WHERE t.FULL_DISTINCT_NAME = wt.FULL_DISTINCT_NAME)';
        EXECUTE IMMEDIATE sqlstr;
    END LOOP;

END UpdateNestedTables;
/

Answer 3

I can recommend to use object types and object tables/views based on that types. Use can define root type and inherit all subtypes from it. Use can pass variable of root type into/from your generic proc and cast it to you specific type using treat inside dynamic SQL. You can pass SQL object types in/out to execute immediate as well.