PROCEDURE execute_dml_dynamically(
p_table_name IN VARCHAR2
, p_where_column IN VARCHAR2
, p_where_column_val IN INTEGER
, p_upd_col_name IN VARCHAR2
, p_set_value IN VARCHAR2)
IS
l_cursor PLS_INTEGER := DBMS_SQL.open_cursor;
l_execute PLS_INTEGER;
BEGIN
DBMS_SQL.parse(l_cursor
, 'BEGIN update '
|| p_table_name
|| ' set '
|| p_upd_col_name
|| ' = '
|| p_set_value
|| ' where '
|| p_where_column
|| ' = '
|| p_where_column_val
|| '; END;'
, DBMS_SQL.native);
l_execute := DBMS_SQL.execute( l_cursor);
IF l_execute > 0
THEN
DBMS_OUTPUT.put_line( 'Value of '
|| p_upd_col_name
|| ' updated to '
|| p_set_value);
ELSE
DBMS_OUTPUT.put_line( 'Update of '
|| p_upd_col_name
|| ' to '
|| p_set_value
|| ' failed.');
END IF;
DBMS_SQL.close_cursor( l_cursor);
END;
/
p_table_name IN VARCHAR2
, p_where_column IN VARCHAR2
, p_where_column_val IN INTEGER
, p_upd_col_name IN VARCHAR2
, p_set_value IN VARCHAR2)
IS
l_cursor PLS_INTEGER := DBMS_SQL.open_cursor;
l_execute PLS_INTEGER;
BEGIN
DBMS_SQL.parse(l_cursor
, 'BEGIN update '
|| p_table_name
|| ' set '
|| p_upd_col_name
|| ' = '
|| p_set_value
|| ' where '
|| p_where_column
|| ' = '
|| p_where_column_val
|| '; END;'
, DBMS_SQL.native);
l_execute := DBMS_SQL.execute( l_cursor);
IF l_execute > 0
THEN
DBMS_OUTPUT.put_line( 'Value of '
|| p_upd_col_name
|| ' updated to '
|| p_set_value);
ELSE
DBMS_OUTPUT.put_line( 'Update of '
|| p_upd_col_name
|| ' to '
|| p_set_value
|| ' failed.');
END IF;
DBMS_SQL.close_cursor( l_cursor);
END;
/
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.