Oracle PL/SQL Programming: A Developer's Workbook

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 'SELECT COUNT(*) FROM ' || nm || ' ' || v_where INTO retval; RETURN retval; END; /
Q:
13-6.	Just construct the appropriate DDL statement, and the deed is done: /* Filename on web page: dropit.sp */ CREATE OR REPLACE PROCEDURE dropit ( ittype IN VARCHAR2, itname IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'drop ' || ittype || ' ' || itname; END; /
Q:
13-7.	You cannot use placeholders for schema elements of the dynamic SQL statement. What is a schema element? It’s the name of an element in your schema: table, column, view, object type, etc. If you have variable schema elements, you must use concatenation to incorporate that variable name into the SQL string.
Q:
13-8.	Here is a generic procedure: /* Filename on web page: runddl81.sp */ CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2) AUTHID CURRENT_USER IS BEGIN EXECUTE IMMEDIATE ddl_in; ...