O'Reilly logo

Oracle Built-in Packages by John Beresniewicz, Charles Dye, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required