In this chapter, as the title suggests, you’ll learn about the foundational structures of PL/SQL: blocks, functions, and procedures. Blocks define the structures upon which functions and procedures are built. Functions and procedures are very similar, but distinct, in that a function returns a value to a calling function or procedure, while a procedure does not. So let’s begin our discussion of PL/SQL by starting with its foundation: the block.

Blocks

The Procedure Language extension for SQL (PL/SQL) has a block structure. Every piece of code executes in a block, similar to Java, JavaScript, and PHP’s try-catch clause; Python’s try-except clause; or Ruby’s begin-rescue-end clause. However, PL/SQL has four keywords:

• DECLARE: Every PL/SQL block has a declaration section. This is where you allocate memory for cursors, data type definitions, variables, embedded functions, and procedures (don’t worry—you’ll learn about all of these in this book, beginning with functions and procedures in this chapter). Sometimes when you code a PL/SQL program, you won’t even use the declaration section, but it’s still there.
• BEGIN: Every PL/SQL block has an executable section. It starts with the keyword BEGIN. BEGIN marks the beginning of where you put your program logic. And every PL/SQL program must have at least one line of executable code, even if it’s the keyword NULL, which in this context means no operation.
• EXCEPTION: Every PL/SQL block has an exception-handling section. It starts with the keyword EXCEPTION. This is where you will catch any database or PL/SQL errors, or as they are affectionately known, exceptions. Like the declaration section, sometimes you won’t even use an exception-handling section, but it’s still there.
• END: Every PL/SQL block ends with the keyword END.

Anonymous Blocks

We’ll start with an anonymous PL/SQL block. It’s called anonymous because it’s not going to be saved in the database, so it will never have a name. In practice, you won’t find yourself using anonymous blocks in production, but you’ll use them throughout the development process. They’re perfect for creating test units that you’ll eventually move into your stored PL/SQL blocks. You’ll learn more about that in Chapter 8. For now, let’s look at an example.

Anonymous Block Example

Listing 2-1 is an example of an anonymous PL/SQL block. Comments describe the different sections.

Listing 2-1. An Anonymous PL/SQL Block, anonymous.sql

01  -- This is an anonymous procedure, so it has no name
02  DECLARE
03    /*
04     You declare local cursors, variables, and methods here,
05     but you don't need to have a declaration section.
06    */
07  BEGIN
08    -- You write your executable code here
09
10    NULL;  -- Ahhh, you've got to have at least one command!
11  EXCEPTION
12    when NO_DATA_FOUND then
13      raise_application_error(-20000,
14        'Hey, This is in the exception-handling section!'),
15  END;
16  /
17  -- the forward slash on a line by itself says execute this procedure

Let’s go through Listing 2-1 line by line:

• Line 1 is a single-line comment. Single-line comments start with a double-dash (--), but line 1 is not part of the PL/SQL block or program because it exists outside the block structure.
• Line 2 starts the block with the keyword DECLARE. This anonymous PL/SQL block could have started with the keyword BEGIN because it doesn’t have anything to declare.
• Lines 3 through 6 are a multiline comment in the declaration section. Multiline comments start with a slash and asterisk (/*) and end with an asterisk and slash (*/).
• Line 7 starts the PL/SQL executable section of the PL/SQL block with the keyword BEGIN.
• Line 8 is a single-line comment in the executable section.
• Line 10 is the PL/SQL keyword NULL followed by a single-line comment. NULL in this context means no operation. I put it there because every PL/SQL block must have at least one line of code, or it won’t compile. The NULL command is terminated with a semicolon. In PL/SQL, a semicolon denotes the end of a command.
• Line 11 starts the exception-handling section with the keyword EXCEPTION. If you code an exception-handling section, you must catch at least one exception. In this case, I’ve coded a NO_DATA_FOUND exception, which will raise an application error with my message should a NO_DATA_FOUND exception occur.
• Line 15 ends the PL/SQL block or program with the keyword END.
• Line 16 has a single forward slash (/), which is a signal to SQL*Plus to execute the PL/SQL block. In this case—that is, with an anonymous PL/SQL block—the Oracle database will compile and then run the code in the PL/SQL block.
• Line 17 is another single-line comment that, too, exists outside the PL/SQL block.

It’s Your Turn to Execute an Anonymous Block

Now it’s time for you to execute an anonymous PL/SQL block/program.

1. Open SQL*Plus and connect to the database with username RPS.
2. At the SQL> prompt, type the following:

   set serveroutput on size 1000000
   begin
     SYS.DBMS_OUTPUT.put_line('Hi there genius!'),
   end;
   /

After you type the forward slash on a line by itself, you will have executed your very first PL/SQL procedure, and a very complimentary one at that.

The first line tells SQL*Plus to echo the database’s output for your session to the screen after the PL/SQL procedure is finished executing. Note that set serveroutput on size 1000000 is a SQL*Plus command and is not part of the anonymous block.

The line that starts with begin starts your PL/SQL procedure’s executable code section. The next line calls the put_line procedure in package (library of stored procedures) DBMS_OUTPUT, owned by user SYS:

sys.dbms_output.put_line('Hi there genius!'),

Here, put_line stores your text in a buffer until the PL/SQL procedure ends. Then SQL*Plus displays the text from that buffer on the screen for you to see.

The fourth line, which starts with end, ends the anonymous PL/SQL procedure. Finally, as stated earlier, the forward slash (/) on a line by itself tells SQL*Plus to execute the procedure.

In this example, you didn’t code declaration or exception-handling sections, but they’re still there! If an error occurs between the BEGIN and END keywords, PL/SQL will use the default (invisible) exception handler to raise the error to the enclosing program unit, which, in this case, is SQL*Plus.

Exceptions

Exceptions allow you to catch errors as your PL/SQL program executes, so you have control over what happens in response to those errors. PL/SQL predefines more than 20 named exceptions, but you’ll probably use only a few.

Common Exceptions

As you’ll learn in future chapters, there are a handful of commonly seen exceptions. The rest occur only when there are catastrophic problems with the database, the network, or your PL/SQL code.

Two exceptions are very common:

• NO_DATA_FOUND: You’ll get a NO_DATA_FOUND exception anytime you code a SQL SELECT statement that does not return any rows.
• TOO_MANY_ROWS: If you code a SELECT statement where you expect only one row but you get more than one, you’ll get a TOO_MANY_ROWS exception.

Catching an Exception

You add the keyword EXCEPTION between the keywords BEGIN and END in order to add an exception-handling section to your PL/SQL block. Once you do that, any error that occurs between the keywords BEGIN and EXCEPTION will be handled by your exception-handling section.

Functions

A FUNCTION is a PL/SQL block or method that returns a value, so it can be used on the right-hand side of an assignment. Here is an example:

n_value := to_number('123.45'),

In this line of PL/SQL code, n_value is a numeric variable (we’ll cover variables in the next chapter). n_value is followed by an assignment operator, which is a colon followed by an equal sign (:=). Next is the PL/SQL built-in function to_number(text in varchar2), which parses a varchar2 data type, and then returns its numeric value—that is, if the varchar2 represents a number; otherwise, the function raises an INVALID_NUMBER exception.

Since a FUNCTION returns a value, you can also use it in a SQL statement, as in this example:

SQL> select to_number('A') from dual;
select to_number('A') from dual
           *
ERROR at line 1:
ORA-01722: invalid number

Look, there’s that INVALID_NUMBER exception I was talking about!

Create a Function

Instead of dealing with errors when we try to convert a varchar2 (character string) to a number on the fly as in a SELECT statement, let’s create an errorless to_number() function. Listing 2-2 is the DDL to do just that.

Listing 2-2. An Errorless to_number( ) Function, to_number_or_null.fun

01  CREATE OR REPLACE FUNCTION to_number_or_null (
02  aiv_number        IN     varchar2 )
03  return            number is
04  /*
05  to_number_or_null.fun
06  by Donald J. Bales on 2014-10-20
07  An errorless to_number( ) method
08  */
09  begin
10    return to_number(aiv_number);
11  exception
12    when OTHERS then
13      return NULL;
14  end to_number_or_null;
15  /
16  @fe.sql to_number_or_null;

The DDL syntax used in Listing 2-2 to create the function is as follows:

CREATE [OR REPLACE] FUNCTION <function_name> [(
<parameter_name_1>          [IN] [OUT] <parameter_data_type_1>,
<parameter_name_2>          [IN] [OUT] <parameter_data_type_2>,...
<parameter_name_N>          [IN] [OUT] <parameter_data_type_N> )]
RETURN                                 <return_data_type> IS
  --the declaration section
BEGIN
  -- the executable section
  return <return_data_type>;
EXCEPTION
  -- the exception-handling section
END;
/

where <function_name> is the name of the FUNCTION; <parameter_name> is the name of a parameter being passed IN, OUT, or IN and OUT; <parameter_data_type> is the PL/SQL data type of the corresponding parameter; and <return_data_type> is the PL/SQL data type of the value that will be returned by the FUNCTION when it completes its execution. The brackets ([]) around the keywords OR REPLACE denote that they are optional. The brackets around the parameters denote that they are optional, too.

The block structure of a FUNCTION is exactly the same as an anonymous procedure, except for the addition of the Data Definition Language (DDL) CREATE FUNCTION keywords, the optional parameters, and the RETURN clause.

Let’s take a look at Listing 2-2 line by line:

• Line 1 has the DDL keywords to CREATE a stored FUNCTION. These take the place of the keyword DECLARE used earlier in the anonymous PL/SQL block.
• Line 2 declares one parameter, aiv_number, a varchar2 value passed INto the FUNCTION.
• Line 3 contains the RETURN clause. In this case, I’m returning a number.
• Lines 4 through 8 contain a multiline comment that lists the function’s source code filename, the name of the author, the date the function was written, and a description of the function’s purpose. So the only thing in the declaration section of this PL/SQL block is the multiline comment.
• On line 9, the keyword BEGIN starts the execution section of the PL/SQL block.
• On line 10, I return the built-in to_number( )’s return value for converting the varchar2 variable aiv_number to a number. Then the program ends. However, if the built-in to_number( ) raises an exception, the program’s execution branches to the exception-handling section.
• On line 11, the keyword EXCEPTION starts the exception-handling section of this PL/SQL block.
• On line 12, the exception handler checks to see if the raised exception is the named exception OTHERS (a catch-all exception). If it is, it executes the code that follows it on line 13. It may have sufficed to use the execption INVALID_NUMBER; however, in practice, Oracle may define additional numeric conversion exceptions in the future, and consequently, my errorless routine might actually report an error!
• On line 13, I return NULL if there is an OTHERS, which includes INVALID_NUMBER, exception. That’s what makes this an errorless to_number( ) function. If there are any other exceptions, the exception hander will not raise the error to the enclosing PL/SQL block or program, which in this case, will be SQL*Plus.
• On line 14, the keyword END denotes the end of the PL/SQL block, and hence the FUNCTION.
• On line 15, the single slash character (/) tells SQL*Plus to execute the DDL, which stores the PL/SQL in the database and then compiles it.
• Line 16 contains a helper SQL script that will list any compilation errors. Most of the time, this script will simply have one line of code that says show errors. However, if you’re compiling into another schema (other than the username you’re logged in with), you may need a more complex SQL script to display errors.

Now let’s try using this errorless FUNCTION in a SELECT statement:

SQL> select to_number_or_null('A') from DUAL;

TO_NUMBER_OR_NULL('A')
----------------------

Ta da! It returned a NULL value since the letter A is not a number. Just in case you’re a skeptic, here’s a second test:

SQL> select to_number_or_null('234.56') from DUAL;

TO_NUMBER_OR_NULL('234.56')
---------------------------
                     234.56

Yes, indeed, it works correctly!

It’s Your Turn to Create a Function

For this exercise, you’ll create a function that returns a date value. You can use the function in Listing 2-2 as a model. This time, you’ll pass this new function a varchar2 value that represents a date in the form MM/DD/YYYY. Your function will parse the varchar2 value and return a date data type if the varchar2 value is actually a date in the form MM/DD/YYYY; otherwise, it will return NULL.

You can use the PL/SQL built-in function to_date(text in varchar2, format in varchar2) in your function to do the actual parsing of the date. In the following form, to_date( ) will return a date value if it successfully parses the passed varchar2 value, or raise an exception if the passed varchar2 value is not a date in the format MM/DD/YYYY:

return to_date(aiv_date, 'MM/DD/YYYY'),

Using this built-in function, create your function by following these steps.

1. Open your text editor, and code the DDL to create your function.
2. Save your new DDL script with the file name to_mmsddsyyyy_or_null.fun.
3. At your SQL*Plus prompt, type the at sign (@) followed by the name of your file in order to store and compile your new function.
4. Test your new function by using it in a SELECT statement against table DUAL.

Listing 2-3 shows my solution for this exercise.

Listing 2-3. An Errorless to_date( ) Function, to_mmsddsyyyy_or_null.fun

01  create or replace FUNCTION to_mmsddsyyyy_or_null (
02  aiv_date             in     varchar2 )
03  return                      date is
04  /*
05  to_mmsddsyyyy_or_null.fun
06  by Donald J. Bales on 2014-10-20
07  An errorless to_date( ) method
08  */
09  begin
10    return to_date(aiv_date, 'MM/DD/YYYY'),
11  exception
12    /*
13    There are too many possible errors, for example:
14      ORA-01830: date format picture ends before
15                 converting entire input string
16      ORA-01843: not a valid month
17      ORA-01847: day of month must be between 1
18                 and last day of month
19      ORA-01858: a non-numeric character was found
20                 where a numeric was expected
21    so I used the exception OTHERS
22    */
23    when OTHERS then
24      return NULL;
25  end to_mmsddsyyyy_or_null;
26  /
27  @fe.sql to_mmsddsyyyy_or_null;

First, I’ll test it using a SELECT statement against DUAL:

SQL> select to_mmsddsyyyy_or_null('A') from DUAL;

TO_MMSDDSYYYY_OR
----------------

Well, that worked. The to_mmsddsyyyy_or_null() function returned a NULL because the letter A is not a date in the form MM/DD/YYYY. So let’s try a date string. This time, however, I’ll execute the anonymous PL/SQL block in Listing 2-4 as a test unit in order to test the function.

Listing 2-4. A Test Unit for Function to_mmsddsyyyy_or_null( ), to_mmsddsyyyy_or_null.sql

01  rem to_mmsddsyyyy_or_null.sql
02  rem by Donald J. Bales on 2014-10-20
03  rem FUNCTION to_mmsddsyyyy_or_null() test unit
04
05  begin
06    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('01/01/1980'));
07    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('02/29/1980'));
08    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('02/29/1981'));
09    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('9/9/2006'));
10    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('9/9/9999'));
11    sys.dbms_output.put_line(to_mmsddsyyyy_or_null('1/1/4712 BC'));
12  end;
13  /

The following output from executing function to_mmsddsyyyy_or_null()’s test unit shows that it’s working great. The tests on lines 8 and 11 should have returned NULL because I passed invalid date strings, and they did!

SQL> @to_mmsddsyyyy_or_null.sql

01-JAN-80
29-FEB-80
09-SEP-06
09-SEP-99
PL/SQL procedure successfully completed.

Up to this point, you’ve seen an anonymous PL/SQL block and a function’s PL/SQL block. The differences between the two are that an anonymous block is not permanently stored in the database with a name, nor can it return a value. Now let’s take a look at PL/SQL blocks that do have names, but don’t return values: procedures.

Procedures

PL/SQL procedures don’t return a value. They just perform their instructions and return. Of course, this means that you can’t use procedures on the right-hand side of an assignment statement like a function.

Create a Procedure

Listing 2-5 is a very simple example of a procedure. It’s a wrapper around the Oracle SYS.DBMS_LOCK package’s procedure sleep(seconds in number). This procedure will stop executing (or sleep) without using many CPU cycles for the number of seconds specified. I’ll admit, it’s kind of lame, but we’re just starting out here, so I’m keeping things simple.

Listing 2-5. A Wrapper Procedure for SYS.DBMS_LOCK.sleep( ), wait.prc

01  CREATE OR REPLACE PROCEDURE wait(
02  ain_seconds       IN     number) is
03  /*
04  wait.prc
05  by Donald J. Bales on 2014-10-20
06  Wrapper for SYS.DBMS_LOCK.sleep()
07  */
08  begin
09   SYS.DBMS_LOCK.sleep(ain_seconds);
10  end wait;
11  /
12  @pe.sql wait

The DDL syntax used by Listing 2-5 is as follows:

CREATE [OR REPLACE] PROCEDURE <procedure_name> [(
<parameter_name_1>          [IN] [OUT] <parameter_data_type_1>,
<parameter_name_2>          [IN] [OUT] <parameter_data_type_2>,...
<parameter_name_N>          [IN] [OUT] <parameter_data_type_N> )] IS
  --the declaration section
BEGIN
  -- the executable section
EXCEPTION
  -- the exception-handling section
END;
/

where <procedure_name> is the name of the PROCEDURE; <parameter_name> is the name of a parameter being passed IN, OUT, or IN and OUT; and <parameter_data_type> is the PL/SQL data type of the corresponding parameter. The brackets around the keywords OR REPLACE denote that they are optional. In addition, just as with a function, the brackets around the parameters denote that they are optional.

The block structure of a PROCEDURE is exactly the same as an anonymous block, except for the addition of the DDL CREATE PROCEDURE keywords and the optional parameters. A procedure differs from a function in that it does not have a RETURN parameter.

Let’s take a look at Listing 2-5, line by line:

• Line 1 contains the DDL keywords to CREATE a stored PROCEDURE.
• Line 2 passes in one parameter: the number of seconds to wait.
• Lines 3 through 7, in the declaration section, have a multiline comment that documents the procedure’s source code filename, author, date the procedure was written, and finally a comment about what the procedure does.
• On line 8, the keyword BEGIN starts the executable section.
• On line 9, I call the procedure sleep() located in package DBMS_LOCK, owned by user SYS. sleep() calls the host operating system’s sleep() or wait function and then returns sometime after the specified period in seconds.
• On line 10, the keyword END ends the executable section. Did you notice that there is no defined exception-handling section? Since I didn’t code one, PL/SQL will use the default exception handler, which will simply raise the error to the enclosing program unit.
• On line 11, the slash (/) tells Oracle to store and then compile the procedure.
• Line 12 calls a helper script to show any compilation errors.

The PL/SQL block structure for the stored procedure in Listing 2-5 was really not that much different from the structure of the anonymous procedure and the stored function. The wait() procedure has a name and parameters, while an anonymous procedure does not. In addition, wait() does not return a value, while a stored function does.

It’s Your Turn to Create a Procedure

Now you’ll create a procedure that wraps the SYS.DBMS_OUTPUT.put_line() procedure, but uses a very short name. You’ll end up using the SYS.DBMS_OUTPUT.put_line() procedure a lot. It gets tiresome to type a 24-character method name every time you want to display a line of text on the screen in SQL*Plus. So, to save keystrokes, you will give your SYS.DBMS_OUTPUT.put_line() wrapper procedure the name pl(), as in p for put and l for line.

You can use the procedure in Listing 2-5 as a model. Just replace the parameter on line 2 with aiv_text in varchar2, and write line 9 with a call to SYS.DBMS_OUTPUT.put_line(aiv_text).

1. Write the DDL to create your procedure.
2. Save your new DDL script with the file name pl.prc.
3. At your SQL*Plus prompt, type the at sign (@) followed by the name of your file to store and compile your new procedure.
4. Test your new procedure by using it in an anonymous procedure.

Listing 2-6 shows my solution for this exercise.

Listing 2-6. A Lazy Typist’s SYS.DBMS_OUTPUT.put_line( ), pl.prc

01  create or replace PROCEDURE pl(
02  aiv_text            in     varchar2 ) is
03  /*
04  pl.prc
05  by Donald J. Bales on 2014-10-20
06  A wrapper procedure for SYS.DBMS_OUTPUT.put_line()
07  for the lazy typist.
08  */
09
10  begin
11    SYS.DBMS_OUTPUT.put_line(aiv_text);
12  end pl;
13  /
14  @pe.sql pl

Listing 2-7 is my test unit for procedure pl().I’ve named it with the same file name as my procedure, except for the extension: I used .prc for the stored procedure and .sql for the anonymous procedure, its test unit. Since this is a wrapper procedure, I’m simply testing the known limits of procedure SYS.DBMS_OUTPUT.put_line() to make sure pl() is working properly.

Listing 2-7. A Test Unit for Procedure pl( ), pl.sql

01  rem pl.sql
02  rem by Donald J. Bales on 2014-10-20
03  rem pl's test unit
04
05  declare
06
07    v_max_line varchar2(32767);
08
09  begin
10    -- The next three lines initialize the
11    -- variable v_max_line with 32,767 spaces.
12    for i in 1..32767 loop
13      v_max_line := v_max_line || ' ';
14    end loop;
15
16    pl('Test a line of text.'),
17
18    pl('Test a number, such as 1?'),
19    pl(1);
20
21    pl('Test a date, such as 01/01/1980?'),
22    pl(to_date('19800101', 'YYYYMMDD'));
23
24    pl('Test a line <= 32767'),
25    pl(v_max_line);
26
27    pl('Test a line  > 32767'),
28    pl(v_max_line||' '),
29
30    pl('Test a multi-line'),
31    pl('12345678901234567890123456789012345678901234567890'||
32       '12345678901234567890123456789012345678901234567890'||chr(10)||
33       '12345678901234567890123456789012345678901234567890'||
34       '12345678901234567890123456789012345678901234567890'||chr(10)||
35       '12345678901234567890123456789012345678901234567890'||
36       '12345678901234567890123456789012345678901234567890'),
37  end;
38  /

Here’s the output from my test unit:

SQL> @pl.sql

Test a line of text.
Test a number, such as 1?
1
Test a date, such as 01/01/1980?
01-JAN-80
Test a line <= 32767
Test a line  > 32767
declare
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "RPS.PL", line 11
ORA-06512: at line 24

The line of text, number 1, date 01/01/1980, and line <= 32767 characters tests ran, but the last test, multiline, didn’t because the > 32767 test threw an exception that was not handled. Let’s see how the code can handle this problem.

Nested Blocks

You can, and should when necessary, nest PL/SQL blocks. To nest a PL/SQL block means to embed one or more PL/SQL blocks inside another PL/SQL block. Nesting PL/SQL blocks allows you greater control over your PL/SQL program’s execution. You can wrap one or more PL/SQL or SQL statements in their own PL/SQL block, so you can catch an exception that is generated within that block. I call this “blocking” code. Let’s look at an example.

An Example of Nesting Blocks

As you saw, the test unit for procedure pl() in Listing 2-7 stopped executing after line 28 because line 28 caused the following error:

ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line

In Listing 2-8, I’ve recoded the pl() test unit. This time, I’ve blocked the lines that I expect to fail, so the program will catch any raised exceptions and continue processing the code all the way to the end of the source code listing.

Listing 2-8. The New, Improved pl( ) Test Unit, pl2.sql

01  rem pl2.sql
02  rem by Donald J. Bales on 2014-10-20
03  rem Test unit for procedure pl
04
05  declare
06    v_max_line varchar2(32767);
07
08  begin
09    -- The next three lines initialize a variable v_max_line with 32,767 spaces.
10    for i in 1..32767 loop
11      v_max_line := v_max_line || ' ';
12    end loop;
13
14    pl('Test a line of text.'),
15
16    pl('Test a number, such as 1?'),
17    pl(1);
18
19    pl('Test a date, such as 01/01/1980?'),
20    pl(to_date('19800101', 'YYYYMMDD'));
21
22    pl('Test a line <= 32767'),
23    pl(v_max_line);
24
25    pl('Test a line  > 32767'),
26    begin
27      pl(v_max_line||' '),
28    exception
29      when OTHERS then
30        pl(SQLERRM);
31    end;
32
33    pl('Test a multi-line'),
34    begin
35      pl('12345678901234567890123456789012345678901234567890'||
36         '12345678901234567890123456789012345678901234567890'||chr(10)||
37         '12345678901234567890123456789012345678901234567890'||
38         '12345678901234567890123456789012345678901234567890'||chr(10)||
39         '12345678901234567890123456789012345678901234567890'||
40         '12345678901234567890123456789012345678901234567890'),
41    exception
42      when OTHERS then
43        pl(SQLERRM);
44    end;
45  end;
46  /

The changed lines (highlighted in the listing) are as follows:

• Line 26 starts a nested block with the keyword BEGIN around the PL/SQL statement that will actually test > 32767.
• Line 28 contains a corresponding keyword EXCEPTION in order to create an exception-handling section for the nested PL/SQL block.
• Line 29 has the phrase when OTHERS then in order to catch all exceptions that can be generated within the nested block.
• On line 30, I once again call procedure pl(), but this time I display the exception error message: SQLERRM.
• On line 31, the nested block ends with the keyword END.
• Once again, on lines 34 and 41 through 44, I create a nested block around the next PL/SQL statement that I expect may fail.

Let’s see the results of these changes:

SQL> @pl2.sql
Test a line of text.
Test a number, such as 1?
1
Test a date, such as 01/01/1980?
01-JAN-80
Test a line <= 32767
Test a line  > 32767
ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line
Test a multi-line
1234567890123456789012345678901234567890123456789012345678901234567890...

PL/SQL procedure successfully completed.

Great! Now my test unit completed successfully, and it reported the errors I expected to see. As we move through the chapters ahead, you’ll get plenty of experience nesting PL/SQL blocks.

Rules for Nesting

Here are the rules I employ when it comes to blocking PL/SQL code:

• Block every SQL statement except cursors (you’ll learn about cursors in Chapter 5).
• Block any PL/SQL statement where you are converting from one data type to another, or moving a possibly larger character string into a smaller character string variable (you’ll learn about variables in Chapter 3).
• Block any PL/SQL statement that you expect will raise an exception.

The important point about these nesting rules is that blocking will enable you to identify the reason for and the location of an exception when it is raised. In turn, that will make your software development easier and the resulting programs more robust.

Packages

In practice, you’ll rarely create a stand-alone stored function or procedure. Instead, you’ll use a package. What is a package? A package is a means to organize related functions and procedures together, like creating a library, but in PL/SQL jargon the library is called a package.

A PL/SQL package has two parts:

• A package specification
• A package body

A package specification (spec) is the public face to the package. It lists any globally accessible constants, cursors, functions, procedures, and variables. By “globally accessible,” I mean those procedures, functions, and other items that other PL/SQL programs can access. If you consider a package as a sort of library, then the package spec describes what you can read from that library, while the package body contains the behind-the-scenes code that implements the package spec.

Create a Package Specification

Listing 2-9 is an example of a package spec. It shows the package spec for the package DATE_, which is a utility package for date-related constants and functions.

Listing 2-9. The DATE_ Package Spec, date_.pks

01  create or replace package DATE_ AS
02  /*
03  date_.pks
04  by Donald J. Bales on 2014-10-20
05  Additional DATE data type methods.
06  */
07
08  -- The maximum and minimum date values.
09
10  d_MAX                        constant date :=
11    to_date('99991231235959', 'YYYYMMDDHH24MISS'),
12  d_MIN                        constant date :=
13    to_date('-47120101', 'SYYYYMMDD'),
14
15
16  -- Returns the specified date with the time set to 23:59:59, therefore,
17  -- the end of the day.
18
19  FUNCTION end_of_day(
20  aid_date          in     date )
21  return            date;
22
23
24  -- Returns constant d_MAX. This is useful in SQL statements where the
25  -- constant DATE_.d_MAX is not accessible.
26
27  FUNCTION get_max
28  return            date;
29
30
31  -- Returns constant d_MIN. This is useful in SQL statements where the
32  -- constant DATE_.d_MIN is not accessible.
33
34  FUNCTION get_min
35  return            date;
36
37
38  -- Text-based help for this package. "set serveroutput on" in SQL*Plus.
39
40  PROCEDURE help;
41
42
43  -- Returns a randomly generated date that exists between the years specified.
44
45  FUNCTION random(
46  ain_starting_year  in     number,
47  ain_ending_year    in     number )
48  return                    date;
49
50
51  -- Returns the specified date with the time set to 00:00:00, therefore, the
52  -- start of the day.
53
54  FUNCTION start_of_day(
55  aid_date          in     date )
56  return                   date;
57
58
59  -- Test unit for this package.
60
61  PROCEDURE test;
62
63
64  end DATE_;
65  /
66  @se.sql DATE_

The DDL syntax used to create the package spec in Listing 2-9 is as follows:

CREATE [OR REPLACE] PACKAGE <package_name> AS
-- one or more: constant, cursor, function, procedure, or variable declarations
END <package_name>;

where <package_name> is the name of the package you’re creating.

Not much to a package spec? Sure there is. Now, instead of using the DDL CREATE FUNCTION or CREATE PROCEDURE, you’ll use the keywords FUNCTION and PROCEDURE for each PL/SQL method you want to declare globally.

Let’s take a look at the package spec in Listing 2-9:

• Line 1 uses the DDL keywords to CREATE a stored PACKAGE specification.
• Lines 10 and 12 declare two global constants (ones available to any other PL/SQL stored procedure), d_MAX and d_MIN, which are the current maximum and minimum date values supported by Oracle.
• Lines 19, 27, 34, 45, and 54 declare five date functions. Please note that these are only declarations; they have no code. The code will be found in the package body.
• Lines 40 and 61 declare two “helper” procedures. I will discuss these helper procedures in great length in Chapters 7, 8, and 9.
• Line 64 ends the PACKAGE declaration with the keyword END.
• On line 65, I tell Oracle to store and compile the package spec.
• Line 66 calls a helper SQL script to show any errors.

You may also have noticed that I took the time to document the purpose of every declaration in the package spec. You should do the same. Documenting as you go is one of the major characteristics of a professional PL/SQL programmer.

It’s Your Turn to Create a Package Specification

Do you remember the function to_number_or_null() in Listing 2-2? Now you will create a package called NUMBER_ that has it as one of its methods. Mimic what I just showed you as you follow these steps.

1. Write the DDL to create a package spec called NUMBER_.
2. Save your DDL script as number_.pks.
3. Execute your DDL script in SQL*Plus: SQL> @number_.pks.
4. Type desc number_ at the SQL*Plus prompt to verify that your package spec exists.

As usual, if you get any errors, figure out what’s wrong so you can compile the script successfully. Remember to use the script in Listing 2-9 as a model. Listing 2-10 shows my solution.

Listing 2-10. The NUMBER_ Package Spec, number_.pks

01  create or replace package NUMBER_ as
02  /*
03  number_.pks
04  by Donald J. Bales on 2014-10-20
05  A utility package for the data type NUMBER
06  */
07
08  /*
09  Returns the passed varchar2 as a number if it represents a number,
10  otherwise, it returns NULL
11  */
12  FUNCTION to_number_or_null (
13  aiv_number          in     varchar2 )
14  return                     number;
15
16  end NUMBER_;
17  /
18  @se.sql

There’s no way to test the package spec, because there’s no code yet. The code goes in the package body.

Create a Package Body

A package body is the implementation for a package spec. It must contain the code for any functions or procedures declared in its corresponding package spec. In addition, the body can also contain any constant, cursor, function, procedure, or variable that should be accessible within the package body (that is, not publicly accessible). Let’s take a look at the corresponding package body for package DATE_.

I’m about to show you a package body that has a lot of PL/SQL code that we haven’t covered yet. Don’t worry too much about the code itself. You won’t understand it all now, but you’ll be able to understand it after finishing this book. What you need to take away from this example is the package body structure, which is also a PL/SQL block structure. Let’s look at Listing 2-11.

Listing 2-11. The DATE_ Package Body, date_.pkb

001  create or replace package body DATE_ AS
002  /*
003  date_.pkb
004  by Donald J. Bales on 2014-10-20
005  Additional DATE data type methods
006  */
007
008
009  FUNCTION end_of_day(
010  aid_date          in     date )
011  return                   date is
012
013  begin
014   return to_date(to_char(aid_date, 'SYYYYMMDD')||'235959',
015     'SYYYYMMDDHH24MISS'),
016  end end_of_day;
017
018
019  FUNCTION get_max
020  return            date is
021
022  begin
023   return d_MAX;
024  end get_max;
025
026
027  FUNCTION get_min
028  return            date is
029
030  begin
031   return d_MIN;
032  end get_min;
033
034
035  FUNCTION random(
036  ain_starting_year in     number,
037  ain_ending_year   in     number )
038  return                   date is
039
040  d_random                 date;
041  n_day                    number;
042  n_month                  number;
043  n_year                   number;
044
045  begin
046    n_year    := round(DBMS_RANDOM.value(
047      ain_starting_year, ain_ending_year), 0);
048    --pl('n_year='||n_year);
049    loop
050      n_month := round(DBMS_RANDOM.value(1, 12), 0);
051      --pl('n_month='||n_month);
052      n_day   := round(DBMS_RANDOM.value(1, 31), 0);
053      --pl('n_day='||n_day);
054      begin
055        d_random := to_date(lpad(to_char(n_year),  4, '0')||
056                            lpad(to_char(n_month), 2, '0')||
057                            lpad(to_char(n_day),   2, '0'),
058                            'YYYYMMDD'),
059        exit;
060      exception
061        when OTHERS then
062          if SQLCODE <> -1839 then
063            pl(SQLERRM);
064          --else
065          --  pl('29-31'),
066          end if;
067      end;
068    end loop;
069    return d_random;
070  end random;
071
072
073  FUNCTION start_of_day(
074  aid_date            in     date )
075  return                     date is
076
077  begin
078   return trunc(aid_date);
079  end start_of_day;
080
081
082  -- Write up the help text here in this help method
083  PROCEDURE help is
084
085  begin
086  --   1234567890123456789012345678901234567890123456789012345678901234567890
087    pl('============================== PACKAGE =============================='),
088    pl(chr(9));
089    pl('DATE_'),
090    pl(chr(9));
091    pl('------------------------------ CONSTANTS ----------------------------'),
092    pl(chr(9));
093    pl('d_MAX'),
094    pl(chr(9)||'Represents the maximum value for the DATE data type.'),
095    pl('d_MIN'),
096    pl(chr(9)||'Represents the minimum value for the DATE data type.'),
097    pl(chr(9));
098    pl('------------------------------ FUNCTIONS ----------------------------'),
099    pl(chr(9));
100    pl('DATE_.end_of_day('),
101    pl('aid_date            in     date)'),
102    pl('return                     date;'),
103    pl(chr(9)||'Returns the passed date with the time portion set to the end '),
104    pl(chr(9)||'of the day:'),
105    pl(chr(9)||'23:59:59 (HH24:MI:SS).'),
106    pl(chr(9));
107    pl('DATE_.get_max( )'),
108    pl('return                     date;'),
109    pl(chr(9)||'Returns the constant DATE_.d_MAX.'),
110    pl(chr(9));
111    pl('DATE_.get_mim( )'),
112    pl('return                     date;'),
113    pl(chr(9)||'Returns the constant DATE_.d_MIN.'),
114    pl(chr(9));
115    pl('DATE_.random('),
116    pl('ain_starting_year   in     number,'),
117    pl('ain_ending_year     in     number)'),
118    pl('return                                date;'),
119    pl(chr(9)||'Returns a random date that exists between the specified years.'),
120    pl(chr(9));
121    pl('DATE_.start_of_day('),
122    pl('aid_date            in     date)'),
123    pl('return                     date;'),
124    pl(chr(9)||'Returns the passed date with the time portion set to the start'),
125    pl(chr(9)||'of the day:'),
126    pl(chr(9)||'00:00:00 (HH24:MI:SS).'),
127    pl(chr(9));
128    pl('------------------------------ PROCEDURES ----------------------------'),
129    pl(chr(9));
130    pl('DATE_.help( );'),
131    pl(chr(9)||'Displays this help text if set serveroutput is on.'),
132    pl(chr(9));
133    pl('DATE_.test( );'),
134    pl(chr(9)||'Built-in test unit. It will report success or error for each'),
135    pl(chr(9)||'test if set'),
136    pl(chr(9)||'serveroutput is on.'),
137    pl(chr(9));
138  end help;
139
140
141  PROCEDURE test is
142
143  d_date            date;
144
145  begin
146    pl('============================== PACKAGE ==============================='),
147    pl(chr(9));
148    pl('DATE_'),
149    pl(chr(9));
150    pl('1. Testing constants d_MIN and d_MAX'),
151    if d_MIN < d_MAX then
152     pl('SUCCESS'),
153    else
154     pl('ERROR: d_MIN is not less than d_MAX'),
155    end if;
156
157    pl('2. Testing end_of_day()'),
158    if to_char(end_of_day(SYSDATE), 'HH24MISS') = '235959' then
159     pl('SUCCESS'),
160    else
161     pl('ERROR: end_of_day is not 23:59:59'),
162    end if;
163
164    pl('3. Testing get_max()'),
165    if get_max() = d_MAX then
166     pl('SUCCESS'),
167    else
168     pl('ERROR: get_max() is not equal to d_MAX'),
169    end if;
170
171    pl('4. Testing get_min()'),
172    if get_min() = d_MIN then
173     pl('SUCCESS'),
174    else
175     pl('ERROR: get_min() is not equal to d_MIN'),
176    end if;
177
178    pl('5. Testing random() 1000 times'),
179    for i in 1..1000 loop
180      d_date := random(1, 9999);
181      --pl(to_char(d_date, 'YYYY-MM-DD HH24:MI:SS'));
182    end loop;
183    pl('SUCCESS'),
184
185    pl('6. Testing start_of_day()'),
186    if to_char(start_of_day(SYSDATE), 'HH24MISS') = '000000' then
187     pl('SUCCESS'),
188    else
189     pl('ERROR: start_of_day is not 00:00:00'),
190    end if;
191  end test;
192
193
194  end DATE_;
195  /
196  @be.sql DATE_

The DDL syntax used to create the package body in Listing 2-11 is as follows:

CREATE [OR REPLACE] PACKAGE BODY <package_name> AS
-- one or more constant, cursor, or variable declarations
-- one or more function, or procedure implementations
[BEGIN]
-- you can code a PL/SQL block called an initialization section that is
-- executed only once per session, when the package is first instantiated
-- into memory
[EXCEPTION]
-- you can code an exception-handling section for the initialization section
END <package_name>;

where <package_name> is the name of the package body you’re creating.

Did you notice the two optional sections? If you have some initialization code that you want to run the first time a package is loaded into memory, you can use the keyword BEGIN to start an initialization section. And if you want an exception-handling section for your initialization section, you can add it by using the keyword EXCEPTION. This is classic PL/SQL block structure.

Your implementations of functions and procedures are actually embedded functions and procedures in the declaration section of a PL/SQL block! Any constant, cursor, or variable that you declare in the declaration section of the package body is accessible to all the declared functions and procedures in that section, but not globally to other stored procedures. Only the items you declared in the package spec are accessible to other stored procedures.

As I noted at the beginning of this section, Listing 2-11 contains a lot of code that we haven’t yet discussed, so I’ll leave its explanation for later chapters.

It’s Your Turn to Create a Package Body

You should already have a package spec created for the NUMBER_ package, as in Listing 2-10. Now it’s time create the corresponding package body. It’s almost like the package spec, except for the keyword BODY in the DDL and the function to_number_or_null()’s implementation, as in Listing 2-2. Create your package body by following these steps.

1. Write the DDL to create a package body called NUMBER_.
2. Save your DDL script as number_.pkb.
3. Execute your DDL script in SQL*Plus: SQL> @number_.pkb.
4. Test your function using a SELECT statement, just as you did way back in the section on functions. This time, however, you’ll prefix the function name with the name of your package.

Again, if you get any errors, work on your script until you figure out what’s wrong so you can compile the script successfully. Listing 2-12 shows my solution.

Listing 2-12. The NUMBER_ Package Body, number_.pkb

01  create or replace package body NUMBER_ as
02  /*
03  number_.pkb
04  by Donald J. Bales on 2014-10-20
05  A utility package for the data type NUMBER
06  */
07
08  FUNCTION to_number_or_null (
09  aiv_number           in     varchar2 )
10  return                      number is
11  begin
12    return to_number(aiv_number);
13  exception
14    when OTHERS then
15      return NULL;
16  end to_number_or_null;
17
18  end NUMBER_;
19  /
20  @be.sql

Benefits of Using Packages

As I mentioned earlier, anything declared in a package spec can be seen by any username that has execute privileges on the package. Package specs also reduce dependency invalidation issues. What does that mean?

Say that procedure1 calls function1 and is called by procedure2. Then if you change function1, function1 becomes invalid, and so do procedure1 and procedure2. This means you need to recompile all three PL/SQL programs. This chain of dependency can be broken by using packages.

Now suppose that you use packages: package1.procedure1 calls package2.function1, and it is called by package3.procedure2. If you change the package implementation, or body, of package2.function1, it will not cause the invalidation of the function, nor any dependent PL/SQL blocks. You will cause dependent PL/SQL blocks to be invalidated only if you change the package spec.

Summary

Table 2-1 shows a side-by-side comparison of the syntax used for the various types of PL/SQL blocks covered in this chapter. The point here is that PL/SQL code always exists in a PL/SQL block, and that PL/SQL blocks are quite consistent in their structure. Even nested PL/SQL blocks are consistent with the ones shown in Table 2-1.

Now that you have a firm understanding of the block structure of PL/SQL, you’re ready to move on to the next step: learning about PL/SQL data types and variables.