This section contains extended examples of using the DBMS_SQL package.
The dynamic SQL of DBMS_SQL allows you to create completely generic modules to manipulate objects in the Oracle7 Server. You can, for instance, write a procedure that drops the specified table, but you can also create a module that will drop whatever kind of object you specify, as shown in this first version of drop_object:
CREATE OR REPLACE PROCEDURE drop_object (type_in IN VARCHAR2, name_in IN VARCHAR2) IS /* Declare and create a cursor to use for the dynamic SQL */ cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; fdbk PLS_INTEGER; BEGIN /* Construct the SQL statement, parse it and execute it. */ DBMS_SQL.PARSE (cur, 'DROP ' || type_in || ' ' || name_in, DBMS_SQL.NATIVE); fdbk := DBMS_SQL.EXECUTE (cur); DBMS_SQL.CLOSE_CURSOR (cur); END; /
Well, that was straightforward enough. But how useful is it? Sure, it lets me execute DDL in PL/SQL, which wasn’t possible before. But assuming that I have written this procedure as part of a broader interface to manage database objects from a screen, it is fairly limited. It is, in fact, simply equivalent to a DROP OBJECT statement. Boooring. Why not utilize the flexibility of the PL/SQL language to provide additional productivity, above and beyond the “straight” DDL? Wouldn’t it be nice to, for example, drop all packages with names like “STR%” or drop all objects of any type in a schema with a single command?
To implement these kinds ...