Oracle Built-in Packages

DBMS_SQL Examples

This section contains extended examples of using the DBMS_SQL package.

A Generic Drop_Object Procedure

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