Chapter 13. Native Dynamic SQL
Beginner
Q: | |
13-1. | Yes, that’s right! Just two new statements: EXECUTE IMMEDIATE 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; / |
Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.