Oracle Magazine - March/April 2011

Note that I call the hello_message function as part of a PL/SQL statement (in this case, an assignment of a string to a variable). The hello_message function returns a string, so it can be used in place of a string in any executable statement.

I can also return to my hello_place procedure and replace the code used to build the string with a call to the function:

CREATE OR REPLACE PROCEDURE
hello_place (place_in IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line
(hello_message (place_in));

END hello_place;

I can also call the function from within a SQL statement. In the following block, I insert the message into a database table:

BEGIN
INSERT INTO message_table (message_
date, MESSAGE_TEXT)

VALUES (SYSDATE, hello_place ('Chicago'));

END;

Although the “hello place” logic is very simple, it demonstrates the power of assigning names to one or more executable statements (an algorithm) and then referencing that algorithm simply by specifying the name and any required parameters.

Named PL/SQL blocks make it possible to construct complex applications that can be understood and maintained with relative ease.

ABOUT NAMES IN ORACLE DATABASE

Now that you can see the importance of assigning names to logic, it is time to talk about the rules for names (or, to be more precise, identifiers) in both PL/SQL and, more generally, Oracle Database.

Here are the rules for constructing valid identifiers in Oracle Database: The maximum length is 30 characters. •

DOWNLOAD Oracle Database 11g

bit.ly/fherki MARCH/APRIL 2011 ORACLE.COM/ORACLEMAGAZINE

The first character must be a letter, but • each character after the first can be either a letter, a numeral (0 through 9), a dollar sign ($), an underscore (_), or a number sign (#). All of the following are valid identifiers: hello_world hello$world hello#world but these are invalid:

1hello_world
hello%world
PL/SQL is case- •;insensitive with regard to
identifiers. PL/SQL treats all of the fol-
lowing as the same identifier:
hello_world
Hello_World
HELLO_WORLD
To offer you increased flexibility, Oracle
Database lets you bypass the restrictions
of the second and third rules by enclosing
your identifier within double quotes. A
quoted identifier can contain any sequence
of printable characters excluding double
quotes; differences in case will also be pre-
served. So all of the following strings are
valid and distinct identifiers:
"Abc"
"ABC"
"a b c"

You will rarely encounter quoted identifiers in PL/SQL code; some development groups use them to conform to their naming conventions or because they find the mixed-case strings easier to read.

These same rules apply to the names of database objects such as tables, views, and procedures, with one additional rule: unless you put double quotation marks around the names of those database objects, Oracle Database will store them as uppercase.

So when I create a procedure as follows:

CREATE OR REPLACE PROCEDURE
hello_world
IS
BEGIN
DBMS_OUTPUT.put_line
('Hello World!');

END hello_world;

Oracle Database stores this procedure under the name HELLO_WORLD.

In the following block, I call this procedure three times, and although the name looks different in all the calls, they all execute the same procedure:

BEGIN hello_world; HELLO_WORLD; "HELLO_WORLD";

END;

Oracle Database will not, on the other hand, be able to run my procedure if I call it as follows:

BEGIN

"hello_world"; END;

It will look inside the database for a procedure named hello_world rather than HELLO_WORLD.

If you don’t want your subprogram names to be stored as uppercase, precede and follow that name with double quotation marks when you create the subprogram:

CREATE OR REPLACE PROCEDURE "Hello_World"

IS
BEGIN
DBMS_OUTPUT.put_line
('Hello World!');

END "Hello_World";

RUNNING SQL INSIDE PL/SQL BLOCKS PL/SQL is a database programming language. Almost all the programs you will ever write in PL/SQL will read from or write to—or read from and write to—Oracle Database by using SQL. Although this series assumes a working knowledge of SQL, you should be aware of the way you call SQL statements from within a PL/SQL block.

And here’s some very good news: Oracle Database makes it very easy to write and run SQL statements in PL/SQL. For the most part, you simply write the SQL statement directly in your PL/SQL block and then add the code needed to interface between the SQL statement and the PL/SQL code.