​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
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 integer;
    nr_rows integer;
begin
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name, 'TRUNCATE TABLE XYZ' ,DBMS_SQL.V7);
    nr_rows := dbms_sql.execute(cursor_name);

    exception
    when others then
    raise_application_error(-20001,'ERROR');

end trunctab;
/
show error procedure trunctab
exit