Creating a Stored Program

To build your own stored PL/SQL program, you use one of SQL'sCREATE statements. For example, if you want to create a stored function named wordcount that counts words in a string, you can do so using a CREATE FUNCTION statement:

CREATE FUNCTION wordcount (str IN VARCHAR2) RETURN PLS_INTEGERAS declare local variables go hereBEGIN implement algorithm hereEND;/

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 database administrator has granted you Oracle's CREATE PROCEDURE privilege, this statement will cause 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 of the reasons that Oracle provides the OR REPLACE OPTION, which you will want to use probably 99% of the time.

CREATE OR REPLACEFUNCTION wordcount (str IN VARCHAR2) RETURN PLS_INTEGERAS same as before

The OR REPLACE option avoids the side effects of dropping and re-creating the program; in other words, it preserves any object privileges you have granted to other users or roles. Fortunately, it only replaces 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 this example shows, SQL*Plus does not, by default, echo the contents of scripts. You can, however, 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 ONSQL> @wordcount.funSQL> 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>

We get a "warning" message that is completely inadequate for debugging the problem; you will want to look at the full text of the error message. The quickest way to do this is with the SHOW ERRORS command, abbreviated as SHOW ERR:

SQL> SHOW ERRErrors for FUNCTION WORDCOUNT: LINE/COL ERROR-------- ----------------------------------------------14/13 PLS-00201: identifier 'WORDS' must be declared14/13 PL/SQL: Statement ignored21/4 PL/SQL: Statement ignored21/11 PLS-00201: identifier 'WORDS' must be declared

(Why SQL*Plus makes you show errors as a second step, I don't know.) The compiler has detected both occurrences of the variable, reporting the exact line and column numbers. Behind the scenes, SHOW ERRORS is really just querying Oracle's USER_ERRORS view in the data dictionary. To see more detail about any server-based error, you can look it up by its identifier—PLS-00201 in this case—in Oracle's Database Error Messages document.

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 ERRORScategory object For example, to view the latest errors for the wordcount procedure, specify: SQL> SHOW ERRORS PROCEDURE 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 in Oracle9i it includes the following: DIMENSIONFUNCTIONJAVA SOURCEJAVA CLASSPACKAGEPACKAGE BODYPROCEDURETRIGGERTYPETYPE BODYVIEW

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" file template for building stored programs in SQL*Plus might begin with the following form:

CREATE OR REPLACE program-typeAS your code/ 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.