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