Section 2.2.  Performing Essential PL/SQL Tasks

2.2. Performing Essential PL/SQL Tasks Let's turn to the highlights of creating , running, deleting, and otherwise managing PL/SQL programs, using SQL*Plus as the front end. Don't expect to be overwhelmed with detail here; treat this section as a glimpse of topics that will be covered in much greater detail in the chapters ahead. 2.2.1. Creating a Stored Program To build a new stored PL/SQL program, you use one of SQL's CREATE statements. For example, if you want to create a stored function that counts words in a string, you can do so using a CREATE FUNCTION statement : CREATE FUNCTION wordcount (str IN VARCHAR2) RETURN PLS_INTEGER AS declare local variables here BEGIN implement algorithm here END; / As with the simple BEGIN-END blocks shown earlier, running this statement from SQL*Plus requires a trailing slash on a line by itself. Assuming that the DBA has granted you Oracle's CREATE PROCEDURE privilege (which also gives you the privilege of creating functions), this statement causes Oracle to compile and store this stored function in your schema; if your code compiles, you'll probably see a success message such as: Function created. If a table or stored program named wordcount already exists in your Oracle schema, CREATE FUNCTION will fail with the error message ORA-00955: name is already used by an existing object. That is one reason that Oracle provides the OR REPLACE option, which you will want to use probably 99% of the time. CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2) RETURN PLS_INTEGER AS same as before The OR REPLACE option avoids the side effects of dropping and recreating the program; in other words, it preserves any object privileges you have granted to other users or roles. Fortunately, it replaces only objects of the same type, and it won't automatically drop a table named wordcount just because you decided to create a function by that name. As with anonymous blocks used more than once, programmers generally store these statements in files in the operating system. I could create a file wordcount.fun for this function and use the SQL*Plus @ command to run it: SQL> @wordcount.fun Function created. As mentioned earlier, SQL*Plus does not, by default, echo the contents of scripts. You can SET ECHO ON to see the source code scroll past the screen, including the line numbers that Oracle assigns; this setting can be helpful when troubleshooting. Let's introduce an error into the program by commenting out a variable declaration. SQL> /* File on web: wordcount.fun */ SQL> SET ECHO ON SQL> @wordcount.fun SQL> CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2) 2 RETURN PLS_INTEGER 3 AS 4 /* words PLS_INTEGER := 0; ***Commented out for intentional error*** */ 5 len PLS_INTEGER := NVL(LENGTH(str),0); 6 inside_a_word BOOLEAN; 7 BEGIN 8 FOR i IN 1..len + 1 9 LOOP 10 IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len 11 THEN 12 IF inside_a_word 13 THEN 14 words := words + 1; 15 inside_a_word := FALSE; 16 END IF; 17 ELSE 18 inside_a_word := TRUE; 19 END IF; 20 END LOOP; 21 RETURN words; 22 END; 23 / Warning: Function created with compilation errors. SQL> This message tells us that the function was created, but that there were compilation errors that render it inoperable. We've succeeded in storing the source code in the database; now we need to tease the details of the error out of the database. The quickest way to see the full text of the error message is to use SQL*Plus' SHOW ERRORS command, abbreviated as SHOW ERR: SQL> SHOW ERR Errors for FUNCTION WORDCOUNT: LINE/COL ERROR -------- ---------------------------------------------- 14/13 PLS-00201: identifier 'WORDS' must be declared 14/13 PL/SQL: Statement ignored 21/4 PL/SQL: Statement ignored 21/11 PLS-00201: identifier 'WORDS' must be declared The compiler has detected both occurrences of the variable, reporting the exact line and column numbers. To see more detail about any server-based error, you can look it up by its identifierPLS-00201 in this casein Oracle's Database Error Messages document. Behind the scenes, SHOW ERRORS is really just querying Oracle's USER_ERRORS view in the data dictionary. You can query that view yourself, but you generally don't need to (see the sidebar "Show Other Errors"). Show Other Errors Many Oracle programmers know only one form of the SQL*Plus command: SQL> SHOW ERRORS and they incorrectly believe that they must query the USER_ERRORS view directly to see anything but the error messages from the most recent compile. However, you can append to SHOW ERRORS an object category and a name, and it will display the latest errors for any object: SQL> SHOW ERRORS category [schema. ]object For example, to view the latest errors for the wordcount function, specify: SQL> SHOW ERRORS FUNCTION wordcount Use caution when interpreting the output: No errors. This message actually means one of three things: (1) the object did compile successfully; (2) you gave it the wrong category (for example, function instead of procedure); or (3) no object by that name exists. The complete list of categories this command recognizes varies by version, but includes the following: DIMENSION FUNCTION JAVA SOURCE JAVA CLASS PACKAGE PACKAGE BODY PROCEDURE TRIGGER TYPE TYPE BODY VIEW It's common practice to append a SHOW ERRORS command after every scripted CREATE statement that builds a stored PL/SQL program. So, a "good practices" template for building stored programs in SQL*Plus might begin with this form: CREATE OR REPLACE program-type AS your code END; / SHOW ERRORS (I don't usually include SET ECHO ON in scripts, but rather type it at the command line when needed.) When your program contains an error that the compiler can detect, CREATE will still cause Oracle to store the program in the database, though in an invalid state. If, however, you mistype part of the CREATE syntax, Oracle won't be able to figure out what you are trying to do and won't store the code in the database. 2.2.2. Executing a Stored Program We've already looked at two different ways to invoke a stored program: wrap it in a simple PL/SQL block or use the SQL*Plus EXECUTE command. You can also use stored programs inside other stored programs. For example, you can invoke a function such as wordcount in any location where you could use an integer expression. Here is a short illustration of how I might test the wordcount function with a strange input (CHR(9) is an ASCII "tab" character): BEGIN DBMS_OUTPUT.PUT_LINE('There are ' || wordcount(CHR(9)) || ' words in a tab'); END; / I have embedded wordcount as part of an expression and supplied it as an argument to DBMS_OUTPUT.PUT_LINE. Here, PL/SQL automatically casts the integer to a string so it can concatenate it with two other literal expressions; the result is: There are 0 words in a tab You can also invoke many PL/SQL functions inside SQL statements. Here are several examples of how you can use the wordcount function: Apply the function in a select list to compute the number of words in a table column: SELECT isbn, wordcount(description) FROM books; Use the ANSI-compliant CALL statement , binding the function output to a SQL*Plus variable, and display the result: VARIABLE words NUMBER CALL wordcount('some text') INTO :words; PRINT :words Same as above, but execute the function from a remote database as defined in the database link test.newyork.ora.com: CALL wordcount@test.newyork.ora.com('some text') INTO :words; Execute the function, owned by schema bob, while logged in to any schema that has appropriate authorization: SELECT bob.wordcount(description) FROM books WHERE id = 10007; 2.2.3. Showing Stored Programs Sooner or later you will want to get a list of the stored programs you own, and you may also need to view the most recent version of program source that Oracle has saved in its data dictionary. This is one task that you will find far easier if you use some kind of GUI-based navigation assistant (Chapter 1 mentions a few such tools), but if you lack such a tool, it's not too hard to write a few SQL statements that will pull the desired information out of the data dictionary. For example, to see a complete list of your programs (and tables, indexes, etc.), query the USER_OBJECTS view, as in: SELECT * FROM USER_OBJECTS; This view shows name, type, creation time, latest compile times, status (valid or invalid), and other useful information. If all you need is the summary of a PL/SQL program's callable interface in SQL*Plus, the easiest command to use is DESCRIBE: SQL> DESCRIBE wordcount FUNCTION wordcount RETURNS BINARY_INTEGER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- STR VARCHAR2 IN DESCRIBE also works on tables, views, object types, procedures, and packages. To see the complete source code of your stored programs, query USER_SOURCE or TRIGGER_SOURCE. (Querying from these data dictionary views is discussed in further detail in Chapter 20.) 2.2.4. Managing Grants and Synonyms for Stored Programs When you first create a PL/SQL program, normally no one but you or the DBA can execute it. To give another user the authority to execute your program, issue a GRANT statement : GRANT EXECUTE ON wordcount TO scott; To remove the privilege, use REVOKE: REVOKE EXECUTE ON wordcount FROM scott; You could also grant the EXECUTE privilege to a role: GRANT EXECUTE ON wordcount TO all_mis; Or, if appropriate, you could allow any Oracle user on the current database to run the program: GRANT EXECUTE ON wordcount TO PUBLIC; If you grant a privilege to an individual like Scott, and to a role of which the user is a member, and also grant it to PUBLIC, Oracle remembers all three grants until they are revoked. Any one of the grants is sufficient to permit the individual to run the program, so if you ever decide you don't want Scott to run it, you must revoke the privilege from Scott, and revoke it from PUBLIC, and finally revoke it from the all_mis role (or revoke that role from Scott). To view a list of privileges you have granted to other users and roles, you can query the USER_TAB_PRIVS_MADE data dictionary view. Somewhat counterintuitively, PL/SQL program names appear in the table_name column: SQL> SELECT table_name, grantee, privilege 2 FROM USER_TAB_PRIVS_MADE 3 WHERE table_name = 'WORDCOUNT'; TABLE_NAME GRANTEE PRIVILEGE ------------------------------ ------------------------------ ----------- WORDCOUNT PUBLIC EXECUTE WORDCOUNT SCOTT EXECUTE WORDCOUNT MIS_ALL EXECUTE When Scott does have the EXECUTE privilege on wordcount, he will probably want to create a synonym for the program to avoid having to prefix it with the name of the schema that owns it: SQL> CONNECT scott/tiger Connected. SQL> CREATE OR REPLACE SYNONYM wordcount FOR bob.wordcount; Now he can execute the program in his programs by referring only to the synonym: IF wordcount(localvariable) > 100 THEN... This is a good thing, because if the owner of the function changes, only the synonym (and not any stored program) needs modification. It's possible to create a synonym for a procedure, function, package, orin the most recent Oracle versionsuser-defined type. Synonyms for procedures, functions, or packages can hide not only the schema but also the actual database; you can create a synonym for remote programs as easily as local programs. However, synonyms can only hide schema and database identifiers; you cannot use a synonym in place of a packaged subprogram. Removing a synonym is easy: DROP SYNONYM wordcount; 2.2.5. Dropping a Stored Program If you really, truly don't need a particular stored program any more, you can drop it using SQL's DROP statement : DROP FUNCTION wordcount; You can drop a package, which can be composed of up two elements (a specification and body), in its entirety: DROP PACKAGE pkgname; Or you can drop only the body without invalidating the corresponding specification: DROP PACKAGE BODY pkgname; Any time you drop a program that other programs call, the callers will be marked invalid. 2.2.6. Hiding the Source Code of a Stored Program When you create a PL/SQL program as described above, the source code will be available in clear text in the data dictionary, and any DBA can view or even alter it. To protect trade secrets or to prevent tampering with your code, you might want some way to obfuscate your PL/SQL source code before delivering it. Oracle provides a command-line utility called wrap that converts many CREATE statements into a combination of plain text and hex. It's not true encryption, but it does go a long way toward hiding your code. Here are a few extracts from a wrapped file: CREATE OR REPLACE FUNCTION wordcount wrapped 0 abcd abcd ...snip... 1WORDS: 10: 1LEN: 1NVL: 1LENGTH: 1INSIDE_A_WORD: 1BOOLEAN: ...snip... a5 b 81 b0 a3 a0 1c 81 b0 91 51 a0 7e 51 a0 b4 2e 63 37 :4 a0 51 a5 b a5 b 7e 51 b4 2e :2 a0 7e b4 2e 52 10 :3 a0 7e 51 b4 2e d :2 a0 d b7 19 3c b7 :2 a0 d b7 :2 19 3c b7 a0 47 :2 a0 If you need true encryptionfor example, to deliver information such as a password that really needs to be secureyou should not rely on this facility.[*] [*] Oracle does provide a way of incorporating true encryption into your own applications using the built-in package DBMS_CRYPTO (or DBMS_OBFUSCATION_TOOLKIT in releases before Oracle Database 10g; see Chapter 22 for information on DBMS_CRYPTO. To learn more about the wrap utility, see Chapter 20.