Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:
30-10.	A good way is to use dynamic SQL, as in these examples: /* DBMS_SQL version */ DECLARE v_curs PLS_INTEGER; v_dummy INT; BEGIN v_curs := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_curs,'ALTER SYSTEM SWITCH LOGFILE',DBMS_SQL.NATIVE); v_dummy := DBMS_SQL.EXECUTE(v_curs); DBMS_SQL.CLOSE_CURSOR(v_curs); END; /* Native Dynamic SQL (Oracle 8.1.5) version */ BEGIN EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE'; END;
Q:
30-11.	The built-in is DBMS_UTILITY.EXEC_DDL_STATEMENT. Here’s an example of its use: DBMS_UTILITY.EXEC_DDL_STATEMENT('ALTER TABLESPACE USERS COALESCE')
Q:
30-12.	Here’s the CURSOR statement: CURSOR cur_objects ( p_schema VARCHAR2 ) IS SELECT object_name , object_type FROM sys.dba_objects WHERE owner = p_schema AND object_type IN ('CLUSTER','TABLE') ORDER BY object_name ;
Q:
30-13.	And here’s a suggested procedure: /* Filename on web page: validate_structure.sp */ CREATE OR REPLACE PROCEDURE validate_structure ( i_schema IN VARCHAR2 ) IS v_sql VARCHAR2(2000); -- From previous example. CURSOR cur_objects ( p_schema VARCHAR2 ) IS SELECT object_name , object_type FROM sys.dba_objects WHERE owner = p_schema AND object_type IN ('CLUSTER','TABLE') ORDER BY object_name ; a---------------------------------------------------------------------- -- Main Logic -- Loop through the cursor to validate all objects. -- When one fails, the procedure terminates, and -- recovery is required for ...