student_id = arg_student_id;

return GPA;

end;

Obtaining Error Messages When Creating Stored Procedures

If Oracle detects errors when you create a stored PL/SQL program, it issues a nondescript message indicating that errors occurred—without providing any additional details. For example, Listing 10.3 demonstrates what happens if you try to create a stored procedure with a syntax error. To view the errors resulting from the attempted compilation of the PL/SQL code, you can use the SQL*Plus command show errors, which displays the specific PL/SQL compilation errors.

Listing 10.3. An error message returned by Oracle during compilation of a stored procedure.

SQL> create or replace procedure show_inserts IS
  2
  2  max_records constant int := 100;
  3  i           int := 1;
  4
  4  begin
  5
  5  dbms_output.enable;
  6
  6  for i in 1..max_records loop
  7
  7    if (mod(i,10) = 0) then
  8      insert into test_table
  9             (record_number, current_date)
 10      values
 11             (i, SYSDATE)
 12      dbms_output.put_line(`The value of i is ` || to_char(i));

                                                  continues

Listing 10.3. continued

 13
 13    else
 14      null;
 15
 15    end if;
 16
 16  end loop;
 17
 17  end;
 18  /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE SHOW_INSERTS:
LINE/COL ERROR
-------- --------------------------------------------------------------
12/5     PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting
         one of the following:
         ;
         ; was inserted before "DBMS_OUTPUT" to continue.

ANALYSIS
In an attempt to create or replace the procedure show_inserts, Oracle returned a warning, just after line 18, indicating that there were compilation errors. To see the specific error, you issue the command show errors. As you can see on line 11, the INSERT statement isn't terminated with a semicolon.

Retrieving a Stored Procedure

After a stored procedure has been created, you might want to look at the source code of a PL/SQL program. However, even if the SQL script that was used to create the stored procedure isn't available, you can still retrieve the source code of a stored procedure by querying an Oracle data dictionary view.

The Oracle data dictionary is a group of tables that contain information about the Oracle database itself. Because these data dictionary tables are somewhat cryptic in structure, Oracle defines a set of views that provide a more coherent perspective of the data dictionary (please refer to Day 8, "More Sophisticated Queries with SQL," for more information about views). One of these views is named USER_SOURCE, which provides four columns:

As an example, suppose that you created a stored procedure named DROP_CLASS. If you want to see the source code of DROP_CLASS, query the USER_SOURCE data dictionary view, as illustrated in Listing 10.4.

Listing 10.4. Retrieving the source code for a stored procedure.

SQL> select line, text
  2  from user_source
  3  where
  4  name = `DROP_CLASS'
  5  order by line;

LINE TEXT
---- -------------------------------------------------------------------
   1 procedure drop_class (arg_student_ID IN varchar2,
   2                       arg_class_ID   IN varchar2,
   3                       status     OUT number) is
   4
   5 counter  number;
   6
   7 begin
   8
   9 status := 0;
  10
  11 -- Verify that this class really is part of the student's schedule.
  12
  13 select count(*) into counter
  14 from student_schedule
  15 where
  16 student_id = arg_student_id and
  17 class_id   = arg_class_id;
  18
  19 if counter = 1 then
  20   delete from student_schedule
  21   where
  22   student_id = arg_student_id and
  23   class_id   = arg_class_id;
  24   status := -1;
  25 end if;
  26
  27 end;

27 rows selected.

Obtaining a List of Procedures, Functions, Packages, and Package Bodies

You can query USER_OBJECTS to obtain a list of stored procedures, functions, packages, and package bodies owned by the Oracle account to which you are currently connected. If

you wanted to see all of the these objects, regardless of ownership, you would query DBA_OBJECTS rather than USER_OBJECTS. The OBJECT_TYPE column in DBA_OBJECTS indicates the type of the object: table, view, procedure, and so on.

To obtain a list of the types of database objects owned by a user, use the query shown in

Listing 10.5.

Listing 10.5. Determining the object types owned by the current user.

SQL> select distinct object_type
  2  from user_objects;

OBJECT_TYPE
-------------
FUNCTION
INDEX
PACKAGE
PACKAGE BODY
PROCEDURE
TABLE
VIEW

7 rows selected.

Forward Declaration of Procedures and Functions

PL/SQL requires that you declare any identifier—constant, variable, cursor, procedure, or function—using it elsewhere in a PL/SQL subprogram. This requirement can cause a problem when two subprograms reference each other, as shown in Listing 10.6.

Listing 10.6. Function referenced before declaration.

SQL> set serveroutput on
SQL>
SQL> declare
  2
  2  function Medicare_Patient (Patient_ID IN varchar2)
  3           return number is
  4
  4  status   number;
  5  Pat_ID   varchar2(6);
  6
  6  begin

  7
  7  if Insurable_Patient (Pat_ID) = 2 then
  8     status := 1;
  9  end if;
 10
 10  return status;
 11
 11  end Medicare_Patient;
 12
 12
 12  function Insurable_Patient (Patient_ID IN varchar2)
 13           return number is
 14
 14  status   number;
 15  Pat_ID   varchar2(6);
 16
 16  begin
 17
 17  if Medicare_Patient (Pat_ID) = 2 then
 18     status := 1;
 19  end if;
 20
 20  return status;
 21
 21  end Insurable_Patient;
 22
 22  --  Executable portion of anonymous block.
 23
 23  begin
 24
 24  dbms_output.enable;
 25
 25  end;
 26  /
declare
 *
ERROR at line 1:
ORA-06550: line 7, column 4:
PLS-00313: `INSURABLE_PATIENT' not declared in this scope
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored

ANALYSIS
As you can see in Listing 10.6, PL/SQL doesn't recognize the reference to Insurable_Patient in the function Medicare_Patient (line 7) because the declaration of Insurable_Patient occurs after the declaration of Medicare_Patient (line 2). To circumvent this dilemma, you include a forward declaration of the subprogram in the declare section. The forward declaration is a declaration of the subprogram, its arguments, and return type. Listing 10.7 demonstrates how to specify a forward declaration for Insurable_Patient (line 2) for the preceding example.