A general approach to debugging stored procedures and functions in PL/SQL development

Stored Procedures | General methods for debugging stored procedures and functions in PL/SQL development
Summary: Oracle's powerful features in Plsql enable database developers to perform complex tasks on the database side, combining Oracle-supplied packages (package) And a very good third-party development tool to describe how to develop and debug stored procedures in Plsql, but also for functions.

Copyright NOTICE: This article can be reproduced, reprint, please be sure to hyperlink form to indicate the original source and author information.
Original source: http://www.aiview.com/notes/ora_using_proc.htm
Author: Zhang Yang alex_doesathotmail.com
Last Updated: 2003-8-2
Directory preparation from one of the simplest stored procedures to debug stored procedures write log file catch violation in stored procedure
　
Oracle's powerful features in Plsql enable database developers to perform complex tasks on the database side, combining Oracle-provided packages (package) And a very good third-party development tool to describe how to develop and debug stored procedures in Plsql, but also for functions.

The software version and environment used in this article:
Server: Oracle 8.1.2 for Solaris 8
Pl/sql Developer 4.5
Preparatory work
Before you begin, assume that you have installed Oracle database services and have set up a database to allow the client to connect; At the same time, you already have a development client with the local net service name set up and you have installed the above version or update of the Pl/sql Developer development tool.

In the following sample code, we use the sample table scott.dept and scott.emp provided by default in the Oracle database. The statement to build the table is as follows:

CREATE TABLE SCOTT. DEPT
(
DEPTNO Number (2) is not NULL,
Dname VARCHAR2 (14),
LOC VARCHAR2 (13)
)

CREATE TABLE SCOTT. Emp
(
EMPNO Number (4) is not NULL,
Ename VARCHAR2 (10),
JOB VARCHAR2 (9),
MGR Number (4),
HireDate DATE,
SAL number (7,2),
COMM number (7,2),
DEPTNO Number (2)
)
Start with one of the simplest stored procedures
We now need to write a stored procedure, enter a department number, and ask for all employee information, including employee number and name, that belongs to this department. The employee's information is returned to the application through a cursor.

Create or replace procedure usp_getempbydept (
In_deptno in number,
Out_curemp out Pkg_const. Ref_cursor
) as
Begin
Open Curemp for
Select Empno,
Ename
From Scott.emp
where deptno = In_deptno;

End Usp_getempbydept;

Above we define two parameters, where the second parameter needs to use cursor to return the employee information, PLSQL provides the data type of REF CURSOR, can be defined in two ways, one is a strong type, the other is a weak type, which specifies the type of data returned by cursor when defined. The latter can be unspecified and dynamically bound by the database based on the query statement.

You must first define using the Type keyword before use, and we define the data type Ref_cursor in a custom package: Pkg_const

Create or replace package pkg_const as
Type ref_cursor is REF CURSOR;

End Pkg_const;

Note: This package needs to be compiled before the above stored procedure is created, because the stored procedure uses the data type defined in the package.
Debugging Stored Procedures
Use pl/sql Developer login database, username scott, password defaults to: Tiger. Compile the package and stored procedure separately, then locate the new stored procedure under the procedure column of the left browser, right-click, choose Test/test, add the parameter values that you want to enter below, press the shortcut key F8 to run the stored procedure directly, after the execution completes, You can click the button next to the return parameter to view the result set.

If the internal statement of the stored procedure is more complex, you can press F9 to enter the stored procedure for trace debugging. Pl/sql developer provides tracking and debugging functions similar to common development tools, including step, step over, and step out, for variables can also be trace or manually assigned value.
Write log files in stored procedures
The above methods provide maximum convenience for writing and debugging stored procedures during the development phase, but log is required to verify that our code is working properly in system testing or production environments.

Plsql provides a utl_file package that allows you to obtain a file handle by defining the File_type type in the Utl_file package, which enables you to implement general file manipulation functions. However, the default database parameters are not allowed to use the Utl_file package, need to manually configure, use the GUI's administrative tools or manually edit the Init.ora file, find the "utl_file_dir" parameter, if not, add a row, modify to the following:

Utl_file_dir= '/usr/tmp '

Or

utl_file_dir=*

The first approach defines the directories that can be accessed in the Utl_file package, and the second method is not qualified. Either way, make sure that the user running the database instance, typically Oracle, has access to the directory, or that the error message is reported in the process of using the package.

Note that the equal sign does not leave a blank, which may cause a parse error, causing the setting to be invalid.

Next, add the log code to the above stored procedure:

Create or replace procedure usp_getempbydept (
In_deptno in number,
Out_curemp out Pkg_const. Ref_cursor
) as
Fi utl_file.file_type;

Begin
if (pkg_const. DEBUG) Then
Fi: = Utl_file.fopen (pkg_const. Log_path, To_char (sysdate, ' YYYYMMDD ') | | '. Log ', ' a ');
Utl_file.put_line (FI, ' calling usp_getempbydept begin at ' | | to_char (sysdate, ' hh24:mi:ss mm-dd-yyyy ') | | ' ******' );
Utl_file.put_line (FI, ' INPUT: ');
Utl_file.put_line (FI, ' In_chid => ' | | in_chid);
End If;

Open Curemp for
Select Empno,
Ename
From Scott.emp
where deptno = In_deptno;

if (pkg_const. DEBUG) Then
Utl_file.put_line (FI, ' return: ');
Utl_file.put_line (FI, ' out_curemp:unknown ');
Utl_file.put_line (FI, ' usp_getempbydept end at ' | | | to_char (sysdate, ' hh24:mi:ss mm-dd-yyyy ') | | ' ******' );
Utl_file.new_line (FI, 1);
Utl_file.fflush (FI);
Utl_file.fclose (FI);
End If;

exception
When others then

if (pkg_const. DEBUG) Then
if (Utl_file.is_open (FI)) then
Utl_file.put_line (FI, ' ERROR: ');
Utl_file.put_line (FI, ' Sqlcode = ' | | | sqlcode);
Utl_file.put_line (FI, ' sqlerrm = ' | | | sqlerrm);
Utl_file.put_line (FI, ' usp_getempbydept end at ' | | | to_char (sysdate, ' hh24:mi:ss mm-dd-yyyy ') | | ' ******' );
Utl_file.new_line (FI, 1);
Utl_file.fflush (FI);
Utl_file.fclose (FI);
End If;
End If;

/* Raise the exception for caller. */
Raise_application_error ( -20001, Sqlcode | | '|' || SQLERRM);

End Usp_getempbydept;

In the above code, we also refer to two new constants:

DEBUG
Log_path

The debug switch parameters and file path parameters are defined separately, so we need to modify the package we defined earlier:

Create or replace package pkg_const as
Type ref_cursor is REF CURSOR;

DEBUG constant Boolean: = true;
LOG_PATH constant VARCHAR2 (256): = '/usr/tmp/db ';

End Pkg_const;

At the beginning of the code block, the name of the input parameter is entered into the log file with the value pairs, in the normal exit section of the code block, the output parameter names and values are also recorded in pairs, if the program quits normally, then in the processing part of the exception, the error code and error messages are written to the log file. This information is generally used to quickly find out most of the errors that occur in the program's operation.

Note: If the return parameter type is cursor and cannot be written to the log file within the stored procedure, then it should be combined with log information recorded in the calling program, and the following is a detailed analysis of the above code:

The fopen () function uses the given path and file name, create a new file or open an existing file, depending on the last parameter, when using ' a ' as a parameter, if the given file does not exist, create a new file with this file name and open it with a write ' W ' to return a file handle.

The above code establishes the log file in days, and the log file is shared between different stored procedures, and the advantage of this approach is that it is possible to trace the sequence and logic of the program's invocation by looking at the log file. In practical application, we should use more complex log file generation strategy according to different requirements and specific analysis.

The Put_Line () function is used to write characters to a file, add line breaks at the end of the string, and use the put () function if you do not want to wrap the line.

The New_line () function is used to generate a specified number of empty rows, where the modification of the file is written in a buffer, and execution fflush () will immediately write the contents of the buffer to the file, calling this function when you want to read the changes that have been made before the file is closed.

The Is_open () function is used to judge the state of a file handle, and it must remember to close the open file, call the Fclose () function, and add this statement to the exception processing, to prevent the process from exiting without closing the file handle.
Catch violation
In Plsql, you can use two built-in functions Sqlcode and SQLERRM to find out what kind of error occurred and get detailed message information, when an internal violation occurs, Sqlcode returns an error number from 1 to 20000, with one exception, A positive number of 100 is returned only when an internal no_data_found occurs. When a user-defined violation occurs, Sqlcode returns +1 unless the user uses pragma exception_init to bind a custom error number to a custom violation. When no violation is thrown, Sqlcode returns 0.

Here is an example of a simple catch violation:

Declare
I number (3);
Begin
Select 100/0 into I-dual;

exception
When Zero_divide Then
...
End

In the above exception we use the Others keyword to capture all unspecified violations and log log processing, and we must, after these processes, throw the violation back to the caller and invoke the function:
Raise_application_error (), which returns a user-defined error number and error message to the caller, the first parameter specifies an error number, which is defined by the user, but must be limited to between 20000 and 20999, Avoid conflicts with Oracle Internal definition exception error numbers, the second parameter needs to return a string, where we use it to return the error number and description of the error we captured above.

Note: The violation that is thrown through the raise_application_error () function is not an internal violation that is caught inside the block, but is defined by the user.