General way to debug stored procedures and functions in PL / SQL development
Summary: Oracle's powerful feature provided in PLSQL allows database developers to complete functional complex tasks in the database end, this article will be introduced in conjunction with the related packages provided by Oracle and a very excellent third-party development tool in PLSQL The method of developing and debuging the stored procedure is also applicable to functions.
Copyright Notice: This article can be reproduced, please be sure to indicate the original source and author information in hyperlink when reprint. Original Source: http://www.aiview.com/notes/ora_using_proc.htm Author: Zhang Yang Alex_doesAThotmail.com Last updated: 2003-8-2
table of Contents
Preparation From a simplest stored procedure to start the stored procedure to write the log file during the stored procedure to capture violations
The powerful feature provided in the PLSQL makes the database developers can complete the fun enough complex tasks in the database end. This article will be used in conjunction with the related package (Package) provided by Oracle and a very excellent third-party development tool to develop in PLSQL. And the method of debugging the stored procedure, of course, also applies to functions. Software version and environment in this article: Server: Oracle 8.1.2 for Solaris 8 PL / SQL Developer 4.5
Ready to work
Before you start, you have installed Oracle's database services, and have established a database to set the listener to allow the client to connect; at the same time, you already have a development client that sets a local NET service name. And the above version or update of the PL / SQL Developer development tool has been installed.
In the sample code below, we use the sample table Scott.dept and Scott.emp. The statement of the table is as follows: 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), CommM Number (7, 2), Deptno Number (2))
Starting from a simplest stored procedure
We now need to write a stored procedure, enter a department's number, requiring all employee information belonging to this department, including employee number and name. The employee information returns 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, wherein the second parameter need to use the cursor to return employee information, in PLSQL Provides the data type of REF CURSOR, can be defined in two ways, one is a strong type, one is a weak type, the former specifies the data type returned by Cursor when defined, the latter may not be specified, by the database according to the query statement Perform dynamic binding.
You must first use the Type keyword before use, we define the data type REF_CURSOR to define 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 created The stored procedure is compiled before the stored procedure is used in the package defined in the package.
Debug stored procedure
Use the PL / SQL Developer to log in to the database, the username Scott, the password is default: Tiger. The package and stored procedures are compiled separately, and then find the new stored procedure to the left browser's procedure column, click Right click, select "Test" / "Test", add the parameter value that needs to be entered below, press the shortcut key F8 to run the stored procedure, after execution, you can click the button next to the return parameter to view the result set. If the stored procedure is more complicated, you can press F9 Enter the stored procedure for tracking debugging. PL / SQL Developer provides similar tracking debugging features similar to generic development tools, divided into STEP, STEP OVER, STEP OUT, etc., for variables, TRACE or manual assignment.
Write log file during stored procedure
The above method can provide the maximum convenience of writing and debug stored procedures in the development phase, but in order to confirm whether our code is working properly in the system test or production environment, logging is required.
PLSQL provides a UTL_FILE package that can get a file handle by defining the file_type type in the UTL_FILE package, and a general file operation function can be implemented by this handle. But the default database parameters are not allowed to use the UTL_FILE package, you need to manually configure, use the GUI management tool or manually edit the init.ora file, find the "UTL_FILE_DIR" parameter, if not, add a line, modify it as follows:
UTL_FILE_DIR = '/ USR / TMP'
or
UTL_FILE_DIR = *
The first method defines a directory that can be accessed in a UTL_FILE package, and the second mode is not limited. Either way, to ensure that users running the database instance, generally Oracle, have access permissions for this directory, otherwise the error message will be reported during the use of the package.
Note that the equal sign should not leave the space, which may cause a resolution error, resulting in invalidation. The following code is added log records stored in the above process: 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, 'YYYMMDD') || '.log', 'a'); UTL_FILE.PUT_LINE (FI, '****** CALING 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); Endiff; 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 ATEMPBYDEPT 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_apption for caller. * / raise_Application_ERROR (-20001, sqlcode || '|' || sqlerrm); end usp_getempbypt; on it In the code, we reference two new constants:
Debug log_path defines debugging switch parameters and file path parameters. In this, we need to modify the package we need to define: create or replace package pkg_const as type ref_cursor is Ref cursor; debug constant boolean: = true; log_path constant varcha2 (256 : = '/ usr / tmp / db'; END PKG_CONST; In the beginning of the code block, the name of the input parameters and the value of the value is logged into the log file, and the output parameters will be output at the normal exit portion of the code block. The name and value are also recorded, and if the program is absent from normal exit, write the error code and the error message to the log file in the processing section of Exception. This information can generally use this information to find most of the errors that appear in the program run.
Note: If the type of the parameter is CURSOR, it is not possible to write a log file in the stored result set, at which point the log information recorded in the call program, the following code is analyzed below: FOPEN () The function uses a given path and file name, new file or open existing file, depending on the last parameter, when using 'a' as a parameter, if a given file does not exist, then this file name Create a new file, and open the 'W' mode, return a file handle.
The above code establishes log files in the sky, and the log files between different stored procedures are the advantage of being able to trace the call sequence and logic of the program by viewing the log file. In practical applications, specific analysis should be used depending on different needs, and more complex log files can be used to generate strategies. The PUT_LINE () function is used to write the characters to the file and add a newline in the end of the string. If you do not want to change, use the PUT () function.
The new_line () function is used to generate a specified number of blank lines, and the above-mentioned modification of the file is written in a buffer, performing FFLUSH () to write the contents of the buffer into the file, when you want to be closed in the file This function is called when you need to read the changed change.
The is_open () function is used to determine the status of a file handle. Finally, I will remember to turn the open file, call the fclose () function, and should add this statement to the Exception process, prevent the process to leave not normal exit Closed file handle.
Capture violation
In PLSQL, you can find which type of error happened by two built-in functions, SQLCODE and SQLERRM, and obtain detailed Message information, SQLCode returns an error between -1 to -20000 when the internal violation occurs. No., but there is an exception, only one positive number 100 is returned when internal violation no_data_found occurs. When a user-defined violation occurs, SQLCode returns 1 unless the user uses PRAGMA EXCEPTION_INIT to bind a custom illegal error number. SQLCode returns 0 when no violation is thrown out.
Below is an example of a simple capture violation:
Declare I Number (3); Begin Select 100/0 I from Dual; Exception When Zero_diVide the ... end;
In Exception, we use the Others keyword to capture all unclear specified violations and record the log processing, and we must throw the violation to the call again after doing these processing, call the function: raise_Application_ERROR (), This function returns a user-defined error number and error message to the calling program. The first parameter specifies an error number, defined by the user, but must be defined between -20000 to -20999 to avoid definition Exception with Oracle inside. The error number conflict, the second parameter needs to return a string, here we use it to return the error number and error description we captured above. Note: The violation thrusted through the RAISE_APPLICATION_ERROR () function is no longer the internal violation that is captured inside the block, but is defined by the user himself.
