General methods for debugging stored procedures and functions in PL/SQL Development

General methods for debugging stored procedures and functions in PL/SQL Development

General methods for debugging stored procedures and functions in PL/SQL Development
The powerful features provided by Oracle in PLSQL allow database developers to perform complex tasks on the database end. This article will combine the related packages provided by Oracle) and a very good third-party development tool to introduce the methods for developing and debugging stored procedures in PLSQL, of course, also applies to functions.
Copyright Disclaimer: This article can be reproduced at will. During reprinting, you must mark the original source and author information in the form of a hyperlink.

Directory
Preparations
Start with the simplest Stored Procedure
Debug stored procedures
Write log files during storage
Capture Violation
　
The powerful features provided by Oracle in PLSQL allow database developers to perform complex tasks on the database end. This article will combine the related packages provided by Oracle) and a very good third-party development tool to introduce the methods for developing and debugging stored procedures in PLSQL, of course, also applies to functions.
The software version and environment used in this article:
Server: Oracle 8.1.2 for Solaris 8
PL/SQL developer 4.5
Preparations
Before you begin, assume that you have installed the Oracle Database Service, created a database, and configured a listener to allow the client to connect; at the same time, you have a development client with a local net service name set, and you have installed a later version or update of the PL/SQL developer development tool.
In the following sample code, we use the example tables Scott. Dept and Scott. EMP. Provided by the Oracle database by default to create tables:
Create Table Scott. Dept
(
Deptno number (2) not null,
Dname varchar2 (14 ),
Loc varchar2 (13)
)
Create Table Scott. EMP
(
Empno number (4) 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 the simplest Stored Procedure
Now we need to write a stored procedure and enter a department number to obtain information about all employees in the Department, including employee numbers and names. employee 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
)
Begin
Open curemp
Select empno,
Ename
From Scott. EMP
Where deptno = in_deptno;
End usp_getempbydept;
We have defined two parameters above. The second parameter needs to use cursor to return employee information. PLSQL provides the ref cursor data type, which can be defined in two ways, one is a strong type and the other is a weak type. The former specifies the data type returned by the cursor during definition, and the latter can be left unspecified. The database dynamically binds the data type based on the query statement.
Before use, you must use the type keyword to define the data type ref_cursor in the Custom package: pkg_const
Create or replace package pkg_const
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.
Debug stored procedures
Use PL/SQL developer to log on to the database. Username: Scott. default password: Tiger. compile the package and stored procedure respectively, find the new stored procedure in the procedure column of the left-side browser, right-click the stored procedure, and select "test"/"test ", add the parameter values to be entered below and press the shortcut key F8 to directly run the stored procedure. After the execution is complete, click the button next to the return parameter to view the result set.
If the internal statements of the stored procedure are complex, you can press F9 to enter the Stored Procedure for tracking and debugging. PL/SQL developer provides a tracking and debugging function similar to common development tools. It supports multiple methods, such as step, step over, and step out. You can also trace variables or assign values manually.
Write log files during storage
The above methods can provide the maximum convenience for writing and debugging stored procedures in the development phase, but to confirm whether our code works normally in the system test or production environment, you need to record the log.
PLSQL provides an utl_file package. By defining the file_type type in the utl_file package, you can obtain a file handle, which can be used to perform common file operations. However, the default database parameter does not allow the use of the utl_file package. You need to manually configure it, use a GUI management tool or manually edit init. find the "utl_file_dir" parameter in the ora file. If not, add a row and modify it as follows:
Utl_file_dir = '/usr/tmp'
Or
Utl_file_dir = *
The first method limits the directories that can be accessed in the utl_file package, and the second method does not. Either way, ensure that the user running the database instance, generally Oracle, has the permission to access this directory. Otherwise, an error message will be reported during the use of the package.
Note that do not leave spaces around the equal sign, which may cause parsing errors, leading to invalid settings.
The following code adds a log record to the above stored procedure:
Create or replace procedure usp_getempbydept (
In_deptno in number,
Out_curemp out pkg_const.ref_cursor
)
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
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 reference two new constants:
Debug
Log_path
The debugging switch parameters and file path parameters are defined respectively. To solve this problem, we need to modify the previously defined package:
Create or replace package pkg_const
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 names and values of input parameters are paired to the log file. In the normal exit section of the code block, the names and values of output parameters are recorded in pairs, if the program unexpectedly exits, the error code and information are written to the log file in the Exception Handling Section. By using this information, you can quickly find out most of the errors that occur during the program running.
Note: If the type of the returned parameter is cursor, the returned result set cannot be written to the log file one by one in the stored procedure. In this case, the log information recorded in the calling program should be combined, the above code is analyzed as follows:
The fopen () function uses the given path and file name to create a file or open an existing file. This depends on the last parameter. When 'A' is used as the parameter, if the specified file does not exist, create a new file by using the file name and open it by writing 'W'. A file handle is returned.
The above Code creates log files in days and shares log files between different stored procedures. This method may trace the call sequence and logic of the program by viewing log files. In actual application, you can use more complex log file generation policies based on different requirements and specific analysis.
The put_line () function is used to write characters to the file and add line breaks at the end of the string. If you do not want to wrap the line, use the put () function.
The new_line () function is used to generate a specified number of empty rows. Modifications to the above files are written in a buffer zone. Executing fflush () will immediately write the content in the buffer into the file, call this function when you want to read the changes you have made before closing the file.
The is_open () function is used to determine the status of a file handle. When you run out, remember to close the opened file, call the fclose () function, and add this statement to the exception processing, prevents unclosed file handles from being left when the process exits abnormally.
Capture Violation
In PLSQL, you can use two built-in functions sqlcode and sqlerrm to find out which types of errors have occurred and obtain detailed message information. When an internal violation occurs, sqlcode returns an error number ranging from-1 to-20000, but with one exception, a positive number of 100 is returned only when the internal violation no_data_found occurs. When a user-defined violation occurs, sqlcode returns + 1 unless the user uses Pragma exception_init to bind the custom violation to a custom error code. When no violation is thrown, sqlcode returns 0.
The following is an example of a simple violation Capture:
Declare
I number (3 );
Begin
Select 100/0 into I from dual;
Exception
When zero_divide then
...
End;
In the preceding exception, we use the others keyword to capture all unspecified violations and record log processing. At the same time, we must throw the violation to the calling program again after completing these operations, call the function:
Raise_application_error (). This function returns a user-defined error number and error message to the caller. The first parameter specifies an error number, which is customized by the user, but it must be limited to-20000 to-20999 to avoid conflict with the error number defined in Oracle for exception. The second parameter must return a string, here we use it to return the error number and description we captured above.
Note: The violation thrown by using the raise_application_error () function is not an internal violation that begins to be captured within the program block, but defined by the user.