[Chapter 19] 19.4 PLVdyn: A Code Layer over DBMS_SQL Chapter 19PLVdyn and PLVfk: Dynamic SQL and PL/SQL 19.4 PLVdyn: A Code Layer over DBMS_SQLThe PLVdyn (PL/Vision DYNamic SQL) package offers an easy-to-use, programmatic interface, or API, to the DBMS_SQL builtin package. It combines many basic operations in DBMS_SQL into functions and procedures, freeing up developers from having to know about many of the intricacies of using the builtin package.Operations include:Execute a Data Definition Language (DDL) statement with one line of codeExecute a PL/SQL block with one line of codeDelete from or truncate the specified tableDrop one or more objects from the data dictionaryExecute an INSERT...SELECT FROM statement with an absolute minimum of SQL codingView the contents of the specified tableToggle on/off tracing that displays the dynamic SQL statement being parsedThe following sections show how to use each of the different elements of the PLVdyn package.19.4.1 DDL Operations The PLVdyn package offers several programmatic interfaces to DDL or Data Definition Language commands. Before DBMS_SQL was available, you could not execute any DDL statements (such as CREATE TABLE, CREATE INDEX, etc.) within PL/SQL. Now DBMS_SQL lets you execute anything you want -- as long as you have the appropriate authority.DDL statements in SQL are handled differently from DML (Data Manipulation Language) statements such as UPDATE, INSERT, and so on. There is no need to execute a DDL statement. The simple act of parsing DDL automatically executes it and performs a COMMIT. This is true in SQL*Plus and it is true in PL/SQL programs. If you execute a DDL statement in your PL/SQL program, it commits all outstanding changes in your session. This may or may not be acceptable, so factor it into your use of the following PLVdyn programs.NOTE: To dynamically execute DDL from within PL/Vision, the account in which PLVdyn is installed must have the appropriate privilege granted to it explicitly. If you rely solely on role-based privileges, you receive an ORA-01031 error: insufficient privileges. For example, if you want to create tables from within PLVdyn, you need to have CREATE TABLE privilege granted to the PLVdyn account. 19.4.1.1 Generic DDL interfaceFirst of all, PLVdyn offers a single, completely generic procedure to execute a DDL statement. Its header follows:PROCEDURE ddl (string_in IN VARCHAR2);You pass the string to PLVdyn.ddl and it takes care of the details (the implementation of which is discussed in the section called "Bundling Common Operations").I can use the ddl procedure to perform any kind of DDL, as the following examples illustrate.Create an index on the emp table.PLVdyn.ddl ('CREATE INDEX empsal ON emp (sal)');Create or replace a procedure called temp.PLVdyn.ddl ('CREATE OR REPLACE PROCEDURE temp ' || 'IS BEGIN NULL; END;');PLVdyn offers a number of other, more specialized programs to execute various kinds of DDL. These are described in the following sections.19.4.1.2 Dropping and truncating objects with PLVdynPLVdyn offers two separate "cleanup" programs: drop_object and truncate. The drop_object procedure provides a powerful, flexible interface for dropping one or many objects in your schema. The header for drop_object is:PROCEDURE drop_object (type_in IN VARCHAR2, name_in IN VARCHAR2, schema_in IN VARCHAR2 := USER);The truncate command truncates either tables or clusters and has the same interface as drop_object:PROCEDURE truncate (type_in IN VARCHAR2, name_in IN VARCHAR2, schema_in IN VARCHAR2 := USER);The rest of this section describes the behavior and flexibility of drop_object. The same information applies to truncate. You provide the type of object to drop, the name of the object, and the schema, (if you do want to drop objects in another schema). So instead of typing statements like this in SQL*Plus:SQL> drop table emp;you can now use PLVdyn and enter this instead:SQL> exec PLVdyn.drop_object ('table', 'emp');If I were an aggressive and desperate salesperson, I would try to convince you that my way (with PLVdyn) is better than the "native" DROP TABLE statement. That is, however, totally foolish. This is a case where the simple DDL statement is much more straightforward. So why did I bother writing the drop_object procedure? Because when I wrote it, I didn't plan simply to match the capabilities of a single DDL statement. Instead, I examined the way in which DBAs often need to drop and manipulate objects in a schema and I discovered a way to leverage PL/SQL to provide added value when it came to dropping objects.19.4.1.3 Adding value with PL/SQLIn many of the accounts in which I have worked, an application abbreviation is prefixed onto the names of all objects (tables, views, programs, synonyms, etc.) for the application. The inventory system would use the INV abbreviation, for instance, to segregate by name all related objects. The main inventory table is named INV_master, the line items table named INV_item, and the package to maintain the invoices named INV_maint. A common action taken in such environments is to drop all objects for a particular application or to clean out all of the stored procedures or tables or views. I may want to clear the inventory application from my test schema so that I can move the next release over from development. Without dynamic SQL in PL/SQL, you would have to use SQL to generate SQL, reading rows from the USER_OBJECTS table to create a series of DROP statements, and then execute those statements in SQL*Plus. With DBMS_SQL and the PLVdyn package, you no longer have to take such a convoluted path to get the job done. Both the type and name arguments of the drop_object procedure can be wildcarded, and this gives you a tremendous amount of flexibility. I can drop all the objects with the inventory prefix as follows:SQL> exec PLVdyn.drop_object ('%', 'INV%');I can also request that all tables in the SCOTT schema be dropped with this command (it will, of course, only work if the owner of PLVdyn has the authority to drop objects in the SCOTT schema):SQL> exec PLVdyn.drop_object ('table', '%', 'scott');You can provide the same kinds of wildcarded arguments to truncate. If you specify "%" for the object type, for instance, truncate automatically applies the truncate command only to objects of type TABLE or CLUSTER. 19.4.1.4 Implementing multiobject actionsThe implementation of drop_object and truncate is interesting; both of these programs simply call a private module called multiobj, which stands for "multiple objects." This procedure applies the specified command to all the objects in the ALL_OBJECTS view that match the type and name provided. The multiobj procedure itself is a combination of static and dynamic SQL, as shown below:PROCEDURE multiobj (action_in IN VARCHAR2, type_in IN VARCHAR2, name_in IN VARCHAR2, schema_in IN VARCHAR2 := USER) IS /* The static cursor retrieving all matching objects */ CURSOR obj_cur IS SELECT object_name, object_type FROM all_objects WHERE object_name LIKE UPPER (name_in) AND object_type LIKE UPPER (type_in) AND (UPPER (action_in) != c_truncate OR (UPPER (action_in) = c_truncate AND object_type IN ('TABLE', 'CLUSTER'))) AND owner = UPPER (schema_in); BEGIN /* For each matching object ... */ FOR obj_rec IN obj_cur LOOP /* Open and parse the drop statement. */ ddl (action_in || ' ' || obj_rec.object_type || ' ' || UPPER (schema_in) || '.' || obj_rec.object_name); END LOOP; END;The first argument to multiobj is the action desired. The rest of the arguments specify one or more objects upon which the action is to be applied.The static cursor fetches all records from the ALL_OBJECTS data dictionary view that match the criteria. The dynamic cursor is defined and executed inside the generic PLVdyn.ddl procedure.Why, my readers may be asking, did I not use the PLVobj package to fetch from the ALL_OBJECTS view? The whole point of that package was to allow me to avoid making direct references to that view; instead I could rely on a programmatic interface and very high-level operators. Using PLVobj.loopexec, I could theoretically implement multiobj with a single statement, in which I execute PLVdyn.ddl as a dynamically constructed PL/SQL program.Believe me, I really wanted to use PLVobj in this program. The problem I encountered is that PLVobj is not sufficiently flexible. As you can see in the declaration section of multiobj, my cursor against ALL_OBJECTS is somewhat specialized. I have very particular logic inserted that automatically filters out objects that are not appropriate for TRUNCATE operations. There was no way for me to incorporate this logic into PLVobj as it currently exists. I do plan, however, that a future version of PLVobj will utilize dynamic SQL and then allow me to modify the WHERE clause (and even which view it works against: USER_OBJECTS, ALL_OBJECTS, or DBA_OBJECTS). In the meantime, I write my own, somewhat redundant cursor against ALL_OBJECTS and then construct the appropriate DDL statement based on the incoming action and values from the row fetched from ALL_OBJECTS.19.4.1.5 Generating sequence numbersThe nextseq function returns the nth next value from the specified Oracle sequence. Its header is as follows:FUNCTION nextseq (seq_in IN VARCHAR2, increment_in IN INTEGER := 1) RETURN INTEGER;The default value for the increment is 1, so by default you get the immediate next value from the sequence with a call like this::emp.empno := PLVdyn.nextseq ('emp_seq');You can also use nextseq to move your sequence forward by an arbitrary number of values. This is often necessary when the sequence has somehow gotten out of sync with the data. To move the emp_seq sequence ahead by 1000 values, simply execute this statement:SQL> exec PLVdyn.nextseq.('emp_seq', 1000);Why did I bother building nextseq ? The Oracle database offers a very powerful method for generating unique sequential numbers: the sequence generator. This database object guarantees uniqueness of values and comes in very handy when you need to get the next primary key value for INSERTs. One drawback of the sequence generator is that you can only obtain the next value in the sequence by referencing the sequence object from within a SQL statement. The following SELECT statement, for example, is like the one often used within PRE-INSERT triggers in Oracle Forms applications:SELECT emp_seq.NEXTVAL INTO :emp.empno FROM dual;In other words, you must make this artificial query from the dual table to obtain the sequence number to then use inside the Oracle Forms application. And suppose that you want to move the sequence forward by 1000 (this kind of requirement arises when, for one reason or another, the sequence has gotten out of sync with the table's primary key). You would then have to write and execute a FOR along these lines:DECLARE dummy INTEGER; BEGIN FOR val IN 1 .. 1000 LOOP SELECT emp_seq.NEXTVAL INTO dummy FROM dual; END LOOP; END; /I don't know about you, but the year is 1996 and I just don't think I should have to execute queries against dual to get things done. That makes me feel like a dummy! I also believe that Oracle Corporation will come to its senses eventually and allow you to obtain sequence numbers without going to the SQL layer. In the meantime, however, PLVdyn offers a programmatic interface to any sequence so that you can at least hide the fact that you are using dual to get your sequence number. You even get to hide the specific syntax of sequence generation (the NEXTVAL keyword, for instance), which will make it easier for anyone from novices to developers to utilize this technology.19.4.1.6 What about the overhead?Of course, since you are executing dynamic SQL, it takes more time to generate the sequence with nextseq compared with a static generation. My tests showed that, on average, the static generation approach (using a direct call to the sequence NEXTVAL through a dummy SQL query) took .55 seconds for 100 increments of the sequence. The dynamic approach using PLVdyn.nextseq, on the other hand, required 1.54 seconds for 100 increments of the sequence. There are two ways of looking at these results:Cup half empty: The dynamic sequence generation is three times slower than static! How awful!Cup half full: Dynamic generation of a single sequence value took only .0154 seconds on average. Unless I am working in a high-transaction, subsecond kind of environment, this is a totally acceptable level of performance.Is your cup half empty or half full? It depends on your specific application situation. As a general rule, you should carefully evaluate your use of dynamic SQL to make sure that you can afford the overhead. When your application can absorb the extra CPU cycles (and your users can tolerate the difference in response time), a program like PLVdyn.nextseq offers many advantages. 19.4.1.7 Compiling source code with PLVdynWhen you CREATE OR REPLACE a PL/SQL program unit in SQL*Plus, you are executing a DDL command. You can, consequently, issue that same command using DBMS_SQL -- the difference is that you can construct, create, and compile the PL/SQL program dynamically. PLVdyn offers two versions of the compile procedure precisely to allow you to take this action. The headers of the compile programs are as shown:PROCEDURE compile (stg_in IN VARCHAR2, show_err_in IN VARCHAR2 := PLV.noshow); PROCEDURE compile (table_in IN PLVtab.vc2000_table, lines_in IN INTEGER, show_err_in IN VARCHAR2 := PLV.noshow);The second version of compile assumes that the program definition is in a PL/SQL table in which each row starting from 1 and going to lines_in rows contains a sequential line of code. This procedure simply concatenates all of the lines together and passes them to the first version of compile.The string version of cor takes two arguments: stg_in, a string of up to 32,767 characters that contains the definition of the program, and show_err_in, which indicates whether you want to call PLVvu.err after compilation to check for compile errors. The program definition should start with the program unit type (PROCEDURE, FUNCTION, PACKAGE, or PACKAGE BODY). Do not append a CREATE OR REPLACE to the string; compile does this automatically. You also should not include a final / after the END; statement. This syntax is necessary only when executing the CREATE OR REPLACE directly in SQL*Plus. You can, on the other hand, include newline characters in your string.Here is an example of using PLVdyn.compile to create a very simple procedure with an error. I also request that PLVdyn show me the compile errors.SQL> exec PLVdyn.compile('procedure temp is begin nul; end;',PLV.show); -------------------------------------------------------------------- PL/Vision Error Listing for PROCEDURE TEMP -------------------------------------------------------------------- Line# Source -------------------------------------------------------------------- 1 procedure temp is begin nul; end; ERR * PLS-00313: 'NUL' not declared in this scopeHow would you use PLVdyn.compile? You might try building yourself a Windows-based frontend for PL/SQL development. For example, find a really good programming editor that has a macro language and the ability to execute dynamic link libraries (DLLs). Create a DLL that accepts a string and executes PLVdyn.compile. Tie this in to the editor and you are on your way to dramatically improving your PL/SQL development environment.NOTE: To dynamically compile and create stored PL/SQL code from within PL/Vision, the account in which PLVdyn is installed must be directly granted CREATE PROCEDURE authority. If you rely solely on role-based privileges, you receive an ORA-01031 error: insufficient privileges.You might also use PLVdyn.compile to temporarily create program units for use in your application, and then drop them at the end of your session. You could, for example, create a package dynamically that would define some global data structures whose names are established dynamically. They could only be referenced through dynamic PL/SQL as well, but PLVdyn does make this possible.19.4.1.8 Implementing the compile procedureThe implementation of the string-based compile procedure is a good example of how I am able to leverage existing elements of PL/Vision to easily extend the functionality of my library. The body of compile is shown below:PROCEDURE compile (stg_in IN VARCHAR2, show_err_in IN VARCHAR2 := PLV.noshow) IS v_name1 PLV.plsql_identifier%TYPE; v_name2 PLV.plsql_identifier%TYPE; BEGIN ddl ('CREATE OR REPLACE ' || stg_in); IF show_err_in = PLV.show THEN v_name1 := PLVprs.nth_atomic (stg_in, 1, PLVprs.c_word); v_name2 := PLVprs.nth_atomic (stg_in, 2, PLVprs.c_word); IF UPPER(v_name1||' '||v_name2) = 'PACKAGE BODY' THEN v_name1 := v_name1 || ' ' || v_name2; v_name2 := PLVprs.nth_atomic (stg_in, 3, PLVprs.c_word); END IF; PLVvu.err (v_name1 || ':' || v_name2); END IF; END;Notice, first of all, that it relies on the ddl procedure to execute the program. Then compile checks to see if you wanted to view compile errors. If so, it uses the nth_atomic function of PLVprs to extract the first two words from the program definition. If a PACKAGE BODY, it then retrieves the third word, which is the name of the package. Finally, it calls PLVvu.err to display any errors.19.3 The Dynamic Packages of PL/Vision19.5 DML Operations Copyright (c) 2000 O'Reilly & Associates. All rights reserved.