Oracle PL/SQL Programming: A Developer's Workbook

Chapter 13. Native Dynamic SQL

Beginner

Q:
13-1.	Yes, that’s right! Just two new statements: EXECUTE IMMEDIATE SQL_string USING OPEN cursor_variable FOR query_string USING You use EXECUTE IMMEDIATE to, well, immediately execute the specified string with the bind variables specified in the USING clause. You use the OPEN FOR statement to open a multiple-row query. After that, use standard cursor variable FETCH operations to retrieve the data.
Q:
13-2.	They can’t make it much easier than this: BEGIN EXECUTE IMMEDIATE 'drop table employee'; END; /
Q:
13-3.	Here is a suggested procedure: CREATE OR REPLACE PROCEDURE drop_table (tab IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'drop table ' || tab; END; /
Q:
13-4.	Here is a suggested function: /* Filename on web page: tabcount81.sf */ CREATE OR REPLACE FUNCTION tabcount (nm IN VARCHAR2) RETURN PLS_INTEGER IS retval PLS_INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || nm INTO retval; RETURN retval; END; /