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;
/

Intermediate

Q:

13-5.

Here is the enhanced table count function:

 /* Filename on web page: tabcount81-2.sf */ CREATE OR REPLACE FUNCTION tabcount ( nm IN VARCHAR2, whr IN VARCHAR2 := NULL) RETURN PLS_INTEGER IS retval PLS_INTEGER; v_where VARCHAR2(2000) := whr; BEGIN IF UPPER (whr) NOT LIKE 'WHERE %' THEN v_where := 'WHERE ' || v_where; END IF; EXECUTE IMMEDIATE ...

Get Oracle PL/SQL Programming: A Developer's Workbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.