Guideline

Guideline, Oracle PL/SQL Guideline Oracle PL/SQL Coding

WrittenbyJerryMattsson This document was written some time ago but have not been published anywhere, so if anyone can use it, that is fine. I revised it and put some new comments in here and there. The contents is not version specific to the database but covers only PL/SQL version 2. The purpose of this document is to serve as a practical guide or as a standard for programmers and system designers, writing, generating or specifying program modules that access an Oracle Rdbms. The conventions stated here should be applied as far as possible regardless of which tool or environment that are used to create or execute PL/SQL code. This document may be included in your system design guidelines or standards, as long as it is included as is, or the reference is clearly stated. Your own addition could be placed in an addendum or a preface, stating which parts that are used, changes to the parts used, or the parts that you want to exclude from your design guide. Suggestions and questions are welcomed, e-mail: [email protected]. ORACLE, Oracle*Forms. SQL* Forms, SQL*Plus, and SQL*ReportWriter are registered trademarks, and CASE*Generator, and PL/SQL are trademarks of Oracle Corporation. Reformatted Dec-2002. I hope it looks a little bit better.

Contents: •

1 General about PL/SQL

•

2. Naming Conventions

•

2.1 Naming Summary.

•

2.2 Naming Packages

•

2.3 Naming Procedures and Functions

•

2.4 Naming Variables

•

2.5 PL/SQL source code

•

2.5.1 File Names

•

2.5.2 Source and code documentation

•

3. The PL/SQL Language

•

3.1 Coding Style and Appearance

•

3.1.1 General

•

3.1.2 Layout

•

3.1.3 Type declarations

•

3.1.4 Declare and use explicit cursors

•

3.2 Stored Procedures

•

3.2.1 General

•

3.2.2 Transaction handling

•

3.2.3 Reusing SQL-code in PL/SQL

•

3.2.4 Locking

•

3.2.5 Security

•

3.2.6 Performing I/O

•

3.3 Triggers

•

3.4 Error Handling and exceptions

•

3.4.1 General

•

3.4.2 Predefined exceptions

•

3.4.3 Declaring and using exceptions

•

3.4.4 Unexpected user defined Oracle exceptions

•

3.4.5 Expected user defined exceptions - Oracle related.

•

3.4.6 User defined exceptions, non Oracle related.

•

3.4.7 Range of user defined exception codes.

•

3.5 Common error handling routine.

•

3.5.1 Sample Message table

•

3.5.2 Sample message routines

•

4. Utility packages

•

4.1 Simple Client/server Server

1 General about PL/SQL SQL is a set oriented language and has very limited procedural capabilities even if a lot of tasks may be and should be solved using pure SQL. A lot of things are possible to achieve with SQL, but we are more procedural in our thinking, than

set oriented. When things gets a little complex, breaking things down in procedural code often gives us a better understanding ( and code ) than thinking in terms of sets and set functions. If a problem is solved using both approaches, the SQL solution is often more efficient than the procedural equivalent. Do not try to solve your design problems with simple SQL-code and a lot of procedural code, try to solve it the other way around. In most cases this will give better performance, a more stable, more secure system and have a better code structure. Programs that access an Oracle database must use SQL. This can be done in a lot of ways, with 3GL-languages like C/C++, Fortran, COBOL. It can also be done using interfaces like ODBC or JDBC, by using a number of languages that can call these database access methods, or it may be done through a tool written in any language using one of these interfaces. PL/SQL was introduced by Oracle to enable procedural functions within the database. It was planned as a language extension to SQL. It was thought of as a language to be used to solve more complex database operations than could easily be done with only SQL. The purpose was to make it possible to send several SQL-functions to the database in one call, making complex transactions execute more efficiently with fewer calls to the database. Instead of sending a number of separate SQL instructions to the database we now had the possibility to send a complete program and a complex transactions to the database in one call. This gives performance improvements especially in OLTP applications and in network and client/server situations, where it is desirable to minimize the interaction between the client application and the database. Even if the database today can host and use an internal JAVA Machine, PL/SQL is still a very database near language and very well suited and efficient to perform many tasks in the database. PL/SQL is a language that is simple to use and the code is compact. It looks like a general, ADA/Pascal type of language with extension to embed and use the SQL. It is used to create stored procedures, function and triggers. PL/SQL can be regarded as and used in the same way as "pure SQL" and normally from the same tools and environments as SQL can be used. Oracle Rdbms and some Oracle tools contains a PL/SQL interpreter and a "bytecode-compiler". It is also used as a stand alone procedural engine in tools made by Oracle, such as Oracle*Forms, and Oracle*Reports. Some older tools also use PL/SQL version 1. The language has developed for a couple of years, and are now more flexible and contains more functionality than is easily grasped. It is also used in a more general way by many, than might has been intended from the beginning. PL/SQL has also been cloned in PostgreSQL which has some differences but are still so close that an application move could be done with not too much effort.

2. Naming Conventions 2.1 Naming summary. Type Packages Procedures Functions Exceptions

Format CUST023 GenAccno GetAccno NO_ACCNO

Comment Same as Module / Programs < 8 char's Descriptive short name Descriptive short name Descriptive short memo technical name

Cursor Names

PL/SQL Tables Record structures Parameters Local variables

cr_name+number or cu_name+number cr_pers01 t_name r_tab-alias-name p_name l_name

Prefixed with: "cr_" for a read CURSOR. cu_for a read / UPDATE CURSOR. Second part should be table short name and a 2 digit sequence number. Prefixed with "t_" Prefixed with "r_" Prefixed with "p_" Prefixed with "l_"

If needed, specify other types of local variables as follows: Constants Index variables Global variables

c_name i_name g_name

Prefixed with "c_" Prefixed with "i_" Prefixed with "g_"

2.2 Naming packages Naming of packages should be done in conformance to your programming naming convention or conforming to the development tools that you are using. Package names should be composed of application name (short 2-4 characters ) and a running number or a short functional abbreviation, making the length of the name less or equal to 8 characters. Package: CUST023, CUSTHDL, CUSTMNT etc. All packages should be uniquely named in the system. Public synonyms should be created for all package. If we have a package CUST0112 in the system CUST, owned by the user CUSTSYS, then we create a public synonym with: Create public synonym CUST023 for CUSTSYS.CUST023; 2.3 Naming procedures and functions Procedures and functions in packages should be given descriptive names. You may define both a longer functional name and an implementation name ( as in Oracle's Case Designer products ). The implementation name should use as few special characters as possible. Try to pick descriptive shortenings and identify each part with uppercase letters, even if PL/SQL is non case-sensitive. Avoid underscores and special characters. Procedures in the CUST023 package could be: GetTax, CalcNetIncome A program that wanted to call one of there procedures should then call:

CUST023.GetTax(parameters); 2.4 Naming Variables Variables names and parameters should be consistently formed. Cursors should be prefixed by: "cr_" for read only cursors, and "cu_" for "read for update" cursors. and a number should be appended to the name. E.g. cu_sale01 Record structures should be prefixed by "r_". Table structures should be prefixed by "t_". E.g. t_Ename Input / output parameters should be prefixed by "p_" p_Item VARCHAR2(12); Local variables in general should be prefixed by "l_" as in l_item VARCHAR2(12)

TabType1;

Variables in different functions or blocks should if necessary, be given different names to avoid misunderstanding. If needed a further special identification of variables could be made: Index- Global- variables and constants could be prefixed with "i_", "g_" and "c_" respectively for local, internal, global, constant E.g. c_code NUMBER(4); g_code NUMBER(5);

etc.

2.5 PL/SQL source code 2.5.1 File names PL/SQL code does not necessary have to exist anywhere except in the database. But creating and managing it is much easier to do with standard editors and programming tools storing and doing version control with standard file based tools. PL/SQL source code files should be named as the program or module name, consisting of the short name of the application and a number, in total not more than 8 characters long, and suffixed with either ".pck", ".sql" or "pls" depending on the purpose of the file and in conformance to the tools used. 2.5.2 Source and code documentation All PL/SQL code should contain standard header information about: who created the what has been done with

code, it,

why it was done and when this happened. It should be version and time stamped and comments can be done in C- or ADAstyle and syntax. Short sample header information: /* Title/File: CUST0012.PLS Version: 1.0 Description: This program is intended to be used for ....... Author: Jerry Mattsson / Xyz Ab. Creation date: 2/5 -97 */ /* Modification information, added at time of modification Modified date: Modification purpose: Modified by: */ or in ADA style: -- Title/File: CUST0012.PLS -- Version: 1.0 -- Description: This program is intended to be used for ....... -- Author: Jerry Mattsson / Another XYZ company -- Create date: 2/5 -97 When generating or regenerating code with Oracle's code generators, the code will be documented in a similar way. It will use the text information entered in the module text windows, Description, Notes and Modification History. Be sure to maintain this text information in the case tool before generating new code or when reverse engineering code.

3. The PL/SQL Language 3.1 Coding Style and Appearance 3.1.1 General PL/SQL should be written in a structured way and follow a general coding practice. Even if there exists a "GOTO" statement, these should not be used. Comment and explanation of the code should be written, write the purpose of the code, not how you've written it. Calls to procedures should always be made with bind variables and not with constants or strings. E.g. l_code := 'TST'; l_result := CUST.ChkCode(l_code); 3.1.2 Layout

PL/SQL code should have a layout so that it is easy to read. Code should be grouped logically and you can separate different parts from one other by leaving a blank line, or by inserting comments. SQL and PL/SQL code should in general be written in lower case, except for keywords in SQL and PL/SQL. PL/SQL keywords like: DECLARE, BEGIN, END, EXCEPTION, RETURN, EXIT, IF, ELSE, ELSIF, END IF, WHEN, WHILE, FOR, LOOP, and data types ( VARCHAR2(20) ) should all be written in The statements:

upper

case.

LOOP, IF ... END IF FOR ... END LOOP FOR, LOOP, WHILE, sub-blocks ( BEGIN ... END ) should be indented to make the code easy to read, and show the level, that the code applies for. Indentation with 3 spaces should normally be applied. Comments should be inserted into the code to help other developers to understand it. This is a modified form of sample3.sql. It illustrates block structure and scope rules. An outer block declares two variables named X and COUNTER, and loops four times. Inside the loop is a sub-block that also declares a variable named X. The values inserted into the TEMP table show that the two X's are indeed different. DECLARE x NUMBER := 0; counter NUMBER := 0; BEGIN FOR i IN 1..4 LOOP x := x + 1000; counter := counter + 1; INSERT into temp ( col1, col2, col3 ) VALUES (x, counter, 'in OUTER loop'); DECLARE /* start an inner block */ x NUMBER := 0; -- this is a local version of x BEGIN FOR i IN 1..4 LOOP x := x + 1; -- this increments the local x counter := counter + 1; INSERT into temp ( col1, col2, col3 ) VALUES (x, counter, 'inner loop'); END LOOP; END; /* end of inner block */ END LOOP; END; 3.1.3 Type declarations

Data type declarations should be made with %ROWTYPE or %TYPE whenever possible. This eliminates much of the re-coding needed, if table definition changes. For cursor-referenced variables try to use the %TYPE declaration for variables like: DECLARE l_custno customer.custno%TYPE := 0; BEGIN or DECLARE /* Read table valid_number in descending order */ CURSOR cr_vnum1 IS SELECT n1.next_number FROM valid_numbers n1 ORDER BY n1.seq desc; /* Read table valid_numbers in ascending order */ CURSOR cr_vnum2 IS SELECT n2.next_number FROM valid_numbers n2 ORDER BY n2.seq asc; l_num1 valid_numbers.next_number%TYPE; l_num2 valid_numbers.next_number%TYPE; BEGIN or DECLARE CURSOR cr_emp is SELECT * FROM emp; r_emp emp%ROWTYPE; r_dept dept%ROWTYPE; BEGIN IF r_emp.comm is null THEN /* do something smart with ............*/ END IF; END; 3.1.4 Declare and use explicit cursors Programs both in PL/SQL and other environments should be written in a way to minimize the number of simultaneously used cursors. This should be done to minimize i/o and memory usage, and to increase the effect of the SQL-caching mechanism and to improve performance in general. To access data in the database, use explicit cursor declaration and not implicit cursors. Implicit cursors: SELECT ename INTO l_ename FROM emp;

Explicit cursor: DECLARE CURSOR cr_emp1 is SELECT ename FROM emp; ..... FOR r_emp in cr_emp1 LOOP IF r_emp.comm is not null THEN UPDATE emp SET comm = null WHERE current of cr_emp1; END IF; END LOOP; 3.2 Stored Procedures 3.2.1 General A procedure is a program made of PL/SQL commands and SQL statements that can be executed by the database. It may have one or more input and output variables. A function is identical to the procedure except that it always have one return value returned to the calling program. PL/SQL is treated in a very similar way as SQL statements in the database. If you send a text containing a PL/SQL block to the database it will be loaded into the SGA ( System Global Area ), analyzed, decomposed, compiled and executed. This is known as execution of an anonymous PL/SQL block. If this code is compiled by the database and stored in the database, the same code is a stored and "pre-compiled" procedure. The term "stored procedures" is used as a term for procedures, functions and packages, that are pre-defined, compiled and stored in the database. When a stored procedure is called, the database will: •

load the procedure ( into the SGA - the shared pool cache ) if not already there,

•

resolve the SQL access path,

•

check security rules, and

•

execute the procedural code and the access statements.

If any of the underlying objects ( called procedures, tables etc. ) has changed, the procedure will be marked invalid and it will be recompiled before execution. Things that have to be considered carefully in the design of stored procedures are: •

Security.

•

Dependencies.

•

Performance.

A package consists of declarations and one or more procedures and functions. It is divided into a public part and a private part. The public part contains the declaration of the callable procedures and objects that may be used outside the package. The private part contains the actual code, and procedures used only in the package. The public part is seen by users that may execute procedures in the package. Procedures and functions should be part of a package and should not be declared as separate or stand-alone procedures. It would become unmanageable in the end to keep track of thousands of separate procedures and all the dependencies among them. Procedures and functions should therefore be declared and functionally modularized into packages. Both the package declaration and the body should be documented. The package specification should be shortly commented and the header information should contain information for the user of the package: Title, version, short description, author, dated and usage. Create or replace PACKAGE CUST0022 as /* Title: CUST0022.PLS Version: 1.0 Author: Jerry Mattsson Creation date: 2/3 -97 Description: These procedures and functions should be used for ... */ -- Usage/syntax: Username := GetUser( UserNumber ); function GetUser( p_usernum number ) returns varchar2; -- Usage/syntax: SetAppRole(ApplicationName); -- Sets the role for a specific application program. Returns true if success. function SetAppRole(p_AppName varchar2) returns boolean; END;

The body of the package should be commented for each procedure or function with create- and modification information, as well as descriptions and notes about the code. Stored procedure and packages should be kept as small as possible to conserve memory. When a procedure in a package is called, the whole package is loaded into the SQL-cache. The package size should therefore ideally be less than a couple of hundred rows. Group your declarations in a logical way so that exceptions, variables, procedures and functions are declared logically together. Collect common functions, SQL-declarations and common exceptions in separate dedicated packages. E.g.

Errorhandlingroutines. String manipulating Calculations and numeric Conversion functions etc.

routines. functions.

Grouping of functions should be done in a way that loads the least amount of code at execution time. The more general and common functions you are able to use, the more probable it is that you save shared pool memory, and that you reuse procedures already cached in memory. To achieve this a great deal of work has to be done, modularizing your functions and programs. To create small support packages with utility like procedures is probably a good way to cause reuse of code, used by many users. 3.2.2 Transaction handling

Stored procedures that might be called from other procedures or programs should never do a commit or a full rollback. This

is

not

allowed

!

The responsibility to decide if a transaction is successful and should be committed or if the transaction has failed in some part and should be rolled back, should be the responsibility of the "driving" or controlling program. If you call a procedure from your program, and this procedure does a commit, the commit implies that all changes made since the transaction begun, even the changes made in other procedures or in your program code, should be stored. If this is done, you are not able to roll back the transaction from your program, when the control returns to it, if required. The only obvious exception to this is if the controlling program is written as a stored procedure. Be careful and document the transaction behavior in your procedures if they do not follow this rule or are otherwise transaction dependent. Savepoints and rollback to savepoints is acceptable and could be used. E.g. BEGIN Savepoint before_UPDATE; UPDATE emp SET sal = sal + 10; DELETE FROM comm WHERE empno in ( SELECT empno FROM emp WHERE comm is null ); EXCEPTION when NO_DATA_FOUND THEN rollback to before_UPDATE; END; 3.2.3 Reusing SQL-code in PL/SQL To enforce the reuse of SQL statements, you may declare SQL-statements, used in stored procedures, in separate access-definition packages. This should be packages where we gather different SQL-statements for one or more tables, that are often used together. This can then make a library of cursors, that covers the

normal usage of the tables. Uncommon SQL statements or statements rarely used may be separated from the more common ones in separate packages. The same thing should be done with exception declarations. This will increase the reuse and caching mechanism in the SHARED POOL ( the SQL-cache ), and enhance performance and minimize memory utilisation. Declaring reusable SQL-functions. CREATE OR REPLACE PACKAGE CEU001 IS /* Cursors for User info and Common Error Utility */ CURSOR cr_errmsg1 ( p_errcode ) is SELECT t1.messcode FROM errmsg t1 WHERE t1.messcode like p_errcode AND langcode like substr(userenv('LANGUAGE'),3); CURSOR cr_errmsg2 ( p_errcode ) is SELECT t1.messcode||to_char(errcode,'0000')||':'||t1.messtxt FROM errmsg t1 WHERE t1.mess# = p_errcode AND langcode like substr(userenv('LANGUAGE'),3); CURSOR cr_usrinfo1 is SELECT user, sessionid, uid, sysdate FROM sys.dual; -- Here is the procedures used to UPDATE the CURSOR above: Procedure upd_errmsg1 ( errcode number, text varchar2); END; Using the declared SQL-functions. CREATE OR REPLACE PACKAGE CEU002 IS Procedure ErrH ( errcode in number, errtxt in varchar2 ) is -- Overlayed procedure #1 input code is number l_code varchar(32); l_msg varchar2(100); BEGIN IF errcode is null and errtxt is null THEN l_msg := 'CEU002.ErrH: Got error code null with no text!'; raise_application_error(l_msg,-20999); END IF; OPEN CEU001.cr_errmsg1 ( errcode ); FETCH CEU001.cr_errmsg1 INTO l_code; CLOSE CEU001.cr_errmsg1; END; END; 3.2.4 Locking. All code that performs manipulates data ( update and delete functions ) must explicitly be coded to handle locking conflicts. How this is handled should be documented in the package specification or in the procedure description. This is a more complete example to give you coding suggestions about locking functions and a little bit more.

CREATE OR REPLACE PROCEDURE SetGrooveWeight (p_lb_weight IN grooves.weight%TYPE) IS /* This procedure reads the three tables stand, roll_sets and grooves, takes only the used roll_sets and updates the usage sum of........ If the master record in stand is possible to lock, we wait if necessary on the child record....... the groove is updated via the rowid, fetched in .....*/ l_sum_weight NUMBER(7,1); l_event NUMBER(1,0); CURSOR cu_stand IS -- Master and detail SELECT, lock master, get rowid for detail SELECT s.stand, s.missing_weight, r.rowid radr, r.id, r.active_groove, r.acc_roll_weight FROM stands s, roll_sets r WHERE s.stand = r.amloc_stand(+) AND r.amloc_loc(+) = 'MILL' FOR UPDATE of s.missing_weight NOWAIT; CURSOR cu_groove ( p_id roll_sets.id%TYPE ) is SELECT g.weight, g.limit, g.id FROM grooves g WHERE g.roll_id = p_id FOR UPDATE of g.weight; -- if stand was locked and this has to wait, thats ok. RowLocked exception; pragma EXCEPTION_INIT(RowLocked, -0054); BEGIN FOR r_stand IN cu_stand LOOP l_event := 0; -- Test if stand exist and groove is used IF r_stand.id is not null and r_stand.active_groove <> 0 THEN l_sum_weight := 0; --set acc to zero -- loop for each master roll set and, check all grooves FOR r_groove IN cu_groove (r_stand.id) LOOP IF r_groove.id = r_stand.active_groove THEN IF r_groove.limit > r_groove.weight AND r_groove.limit < (r_groove.weight + p_lb_weight) THEN l_event := 1; END IF; l_sum_weight := l_sum_weight + p_lb_weight; -- set weight on the groove with in parameter value UPDATE grooves g SET g.weight = (g.weight + p_lb_weight) WHERE current of cu_groove; -- using CURSOR END IF;

l_sum_weight := l_sum_weight + r_groove.weight; END LOOP; -- When all grooves is done, UPDATE the master UPDATE roll_sets r SET r.status = 'U', ru.acc_roll_weight = l_sum_weight WHERE r.rowid = r_stand.radr; -- using rowid fetched earlier END IF; -- set the master, master status with new value UPDATE stands s SET s.missing_weight = l_event WHERE current of cu_stand; -- using CURSOR UPDATE END LOOP; EXCEPTION When RowLocked THEN raise_application_error(-20322,'Unable to lock record on table stand'); END;

3.2.5 Security. Stored procedures and triggers always execute in the owners schema The user who has execute rights on a package, gets all the rights that is needed, and was used by the creator, when the procedure executes. The user does not have to have any rights to the tables and views that are read or updated in the procedure, only execution right on the procedure or package. 3.2.6 Performing I/O. Stored procedures and triggers are executed in the server, by the RDBMS kernel code. This means that input/output operations from within a stored procedure executing in the server to a client, is not a thing that is easy to do. Input/output operations are much easier to handle in the front-end tool where you can open and communicate with files and terminals in different ways, and this is normally exactly what this tools and programs are made for. While developing and debugging stored procedures, you do want to do some i/o to trace and understand the behavior of the code. Use a front end tool like the "Oracle procedure builder" that contains functions to help you manage and trace your code. Oracle's tools are currently equipped with PL/SQL version 1 and the database is using version 2, and this might cause some problems but it is still useful for it's purpose. To get information on what happens inside the server or to trace the code execution you may: Writing information to tables, Writing to and monitor the "rdbms pipes" stream, Using the dbms_output package Writing to files with utl_file package.

This is a couple of ways to perform i/o from within a procedure. Remember that if you write information to a table, this writing is a part of a your transaction and might be rolled back or committed dependent on the program code. The database is delivered with a number of system packages. One of them is the DBMS_OUTPUT package. This package contains procedures to send strings to some front-end tools SQLPLUS ( or old Sqldba/Svrmgr ). This allows you to "print" messages from within a procedure and this function can therefore be used to debug the code when it is executing in the kernel. One effect using this type of function is that the output buffer used to send information is not flushed just when you write to the output function but when the database has terminated an internal database operation and this often means that the output comes after your procedure has terminated. So limit the use of this package to simple trace-functions and should be the only type of usage of this package. It should not normally be used in the application code and make the execution dependent on this because of it's limited functionality. You may use the output functions like this: dbms_ouput.putline('CUST00022.GetUser '||to_char(user_number)); The code could be left for future trace of the execution. It does not have any impact on the procedure if the "serveroutput" function is not active. But if used general in your code you must include the procedure name in the text line, so that it is possible to identify the function writing the message to you. When enabling the output you enable all messages in all routines, you cannot enable these only for some selected procedures. You switch on or off the dbms_output functions by issuing the command: Set serveroutput on, Set serveroutput off in the SQLPLUS environment. The messages will then be buffered and sent to your front-end session from the database when the code is executing. It is not possible to receive this data in any other tool. Procedure ErrH ( errcode in varchar2, errtxt in varchar2 ) is l_cname varchar2(32); l_errtxt varchar2(78); l_errno number(8); BEGIN DBMS_OUTPUT.PUT_LINE('CEU002.ErrH: Code = ' || errcode ||''|| errtxt); IF errcode is null THEN IF errtxt is null THEN -- We got nothing, no code, no text! raise_application_error(-20999,l_errtxt ); END IF; l_errtxt := 'CEU002.ErrH: Got error code null with no text!'; raise_application_error(-20999,l_errtxt); -- We got no text, no code, END IF; END; -- End of specially treated error codes

3.3 Triggers

Triggers should be created and used with a word of caution. The trigger code is loaded, analyzed and compiled when you cause the trigger to fire. The code is checked and might be compiled in runtime and will, if the code is large, consume considerable CPU power. The code should therefore be as small and as efficient as possible. A trigger may also cause extensive i/o if the trigger contains SQL-statements that access one or more tables when executed. The trigger is supposed to execute for each row in a table that it is applicable for, and if we have a 1000 row table with a trigger that reads and checks data in one other table on an update-statement. This will case 1000 reads in the table, when updating all rows in the trigger table, hopefully very efficient reads and not full table scans. Therefore

be

especially

cautious

with:

Triggers that access or search through data in other tables ( i/o performance ), Triggers that manipulate data in other tables ( locking, transaction consistency, performance ). Triggers that are executed for each row ( all above ).

The safest types of triggers, in the aspect of performance, are triggers that only: a) verifies data on the record you are manipulating or b) triggers that make computations or supplies other derived values to the same record. Try to write triggers that contain as little and as efficient SQL-code as possible. If none, you are on the safe side. Also consider the fact that triggers might be executed even when you don't expect it to. Oracle*Forms for example, updates columns in the table even if the fields have not been touched or changed in the screen. You might therefore need to check old and new values in your trigger code. The example below uses another utility-package that sends alerts ( signal changes ), that occurred in the database to front-end applications. Alerts are transaction dependent, and is sent at the time a commit is done in the database. Waiting for an alert holds ( locks ) a server-process to the user while or because it is waiting. Use dbms_alerts with care. To avoid multiple signaling, use code like the MU001 and MU002 programs below. It handles the signaling process from a triggers. It uses a table to be able to define the relations, between different applications and the signals that they should listen to. rem Created 24/4-97 by Jerry Mattsson rem Created as sample, makes tables, triggers and package to handle terminal rem notification via alerts. The application ( forms ) registers for alerts with a

rem procedure that reads all events that it should register on, FROM a table. rem This is the definition of the table used by an application at startup. rem It is read to find all signals that it will wait for. create table ProgSignal ( SigName varchar2(32) not null constraint PRSIG_CHK_SIGNAME1 check ( upper(Signame)=Signame ), ProgName varchar2(32) not null constraint PRSIG_CHK_PRNAME1 check ( upper(ProgName)=ProgName ); Create unique index ProgSignalPkI on ProgSignal ( ProgName, Signame ); rem Recreate with primary key constraint ! This part of the code declares the functions that are needed by the listener and the sender. These functions are general and do not have to be changed for different frontend applications or send functions, triggers or programs. Some of these procedures are only replacement for the utility procedures supplied by Oracle, but it is a safe way to protect our code from future changes to these functions. CREATE OR REPLACE package MU001 as -- Message utility / written apr-97 / JM -- Used to send and receive alerts FROM applications and triggers. -- The applications registers the events by calling SigRegAll, that reads a signal table -- and registers all signals for that application. -- The trigger signals the events with Signal, and supplies the event name. procedure SigRegAll (ProgName in varchar2); procedure Signal (EventName in varchar2); -- When the application switches context and is done, it should clean -- up by calling: procedure SigDelAll; -- This functions waits for any of the registered alerts. -- It returns the name of the alert. function SigWaitAny ( TimeOut in number default 3 ) return varchar2; -- This function waits for a named event. function SigWait1 ( SigName in varchar2, TimeOut in number default 3) return number; -- Global variables l_SigName varchar2(32); l_message varchar2(100); l_status number(2) := 1; l_time number(2) := 3; END;

Then we have an example of a triggers using the defined package. We need the row trigger to send the signal and to accumulate the signal names in the table, and we need an after statement trigger to reset the table after the statement is done and the transaction is performed. The row trigger: CREATE OR REPLACE trigger t_table_tr1 after UPDATE or INSERT on t_table for each row DECLARE l_signame varchar2(32) := null; -- For each row call proc. to build signal list and send signal once only BEGIN IF :new.test_value between 1 and 4 THEN l_signame := 'VALUE_ALARM'; ELSIF :new.test_value > 8 THEN l_signame := 'OVERFLOW'; ELSE l_signame := 'NORMAL'; END IF; MU001.AlertPgm(l_signame); END; The statement trigger: CREATE OR REPLACE trigger t_table_tr2 after UPDATE or INSERT on t_table BEGIN -- Clean up after statement trigger MU001.SigClean; END; Here is the application side code or

a

part

of

it.

In the application ( E.g. your C-code or Oracle*Forms ), the initialisation process should do a call to subscribe on it's alerts: -- call to be done in the initialization-procedure. MU001.SigRegAll(:system.current_form_name, no_of_alerts_gotten); -- Initialize the timer to trigger the alert check InitTimer('FORMSYNC,3); When the timer expires the function SigWaitAny should be called, to wait for a signal. This should wait for a short time to allow the user to navigate and keep the control in the application. AlertFound := MU001.SigWaitAny ( :Timeout ); -- Test if alert found If AlertFound is not null THEN -- Perform the form screen UPDATE functions ...... When the application is done or leaves the canvas/page where the timer is supposed to do the update, the cleanup-procedure ( SigDelAll ) has to be called.

This is the package code that contains the code that does the work for both sides, the database and the application: CREATE OR REPLACE package body MU001 as PROCEDURE SigRegAll ( ProgName in varchar2 ) is -- Registers on alerts on startup of a form. -- Cursor to read EventList table. CURSOR EventList ( p_pname varchar2 ) is SELECT P.signame FROM ProgSignal P WHERE P.Progname like p_pname; BEGIN -- Reads the event table to register program for all it's signals FOR SigRec in EventList ( ProgName ) LOOP IF EventList%NOTFOUND THEN dbms_output.put_line('MU001,SigRegAll: No Signal found'); END IF; l_SigName := SigRec.signame; dbms_output.put_line('MU001,SigRegAll:Signal '||l_signame||' registered'); dbms_alert.register(l_SigName); END LOOP; END SigRegAll; FUNCTION SigWaitAny ( TimeOut in number default 3 ) RETURN varchar2 is BEGIN dbms_alert.waitany(l_SigName, l_message, l_status,TimeOut); dbms_output.put_line('MU001,SigWaitAny: Signal status '|| to_char(l_status)||' returned'); IF l_status = 0 THEN -- got an alert dbms_output.put_line('Signal'||l_signame||' gotten'); RETURN l_signame; ELSE RETURN null; -- timed out END IF; END SigWaitAny; procedure Signal (EventName in varchar2) is -- replaces the dbms_alert standards function, signals an alert to waiting processes BEGIN dbms_alert.signal(EventName,null); END Signal; procedure SigDelAll is BEGIN -- replaces the dbms_alert standard function, deletes all alerts a program has registered dbms_alert.removeall; END SigDelAll; function SigWait1 ( SigName in varchar2, TimeOut in number default 3)

RETURN number is -- replaces the dbms_alert standard function, Waits for one specific alert BEGIN l_SigName := SigName; dbms_alert.waitone(l_SigName, l_message, l_status, TimeOut); RETURN l_status; END SigWait1; END; This part is the trigger code. It accumulates the signals used in a process global table. This means that if a large update is done, for each new signal, the signal name is stored that in the process table and then send the signal. Next time the same event occurs on the next row, the table is scanned and the signal will not be sent again. rem This package is used by triggers that want to alert applications. rem When the trigger is created the events that you want to signal is defined. rem The names must be inserted into the ProgSignal table, one for each rem form and event that should be signalled. CREATE OR REPLACE package MU002 as -- Created 25/4/97 /JM -- Used from table triggers to notify programs on events. -- This is split into two parts to check and make certain -- that a signal is only sent once on a multi row UPDATE TYPE SIGTABTYPE is TABLE of PROGSIGNAL.SIGNAME%TYPE index by binary_integer; t_Signals SIGTABTYPE; t_Empty SIGTABTYPE; g_MaxSignals BINARY_INTEGER := 0; procedure AlertPgm ( EventName varchar2 ); procedure SigClean; END; CREATE OR REPLACE package body MU002 as procedure AlertPgm ( EventName varchar2 ) is l_found BOOLEAN := false; i BINARY_INTEGER := 0; BEGIN FOR i in 0..g_MaxSignals LOOP IF t_Signals( i ) = p_EventName THEN l_Found := TRUE; EXIT; END IF; END LOOP; EXCEPTION when NO_DATA_FOUND THEN g_MaxSignals := g_MaxSignals + 1; t_Signals( i ) := p_EventName; dbms_alert.signal( EventName, NULL ); END AlertPgm; Procedure SigClean is BEGIN

t_Signals := tab_Empty; g_MaxSignals := 0; END SigClean; END; 3.4 Error handling and exceptions 3.4.1 General There are different types of exceptions or errors that we can identify: •

Predefined PL/SQL exceptions.

•

Expected user defined Oracle exceptions.

•

Unexpected user defined exceptions, Oracle related.

•

User defined exceptions, non Oracle related.

You should always declare exceptions for errors that you consider as "possible or normal errors". This is errors that the procedures are supposed to handle or act upon, and the program should therefore take some actions according to it. The remaining errors are considered as abnormal and the error condition should ripple out to the driving program. The driving or controlling program should then take actions or report the problem to the user, the user program or to a log. PL/SQL used in stored procedures may NEVER contain exception handling like: ..... WHEN OTHERS THEN null; or ..... WHEN OTHERS THEN .... "an action that do not raise an error". Think of what will happen if the RDBMS returns ORA-00942, table or view does not exist, or some other serious error, and your program ignores that. If ... WHEN OTHERS.... is used it must at least contain .... raise_application_error(Some_error_code, 'And Some message'), and this should then be propagated to the correct program level for action. 3.4.2 Predefined exceptions SQL statements in your programs should have exceptions declared that are associated with the outcome or the errors that the statement may cause. This should be used to handle expected SQL-return codes. We can use a number of standard exceptions like the NO_DATA_FOUND, that should take care of the situation when no data was retrieved from a directly executed SQL-statement, because that was one thing that we expected could happen. Predefined exceptions that you can use are: TOO_MANY_ROWS, VALUE_ERROR, NO_DATA_FOUND,

CURSOR_ALREADY_OPEN, INVALID_CURSOR, INVALID_NUMBER,

DUP_VAL_ON_INDEX, LOGON_DENIED, NOT_LOGGED_ON, PROGRAM_ERROR, TIMEOUT_ON_RESOURCE, TRANSACTION_BACKED_OUT, ZERO_DIVIDE This is errors that may occur in most procedures and the consequences of these should always be considered. The exceptions TOO_MANY_ROWS and NO_DATA_FOUND will not occur if all SELECT-statements are declared as explicit cursors. 3.4.3 Declaring and using exceptions Here is an extract from a package, fetched from a database utility package that declares and uses exceptions. This is the package specification that holds comments and information on what errors that are generated by the package. CREATE OR REPLACE package dbms_describe is -- This package is used to describe the arguments of a stored procedure. -- The client specifies an object name and describe returns a set of -- indexed tables with the results. -- PROCEDURES AND FUNCTIONS procedure describe_procedure (object_name in varchar2, reserved1 in varchar2, reserved2 in varchar2, radix out number_table, spare out number_table); -- Exceptions: -- ORA-20000 - A package was specified. Can only specify top-level -- procedure/functions or procedures/functions within a package. -- ORA-20001 - The procedure/function does not exist within the package. end; Here is the body of the procedures and the code that defines and names the error that we want to handle, and the causes that we want to generate the declared errors. CREATE OR REPLACE package body dbms_describe is procedure describe_procedure (object_name in varchar2, radix out number_table, spare out number_table) is NOT_EXIST_OR_NO_PRIV exception; pragma EXCEPTION_INIT(NOT_EXIST_OR_NO_PRIV, -6564); PROC_NOT_IN_PKG exception; pragma EXCEPTION_INIT(PROC_NOT_IN_PKG, -6563); BEGIN BEGIN dbms_utility.name_resolve(object_name, 1,sch, part1, part2, db, typ, objno); EXCEPTION when not_exist_or_no_priv or proc_not_in_pkg THEN raise;

WHEN OTHERS THEN raise_application_error(-20004,'syntax error attempting to parse "'|| object_name ||'"', keeperrorstack => TRUE); END; -- if the translated object is local THEN query the local dictionary IF (part1 is not null and part2 is null) THEN raise_application_error(-20000,'ORU-10035: cannot describe a package (' || object_name ||'); only a procedure within a package'); END IF; END; END; 3.4.4 Unexpected user defined Oracle exceptions This types of exceptions are mostly system oriented errors. All the things that we can anticipate could happen, will probably happen and should be handled by some exception. If the error is a system oriented error it should probably not be trapped in the procedure that caused the error, but it should be trapped by the controlling program. The transaction should normally be rolled back, and the error reported to a log-file, and the program should exit. E.g. transactions that are very large, can result in storage errors or snapshot errors and are therefore necessary to declare in this type of transaction program. Pragma Exception_init(Old_Snapshot,-1555); ... EXCEPTION when Old_Snapshot THEN .... Out of space ...... This are some typical error in this category that must be handled: 0060 deadlock detected while waiting for resource 1002 FETCH out of sequence 1535 space quota exceeded for tablespace 'name' 1547 - failed to allocate extent of size 'num' in tablespace 'name' 1555 snapshot to old (rollback segment too small) 1562 - failed to extend rollback segment (ID='num') 3.4.5 Expected user defined exceptions - Oracle related. This is errors that you could consider as errors in your data. E.g. when you delete data from a table and get No_Data_Found, and this was not the expected outcome of the operation, then your code could look like this: Delete_cust_procedure .... DELETE FROM customer WHERE custno = l_custno; IF sql%notfound THEN raise_application_error(-20201,'XYZ:Customer does not exist.');

END IF; The calling procedure .... DECLARE No_cust_Exist EXCEPTION; pragma exception_init(No_cust_exist, -20201); l_custno number(9); BEGIN .... Delete_Cust_procedure( l_custno ); .... EXCEPTION when No_cust_Exist THEN .... END; The Raise_Application_Error call will terminate the procedure where it is raised, rollback the statement and return the error number to the calling procedure or program. Typical errors in this category: 0001 unique constraint ('table.column') violated 0051 time-out occurred while waiting for resource 0054 - resource busy and acquired with NOWAIT specified 1400 - primary key or mandatory (NOT NULL) column is missing or NULL during insert 1407 - cannot UPDATE mandatory (NOT NULL) column to NULL 2290 check constraint ('str.name') violated 2291 - integrity constraint ('str.name') violated - parent key not found 2292 - integrity constraint ('str.name') violated - child record found 1800 - 1898 Date function errors 3.4.6 User defined exceptions, non Oracle related. This is your own private names on errors that you want to use in your code: DECLARE No_More_Capacity exception; pragma ... BEGIN IF rec.capacity > l_MaxCapacity THEN raise NO_More_Capacity; ... EXCEPTION when No_More_Capacity THEN .... ... END; 3.4.7 Range of user defined exception codes. To declare our own application oriented errors, we can use the range -20000 to -20999. Oracle's utility packages uses some of the -20000 - 20099 errors. We should therefore decide and agree upon a specific and common set of error codes. The range of these should be -20100 and upwards.

Application specific errors used in a program structure could be declared in the range -20200 20299. E.g. Procedure CreateRange ( low number, high number ) is DECLARE ToMuch Exception; PRAGMA Exception_Init(ToMuch, -20201); BEGIN /* some code here */ /* some more code there */ EXCEPTION When ToMuch THEN raise_application_error(CE001.ERRH01,'CreateRange','TOO_MUCH'); END; 3.5 Common error handling routine. If the applications that we are creating ever should be made multilingual, the error messages should be made external to the programs and placed in message tables or files and not hard coded in our application code. If that is done, the messages should preferably be fetched from an error message table. This should be a "global" table and it should be generally agreed on. 3.5.1 Sample Message table LangCode varchar2(4) not null PK UK1 MessCode varchar2(25) not null PK Mess# number(10,0) not null UK1 MessTxt varchar2(75) not null Where MessCode is a short name equivalent to the message number but used for readability in the application, e.g. EMP_EXIST, NO_CUSTOMER_EXIST. Possible addition for multi application usage and support for error actions in the program would require columns like: Severity number(2,0) Application varchar2(12) Action varchar2(12) 3.5.2 Sample message routines Standard error handling packages should be set up to handle messages, and take specific actions. One application system should be created to maintain this and similar general information and functions, in a system support application. These common service routines should be created in it's own application and implemented in it's own schema. Suggested functions for message retrieval: CREATE OR REPLACE PACKAGE CEU002 IS -- Title/File: CEU002.PCK -- Created: Oct-94

-- Author: JM -- Common Error Utility functions. Procedure ErrH ( errcode in number, errtxt in varchar2); Procedure ErrH ( errcode in varchar2, errtxt in varchar2); -- These procedures gets a System specific error message in the users current language. Function GetCnstrName ( errtxt in varchar2 ) returns varchar2; -- This function extracts the constraint name FROM the error messages. Function GetErrm ( errcode in varchar2 ) return varchar2; Function GetErrm ( errcode in number ) return varchar2; -- These functions fetches the error message associated with the error code provided. END CEU002; These procedures are exemplified previously in the text, so this is only one of the overlay procedures. CREATE OR REPLACE PACKAGE BODY CEU002 IS Procedure ErrH ( errcode in number, errtxt in varchar2 ) is -- Overlayed procedure #1 input code is number Procedure ErrH ( errcode in varchar2, errtxt in varchar2 ) is l_cname varchar2(32); l_errtxt varchar2(78); l_errno number(8); BEGIN DBMS_OUTPUT.PUT_LINE('CEU002.ErrH: Code = ' || errcode ||''|| errtxt); IF errcode is null THEN IF errtxt is null THEN -- We got nothing, no code, no text! raise_application_error(-20999,l_errtxt ); END IF; END IF; -- This is Oracle errors, catch all special error codes --- Parent key not found IF l_errno = -2291 Then l_cname := GetCnstrName ( errtxt in varchar2 ); -- Get Constr.name IF l_cname is not null THEN l_errtxt := GetErrm(l_cname); END IF; END IF; -- End of specially treated error codes -- Error code is a character string l_errtxt := GetErrm(errcode); IF l_errtxt is null and errtxt is null and l_errno in not null THEN -- get the error l_errtxt := GetErrm(l_errno); END IF; IF l_errno < -20000 and l_errno > -20999 THEN raise_application_error(l_errno,l_errtxt); -- User defined error END IF;

raise_application_error(-20999,l_errtxt); END ErrH; function GetErrm ( errcode in varchar2 ) return varchar2 is -- Get user friendly error message FROM error table errtext varchar2(78); BEGIN FOR r_err in CEU001.cr_errmsg1 ( errcode ) LOOP errtxt := nvl(r_err.msgtxt, 'Message not found for '|| errcode ); END LOOP; RETURN(errtxt); END GetErrm; function GetErrm ( errcode in number ) return varchar2 is -- Get user friendly error message FROM error table errtext varchar2(78); BEGIN OPEN CEU001.cr_errmsg2 ( errcode ); FETCH CEU001.cr_errmsg2 into errtxt; close CEU001.cr_errmsg2; IF CEU001.cr_errmsg2%notfound THEN RETURN( 'Message not found for '|| to_char(errcode) ); -- could be recursive END IF; RETURN(errtxt); END GetErrm; Function GetCnstrName ( errtxt in varchar2 ) returns varchar2 is BEGIN -- gets the string within parenthesis, WHERE the constraint name should be DBMS_OUTPUT.PUT_LINE ('CEU002.GetCntstrName: '||errtxt); RETURN(substr(errtxt,instr(errtxt,'(')+1,instr(errtxt,')')-instr(errtxt,'(')1)); END; END CEU002; 4. Utility packages There are a number of utility packages. The dbms_pipe package contains routines to send and receive messages with database functions similar to the Unix pipe functions. This allows sessions to pass information between them if connected to the same database instance. Oracle pipes have a number o special properties, here are some: •

Pipes operate independently of transactions.

•

They also operate asynchronously.

•

Pipes are created the first time they are referenced, and they disappear when they does not contain any more data.

•

Pipe functions has a number of possible usage's:

•

User-written server-services. The service can be written in any languages that supports calls to stored procedures.

•

Pipe-services can be used to communicate to separate sessions which can perform transaction independent operations.

•

Post other processes to indicate that the data changed. The application would THEN read the data to get the current values.

Debugging. Triggers and/or stored procedures can send debugging information to a pipe. Another session can keep reading out of the pipe and displaying it on the screen or writing it to a file. 4.1 Simple Client/server Server Here is an example of a simple host-server using the pipe package. The server can be used to perform simple and uncritical jobs, like starting reports or batch programs on behalf of a user. rem Used to create package MS101 Simple client/server jobserver rem **************************************************** Create or replace package MS101 as -- Procedures and functions to start and get server jobs. -- Written mar-97 by Jerry Mattsson -- Procedure to send job request to server handler. -- Client sends request via the procedure procedure SQReq ( p_PgmId in varchar2, p_PgmType in varchar2, p_Params in varchar2, p_TimeOut in number default 45 ); -- Procedure to send commands to the server. procedure QCmd ( Cmd in varchar2, TimeOut in number default 45 ); -- Procedure to get job service information FROM service handler. -- called FROM MS101 server procedure GQReq ( p_Qid out number, p_username out varchar2, p_PgmId out varchar2, p_PgmType out varchar2, p_PrtDest out varchar2, p_PgmParams out varchar2, p_time out date ); END MS101; Create or replace package body MS101 as ------------------------------- B O D I E'S------------------------------/* Created: mar-97 JM. Changed: */ -- Body known variables MsgStr varchar2(250) := ''; PipeName varchar2(10) :='MS101';

l_UserName varchar2(32); l_prc varchar2(32); l_PipeStat number(3); l_PackType varchar2(10) := 'MSREQ'; l_Days_to_keep number(3) := 7; l_cmd varchar(30); l_time date; l_PgmId varchar2(32); l_PgmType varchar2(4); l_PrtDest varchar2(32); l_PgmParams varchar2(250); procedure TruncQueue ( days_to_keep in number ); procedure GetUsrPrt ( p_usr in varchar2, p_prt out varchar2 ); procedure PipeErrH ( LocProc in varchar2, Err_Code in number ); AppPackError exception; pragma exception_init(AppPackError,-20100); LockError exception; pragma exception_init(LockError,-0054); ----------------------------------------------------------------procedure GQReq ( p_Qid out number, p_username out varchar2, p_PgmId out varchar2, p_PgmType out varchar2, p_PrtDest out varchar2, p_PgmParams out varchar2, p_time out date ) is -- This procedure reads the request queue table in reverse request order -- It returns the command and userid to the caller and deletes the record /* Table layout is as the input parameters to this procedure. This information is inserted at request and deleted after 7 days def. */ CURSOR c1 is SELECT nvl(max(qid),0) + 1 FROM mmsqueue; BEGIN l_prc := 'MS101,GQReq: '; -- Message wait, default timeout is approx. 3 years. l_PipeStat := dbms_pipe.receive_message(PipeName); /* Message has arrived */ ------------------------------------------------------------IF l_PipeStat <> 0 THEN MsgStr := 'MS101, Pipe receive error: '||to_char(l_PipeStat); IF l_PipeStat = 3 THEN MsgStr := MsgStr || ' - Process interrupted'; END IF; IF l_PipeStat = 2 THEN MsgStr := MsgStr || ' - Wait request timed out'; END IF; raise_application_error(-20100,MsgStr); END IF;

-- - - Wait for message - - -dbms_pipe.unpack_message(l_PackType); -- - - Start processing - - -IF upper(l_PackType) not in ( 'MSREQ' , 'CMD' ) THEN MsgStr := ' MS101: Wrong type of package received!'; raise_application_error(-20100,MsgStr); END IF; -------------------------------------------------------------------IF upper(l_PackType) = 'CMD' THEN dbms_pipe.unpack_message(l_cmd); dbms_pipe.unpack_message(MsgStr); dbms_output.put_line('-- Received Command package --'); dbms_output.put_line('Issued by '||MsgStr); IF l_cmd = 'STOP' THEN raise_application_error(-20109,Msgstr); END IF; IF l_cmd = 'PURGE' THEN dbms_pipe.purge(PipeName); END IF; ELSE dbms_output.put_line('-- Received MSREQ package --'); -- MSREQ packs 6 elements in the package dbms_pipe.unpack_message(l_PgmId); dbms_pipe.unpack_message(l_Pgmtype); dbms_pipe.unpack_message(l_username); dbms_pipe.unpack_message(l_time); dbms_pipe.unpack_message(l_PgmParams); p_PgmId := l_PgmId; p_Pgmtype := l_Pgmtype; p_PgmParams := l_PgmParams; p_username := l_username; GetUsrPrt ( l_username, p_Prtdest ); OPEN c1; FETCH c1 into p_QId; IF c1%NOTFOUND THEN p_QId := 1; END IF; close c1; p_time := l_time; INSERT INTO msqueue ( qid, pgmid, reqtime, pgmparams, username, pgmtype ) VALUES ( p_QId, l_pgmid, l_time, l_pgmparams, l_username, l_Pgmtype ); TruncQueue ( l_days_to_keep ); END IF; END GQreq; ----------------------------------------------------------------procedure GetUsrPrt ( p_usr in varchar2, p_prt out varchar2 ) is CURSOR c_usr ( c_un varchar2 )is SELECT defprt FROM mmdba.x_ustbl WHERE m_user like c_un; BEGIN

OPEN c_usr ( p_usr ); FETCH c_usr into p_prt; IF c_usr%notfound THEN p_prt := null; END IF; CLOSE c_usr; END GetUsrPrt; procedure TruncQueue ( days_to_keep in number ) is CURSOR c_queue1 ( no_of_days number ) is SELECT rowid FROM MsQueue q WHERE q.Exetime < sysdate - no_of_days FOR UPDATE of q.ExeTime nowait; BEGIN FOR queue_req in c_queue1 ( days_to_keep ) loop DELETE FROM MsQueue WHERE current of c_queue1; END LOOP; EXCEPTION when LockError THEN CLOSE c_queue1; -- When or if rows are locked we skip cleaning and try again later END TruncQueue; ----------------------------------------------------------------procedure SQReq ( p_PgmId in varchar2, p_PgmType in varchar2, p_Params in varchar2, p_TimeOut in number default 45 ) is CURSOR c_usr1 is SELECT user, sysdate FROM sys.dual; BEGIN /* procedure start packs 6 elements in the package */ l_prc := 'MS101,SQReq: '; IF length(p_Pgmid) > 32 THEN MsgStr := l_prc||'Program name too long'; raise_application_error(-20101,MsgStr); END IF; IF length(p_PgmType) > 4 THEN MsgStr := l_prc||'Program type too long'; raise_application_error(-20102,MsgStr); END IF; IF length(p_Params ) > 250 THEN MsgStr := l_prc||'Program parameters too long'; raise_application_error(-20103,MsgStr); END IF; OPEN c_usr1; FETCH c_usr1 into l_username, l_time; close c_usr1; l_PackType := 'MSREQ'; dbms_pipe.reset_buffer; dbms_pipe.pack_message(l_PackType); dbms_pipe.pack_message(upper(p_PgmId)); dbms_pipe.pack_message(upper(p_Pgmtype)); dbms_pipe.pack_message(l_username);

dbms_pipe.pack_message(l_time); dbms_pipe.pack_message(p_Params); l_PipeStat := dbms_pipe.send_message(PipeName, p_TimeOut); IF l_PipeStat <> 0 THEN PipeErrH('Send-err:', l_PipeStat ); END IF; END SQReq; /* End of proc */ --------------------------------------------------------------procedure QCmd ( Cmd in varchar2, TimeOut in number default 45 ) is /* procedure packs 3 elements in the package */ CURSOR c1 is SELECT user||'('|| userenv('SESSIONID') ||') at '|| to_char(sysdate,'yymmddhh24:mi') FROM sys.dual; l_str varchar(100); l_cmd varchar(30); BEGIN l_prc := 'MS101,Qcmd: '; OPEN c1; FETCH c1 into l_str; close c1; IF upper(cmd) in ( 'STOP', 'PURGE' ) THEN l_cmd := upper(Cmd); ELSE MsgStr := l_prc||' Unknown command !'; raise_application_error(-20100,Msgstr); END IF; l_PackType := 'CMD'; dbms_pipe.pack_message(l_PackType); dbms_pipe.pack_message(l_cmd); dbms_pipe.pack_message(l_str); l_PipeStat := dbms_pipe.send_message(PipeName, TimeOut); IF l_PipeStat <> 0 THEN PipeErrH('Send-err:', l_PipeStat ); END IF; END QCmd; /* End of proc */ procedure PipeErrH ( LocProc in varchar2, Err_Code in number ) is /* Common error procedure */ BEGIN MsgStr := 'MS101 Pipe Error in '|| LocProc ||' with error ' || to_char(Err_Code); IF Err_Code = 3 THEN MsgStr := MsgStr || ' - Process interrupted'; ELSIF Err_Code = 2 THEN MsgStr := MsgStr || ' - Wait request timed out'; END IF; raise_application_error(-20100,MsgStr);

END PipeErrH; END; This could be traced or handled in a program on the server as in this example with a sqlplus script checking the pipe. This script is started with sqlplus -s username/password It exits after receiving a message and should be restarted by the controlling script to check the next message set heading off feedback off linesize 500 col c1 format a40 fold_after word_wrap col c2 format a255 fold_after word_wrap whenever sqlerror exit sql.sqlcode variable v_id varchar2(32) variable v_usr varchar2(32) variable v_params varchar2(250) variable v_dest varchar2(32) variable v_pgm varchar2(32) variable v_type varchar2(4) variable v_time varchar2(13) BEGIN ms101.Gqreq( :v_id, :v_usr, :v_pgm, :v_type, :v_dest, :v_params, :v_time ); END; / rem the procedure stores the server command in a table but it is the controlling rem program that actually commits this when received. commit; rem Using a SELECT-statement to format and report the gotten information SELECT 'QID='||:v_id c1, 'USR='||:v_usr c1, 'PGM='||:v_pgm c1, 'TYPE='||:v_type c1, 'DEST='||:v_dest c1, 'TIME='||:v_time c1, 'PAR='||:v_params c2 FROM sys.dual; exit

Date: Jul-2002 Copyright © 2000 Jerry Mattsson Timehole.com All Rights Reserved. CG_jm2.html