 |
Additional Training Downloads
Code from Steven's Oracle PL/SQL Programming Book |
This zip file contains all the code that is referenced in Oracle PL/SQL Programming, 4th edition (and earlier editions, as well).
The exec_ddl_from_file program will execute the contents of your file as a DDL statement. This means that you may not use any bind variables, nor may you return information from the statement that is executed dynamically. But you can use this program to read in a file (using UTL_FILE) that contains, say, the CREATE OR REPLACE PACKAGE definition of a package, and create that package. Hopefully you will find such a utility handy, but I wrote it mostly to demonstrate how you can use DBMS_SQL.PARSE to execute arbitrarily large dynamic SQL strings. EXECUTE IMMEDIATE of Native Dynamic SQL fame is limited to 32K characters. Not so with DBMS_SQL. So here are the files in the download:
exec_ddl_from_file.sql - creates a procedure that executes the contents of a file. You will need to have UTL_FILE configured to get this program to run properly.
exec_ddl_from_file2.sql - creates a procedure that executes the contents of a file and includes the logic required to verify that you are indeed running under invoker rights.You will need to have UTL_FILE configured to get this program to run properly.
invdefinv.sql - a script that demonstrates how when a definer rights program calls an invoker rights program, the current user for that invoker rights program is set to the owner of the definer rights program. It also shows the usefulness of the DBMS_UTILITY.FORMAT_CALL_STACK, which reveals the PL/SQL call stack.
Is element found in collection?
Oracle Database 10g allows you to use MEMBER OF syntax to determine if a particular values is a "member of" a nested table. Here is an example:
DECLARE
TYPE clientele IS TABLE OF VARCHAR2 (64);
client_list_12 clientele :=
clientele ('Customer 1', 'Customer 2');
BEGIN
IF 'Customer 1' MEMBER OF client_list_12
THEN
DBMS_OUTPUT.put_line ('Customer 1 is in the 12 list');
END IF;
IF 'Customer 3' NOT MEMBER OF client_list_12
THEN
DBMS_OUTPUT.put_line ('Customer 3 is not in the 12 list');
END IF;
END;
/
For the rest of you, I have generated a function that works with DBMS_SQL.VARCHAR2S. You can replace references to this type with your own collection type, and then use it in your own environment. This download also includes test scripts (for utPLSQL and native PL/SQL execution) to verify that the program works correctly.
Dynamic SQL Method 4 implementation of "SELECT * FROM table"
The intab procedure in this download provides a "SELECT * FROM " capability from within PL/SQL. That is, you provide the name of the table, an optional where clause and column name filter, and the "in table" procedure displays the contents of the table matching your criteria. The primary intention of this program unit to provide an example and template for writing code that implements Method 4 Dynamic SQL with the DBMS_SQL package. Method 4 means that at the time you are writing your code, you don't know either (a) how many columns you are querying or (b) how many bind variables are in the dynamic string. This level of uncertainty makes the code you write much more challengingly generic. For this download, since the table name is a variable, we don't know the number (and types) of columns that will be queried at runtime. The intab_dbms_sql.sp file shows how to implement a dynamic "SELECT * FROM " using DBMS_SQL. While this supplied package is no longer the first choice for most dynamic SQL requirements, it is the only mechanism available in PL/SQL to implement method 4 dynamic SQL. In fact, the very aspect of DBMS_SQL that makes it unattractive (the low-level API requires us to manually code each step of the process) fits perfectly with the degree of flexibility needed for method 4.
Use the intab.tst script to compile this program, create a test table, and display its contents using intab with a variety of argument values to exercise intab functionality.
Note: the intab procedure has a number of restrictions, documented in the program header, which you will want to address if you intend to use this code in a production environment or increase its flexibility.