Re: PL/SQL Question

Venkat wrote
>I need to update certain tables, whose names are stored
>in another table, from a stored procedure.

Indeed, like Steve suggested, take a look at dbms_sql. Two examples below. Simply create a varchar2 holding your statement and call execDML, like:

  for r in my_cursor
  loop

      v_RowCount := execDML
          ( 'update ' || r.table_name
          || ' set my_date = sysdate'
          || ' where my_column = ''hello'' '
          );

Arjan.

• -----------------------------------------------------------------

  ---

• execDML executes any DML statement, like
• update or delete. Returns the number of
• affected rows.
• -----------------------------------------------------------------

  ---

  function execDML( p_Statement in varchar2) return number is v_CursorID integer; v_Count integer; begin v_CursorID := dbms_sql.open_cursor; dbms_sql.parse( v_CursorID, rtrim( p_Statement, ';') , dbms_sql.native ); v_Count := dbms_sql.execute( v_CursorID); dbms_sql.close_cursor( v_CursorID); return v_Count; exception when others then if dbms_sql.is_open( v_CursorID) then dbms_sql.close_cursor( v_CursorID); end if; raise_application_error( -20001, 'execDML: error executing ' || substr( p_Statement, 1, 40) || '<...>' , true ); end execDML;
• -----------------------------------------------------------------

  ---

• execQuery executes any query that results in one
• number, like count(*) or sum(..).
• -----------------------------------------------------------------

  ---

  function execQuery( p_Statement in varchar2) return number is v_CursorID integer; v_Result integer; v_RowCount integer; begin v_CursorID := dbms_sql.open_cursor; dbms_sql.parse( v_CursorID, rtrim( p_Statement, ';') , dbms_sql.native ); dbms_sql.define_column( v_CursorID, 1, v_Result); v_RowCount := dbms_sql.execute_and_fetch( v_CursorID, true); dbms_sql.column_value( v_CursorId, 1, v_Result); dbms_sql.close_cursor( v_CursorID); return v_Result; exception when others then if dbms_sql.is_open( v_CursorID) then dbms_sql.close_cursor( v_CursorID); end if; raise_application_error( -20001, 'execQuery: error executing ' || substr( p_Statement, 1, 40) || '<...>' , true ); end execQuery;
• -----------------------------------------------------------------

  ---