2.2.2 Executing a Stored Program

We've already looked at two different ways to invoke a stored program: one is to wrap it in a simple PL/SQL block, and another is to use the SQL*Plus EXECUTE command. You can also use stored programs inside other stored programs. For example, you can use a function like wordcount any place that 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 bill, while logged in to any schema that has appropriate authorization:

SELECT bill.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 its 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 isDESCRIBE.

SQL> DESCRIBE wordcount

FUNCTION wordcount RETURNS BINARY_INTEGER

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

STR VARCHAR2 IN

For the complete source code of your stored programs, queryUSER_SOURCE or TRIGGER_SOURCE. (Querying from these data dictionary views is discussed in further detail in Chapter 19.)