Dynamic PL/SQL | Oracle DBA Tutorial pdf

Dynamic PL/SQL

A sample is given below
CREATE TABLE TEST.EMP(NO NUMBER, NAME VARCHAR2(100));
CREATE OR REPLACE PROCEDURE DynSql
(pNo IN test.emp.no%TYPE,
pName IN test.emp.NAME%TYPE)
AS
cur INTEGER;
p INTEGER;
stmt VARCHAR2(100);
BEGIN
cur := dbms_sql.open_cursor;
stmt := 'INSERT INTO TEST.EMP VALUES(:no,:name)';
dbms_sql.parse(cur,stmt,dbms_sql.v7);
dbms_sql.bind_variable(cur,':no',pNo);
dbms_sql.bind_variable(cur,':name',pName);
p := dbms_sql.EXECUTE(cur);
COMMIT;
dbms_output.put_line(p);
dbms_sql.close_cursor(cur);
END;
Run the procedure from SQL Plus as EXEC DYNSQL(1,'Saikat');
It is also possible to execute dynamic SQL using EXECUTE IMMEDATE as shown below.
EXECUTE IMMEDIATE(‘INSERT INTO MY_TABLE SELECT * FROM THAT_TABLE’);