A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
Using dynamic SQL in a procedure
Question:- 'Why would I want to use dynamic SQL in a procedure?"
Answer :- Simply because there are some database commands that you just can't put into a procedure, for instance truncate or drop table.
One way of getting around this is to use the DBMS_SQL tools as in the example below which you can type into SQL*PLUS :-
create or replace procedure trunctab as
cursor_name := dbms_sql.open_cursor;
dbms_sql.parse(cursor_name, 'TRUNCATE TABLE XYZ' ,DBMS_SQL.V7);
nr_rows := dbms_sql.execute(cursor_name);
when others then
show error procedure trunctab