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'' '
);
end loop;
commit;
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;
- -----------------------------------------------------------------
Received on Tue Dec 01 1998 - 00:00:00 CST
