Writing PL/SQL Procedures (Oracle Projects Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Writing PL/SQL Procedures

To help you to write PL/SQL procedures for client extensions, we first provide you with a brief technical background of PL/SQL procedures. Then, we provide you with information on how to use predefined procedures and parameters in writing your own procedures. We recommend that you read the PL/SQL User's Guide and Reference Manual to learn more about PL/SQL.

Packages

Packages are database objects that group logically related PL/SQL types, objects, and subprograms. Packages usually consist of two files: a package specification file and a package body file. The specification file is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use in the package. It contains the name of the package and procedures function declarations. The package body defines cursors and subprograms, contains the actual PL/SQL code for the procedures, and so implements the specification.

Procedures

Procedures are subprograms within a package that are invoked by the application and perform a specific action. Procedures define what parameters will be passed in as context for the program, how the inputs are processed, and what output is returned. A procedure consists of the following elements:

Inputs Each procedure has predefined input parameters, which must be passed in the predefined order. The parameters identify the transaction being processed and the context in which the program is called. You can derive additional inputs from any Oracle table based on the predefined input parameters.

Logic The procedure uses the inputs and performs any logical processing and calculations. The program can be a simple program, such that it returns a fixed number, or it can be a complex algorithm that performs a number of functions.

Outputs Each procedure returns whatever value you define it to return. For example, your procedure for transaction control extensions may return a null value if the transaction passes all validation rules; or an error message if validation fails.

Syntax for Procedures

A procedure consists of two parts: the specification and the body. The procedure specification begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. The procedure body begins with the keyword IS and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: a declarative part, an executable part, and an optional error handling part. You write procedures using the following syntax:

	PROCEDURE name [  (parameter [, parameter,...])  ] IS
		[local declarations]
	BEGIN 
		executable statements
	[EXCEPTION
		exception handlers]
	END [name];

The parameter syntax above expands to the following syntax:

var_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} value]

For more information, refer to the PL/SQL User's Guide and Reference Manual.

Using Template Procedures

Oracle Projects provides you with template procedures for each client extension that you can use to write your own procedures. Each procedure contains predefined parameters that are passed into the procedure by the program that calls the procedure; you cannot change these predefined input parameters.

The Client Extensions table lists each client extension and its predefined template procedure filenames. The template procedure files are stored in the Oracle Projects admin/sql directory.

Suggestion: Review the appropriate files before you design and implement a client extension. They provide a lot of useful information, including the predefined input parameter list and example case studies.

Suggestion: You should make a copy of these template files in a directory used by your company to store code that you have written. You should make changes to these copies of the files instead of writing directly into these template files. These template files will be replaced when the software is upgraded between releases. Use your modified files to reinstall your procedures after an upgrade to a new release of Oracle Projects.

Writing Logic in Your PL/SQL Procedures

You write the logic in the PL/SQL procedures based on the functional specifications created during the design process. Before you begin to write the client extension PL/SQL procedures, you should have a clear understanding of the client extension procedures; including the inputs and outputs, the error handling of the extension, along with any example procedures provided for each extension. Read the appropriate client extension essays and template procedures to obtain detailed information about the client extensions.

As you determine how to best write the client extension, you should consider these issues:

Can I derive every derived input parameter based on the data structures known?

What outputs should the client extension return?

How does the client extension handle exceptions?

Are there procedures which I can write which are reusable across similar client extensions?

How I can write logical, well commented code that is easy to maintain and debug?

How do I test and debug this client extension?

Are there any performance considerations in the client extension? If so, what are they and how do I address them?

Attention: You must not commit data within your PL/SQL procedure. Oracle Projects processes that call your procedures handle the commit logic.

Storing Your Procedures

After you write your procedures and ensure that the specification file correctly includes any procedures that you have defined, you need to compile and store the procedures in the database in the Applications Oracle username. You must install the package specification before the package body.

The syntax for compiling and storing PL/SQL procedures is included in the template procedure files. Assuming you have written your procedures using copies of these template procedure files, you can use these steps to compile and store your procedures:

Change to the directory in which your files are stored (use the command that is appropriate to your operating system)

$ sqlplus <apps username>/<apps password>

SQL> @<spec_filename>.pls <apps username> <apps password>

SQL> @<body_filename>.pls <apps username> <apps password>

For example, you use the following commands to install your transaction control extensions (assuming your Oracle Applications Oracle username/password is apps/apps):

$ sqlplus apps/apps

SQL> @PAXTTXCS.pls apps apps

SQL> @PAXTTXCB.pls apps apps

If you encounter compilation errors in trying to create your packages and its procedures, you must debug the errors, correct your package definitions, and try to create your packages again. You must successfully compile and store your package and its procedures in the database before you can use the client extensions in Oracle Projects.

Testing Your Procedures

After you have created your client extension procedures, you must test your client extension definitions within the processing flow of Oracle Projects to verify the results are as you expect.

Contents

Index

Navigation

Glossary

Library

Inputs	Each procedure has predefined input parameters, which must be passed in the predefined order. The parameters identify the transaction being processed and the context in which the program is called. You can derive additional inputs from any Oracle table based on the predefined input parameters.
Logic	The procedure uses the inputs and performs any logical processing and calculations. The program can be a simple program, such that it returns a fixed number, or it can be a complex algorithm that performs a number of functions.
Outputs	Each procedure returns whatever value you define it to return. For example, your procedure for transaction control extensions may return a null value if the transaction passes all validation rules; or an error message if validation fails.