ORACLE - The Right Place for PL/SQL

By Steven Feuerstein

Best practices for PL/SQL location, location, location

I write packages and procedures in both Oracle Database and Oracle Developer applications (Oracle Forms). How should I decide where to put my code?

Another way to frame this question is to ask what the scope of your program should be. That is, from which programs in your application can it be called? Within just a single form? All forms? From within a single program on the server? From any schema that connects to your instance?

I make my decision on these issues by following this principle: Implement the program as closely as possible to where it is used (called).

Location and Relocation Demonstration

For this answer, I use the following business requirement to demonstrate the variations possible and appropriate ways to define my code:

My team is building a call support application. Purchasers of my company's product call us when they have a problem, and we put their calls into a queue if they cannot be handled immediately. I must now write a program that distributes unhandled calls to members of the call support team. The package that will hold all of this logic is call_manager. The procedure for distributing unhandled calls is distribute_calls. Listing 1 shows the header and executable section of this program.

Code Listing 1: distribute_calls procedure

PROCEDURE distribute_calls (    department_id_in IN departments.department_id%TYPE) IS BEGIN    WHILE ( calls_are_unhandled ( ) )    LOOP        FOR emp_rec IN emps_in_dept_cur (department_id_in)        LOOP           IF current_caseload (emp_rec.employee_id) <                   avg_caseload_for_dept (department_id_in)           THEN               assign_next_open_call (emp_rec.employee_id);           END IF;        END LOOP;    END LOOP; END distribute_calls; 

As you can see, this executable section is quite readable: While there is still at least one unhandled call, then for each employee in the specified department, if the current caseload is less than the average for that department, assign the next open call to that employee. Then continue only if there are still unhandled calls.

The executable section calls many subprograms to get the work done:

• calls_are_unhandled function: takes no arguments; returns TRUE if there is still at least one unhandled call, FALSE otherwise
• current_caseload function: returns the number of calls (caseload) assigned to that employee
• avg_caseload_for_dept function: returns the average number of calls assigned to employees in that department
• assign_next_open_call procedure: assigns the employee to the call, making it handled (as opposed to unhandled)

One other thing to note: I haven't yet actually implemented any of these programs. I am using top-down design, also known as stepwise refinement, to stay focused on the overall, high-level logic of the program. That way I avoid getting consumed by all the little details.

I can now go down to the next level of detail and figure out where to put the implementation for these subprograms.

As I said, my rule is: Define the subprogram as closely as possible to its usage. Following that rule without any further analysis, I would define each of the programs as local subprograms within distribute_calls itself, as shown in Listing 2 (the ellipses [...] indicate the implementation of the subprograms).

Code Listing 2: Four local programs in distribute_calls

PROCEDURE distribute_calls (      department_id_in IN departments.department_id%TYPE) IS     FUNCTION calls_are_handled RETURN BOOLEAN            IS BEGIN ... END calls_are_handled;         FUNCTION current_caseload (              employee_id_in IN employees.employee_id%TYPE)         RETURN PLS_INTEGER           IS BEGIN ... END current_caseload;      FUNCTION avg_caseload_for_dept (              employee_id_in IN employees.employee_id%TYPE)         RETURN PLS_INTEGER           IS BEGIN ... END current_caseload;         PROCEDURE assign_next_open_call (              employee_id_in IN employees.employee_id%TYPE)                IS BEGIN ... END assign_next_open_call;    BEGIN 

Procedures and functions directly defined within the declaration section of any PL/SQL block are called local or nested subprograms. In this example, they can be called only within the distribute_calls procedure, and that certainly defines them as closely as possible to their usage.

Yet, as I do this, I find myself thinking about the other programs I have already written in this package and how I might want to use some of this new code in programs I'll write in the future.

I realize, for example, that last week I wrote another function that is very similar to current_caseload. It is now "buried" inside a procedure named show_caseload. Rather than implement the same logic twice (and thereby need to debug and maintain it in both places), it makes more sense for me to move the current_caseload function out of both distribute_calls and show_caseload.

So with a little reshuffling of code, I end up with the package body shown in Listing 3.

Code Listing 3: Relocating the current_caseload function

CREATE OR REPLACE PACKAGE BODY call_manager IS    FUNCTION current_caseload (          employee_id_in IN employees.employee_id%TYPE)        RETURN PLS_INTEGER    IS BEGIN ... END current_caseload;     PROCEDURE show_caseload (          department_id_in IN departments.department_id%TYPE)    IS BEGIN ... END show_caseload;     PROCEDURE distribute_calls (          department_id_in IN departments.department_id%TYPE    )    IS BEGIN ... END distribute_calls; END; / 

Now I have moved the current_caseload function farther away from distribute_calls, but that is because it is used by two subprograms in the package. So it is now as close as possible to both of its usages. Yet I don't expect or see any need for current_caseload to be used outside of the distribute_calls package, so I do not place the header of current_caseload in the package specification.

Now my attention turns to avg_caseload_for_dept. Something about this program seems so familiar. What is it, what is it? Oh, yeah! My coworker Sandra sent out an e-mail last week letting us all know that she had put together a package named call_util that contained several handy utility programs, including a function that returned the average caseload for an employee.

I slap my forehead, dig out the e-mail, and find that the function is named dept_avg_caseload. I check for the existence of call_util in my PL/SQL editing environment, and—lo and behold—the call_util.dept_avg_caseload function in all its already implemented beauty is there, waiting to be used.

I now go back to my distribute_calls procedure, delete the avg_caseload_for_dept function, and change my executable section as shown in Listing 4.

Code Listing 4: Revised distribute_calls executable section

BEGIN     WHILE ( calls_are_unhandled ( ) )     LOOP        FOR emp_rec IN emps_in_dept_cur (department_id_in)        LOOP            IF current_caseload (emp_rec.employee_id) <                      call_util.dept_avg_caseload (department_id_in)            THEN                 assign_next_open_call (emp_rec.employee_id);            END IF;        END LOOP;     END LOOP; END distribute_calls; 

Now one of the subprograms I am using in my procedure is declared so far away that I don't even have control over its implementation and may never even see that implementation. Is that a problem? No. I have more than enough to do and worry about!

The call_util.dept_avg_caseload function is implemented far from my usage, but it is as close as possible to all usages, which span various packages, and so must be declared in the package specification of call_utils.

Whew. I think I have now finished optimizing the location of the definitions of my subprograms. I am left with two local subprograms (calls_are_unhandled and assign_next_open_call), one program (current_caseload) defined privately at the package level (not appearing in the package specification), and another function (call_util.dept_avg_caseload) that someone else wrote and that is available to any schema with execute authority on the call_util package.

I hope the steps I went through to build distribute_calls will help you make your own decisions on where best to place the implementations of your own complex, multilayered programs.

Oracle Developer Code Location

This column has focused on where and how to define code in Oracle Database, but the same rules and logic apply to the Oracle Developer environment. I suggest these guidelines:

• If your program could be useful on both the server side and the client side, move it to the server, because it can be invoked from both sides there.
• If the program is used only in and relevant to client-side modules (it may, for example, manipulate the contents of a field in a form) and you think or know that it will be useful in more than one module, put it into a shared library.
• If your client program is very specific to a current form or report, define it within that module.

---

Steven Feuerstein is Quest Software's PL/SQL evangelist. He has published 10 books on Oracle's programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media), and has created the free Quest Code Tester for Oracle.
More Oracle
Mas Oracle

ORACLE - Using PL/SQL Associative Arrays

By Mark A. Williams

Bring the power of PL/SQL stored procedures to ODP.NET.

Suppose you've just received an e-mail detailing several IT initiatives, two of which will affect your future application development techniques. First, all applications should be designed to minimize network utilization. And second, applications that use Oracle databases must use PL/SQL for access to the database structures.

Knowing that you need to use PL/SQL in the database as the application-programming interface (API) and that you need to reduce network usage, where do you begin? Fortunately, ODP.NET's support for PL/SQL associative arrays can help you meet both IT mandates.

ODP.NET developers can use PL/SQL as an API to the data in the database and use associative array binding to reduce network round-trips. The result is a reduced workload for both the network and Oracle Database, which means faster performance and better scalability for your applications.

The Sample Application

Suppose that one of the reasons for the PL/SQL and performance mandates is that your company is growing rapidly: The IT department alone has three new job classifications—database administrator, manager, and vice president. This column demonstrates how to use .NET, PL/SQL, and associative arrays to add these job categories to an Oracle database—with minimal network utilization.

The sample developed in this column uses the HR schema provided with Oracle Database. This schema includes a JOBS table that contains a row for each job.

The PL/SQL Code

PL/SQL code will provide the interface between the application and the database. The associative_array package specification and body code in Listing 1 are the interface, and it runs in the database's HR schema. (Note that a Microsoft Visual Studio developer might use Oracle Developer Tools for Visual Studio .NET or a tool such as Oracle SQL Developer to create and edit the PL/SQL code.)

Code Listing 1: The PL/SQL code

create or replace package associative_array as   -- define an associative array type for each column in the jobs table    type t_job_id is table of jobs.job_id%type index by pls_integer;   type t_job_title is table of jobs.job_title%type index by pls_integer;   type t_min_salary is table of jobs.min_salary%type index by pls_integer;   type t_max_salary is table of jobs.max_salary%type index by pls_integer;    -- define the procedure that will perform the array insert    procedure array_insert (p_job_id in t_job_id,                                    p_job_title in t_job_title,                                    p_min_salary in t_min_salary,                                    p_max_salary in t_max_salary); end associative_array; /  create or replace package body associative_array as   -- implement the procedure that will perform the array insert    procedure array_insert (p_job_id in t_job_id,                                    p_job_title in t_job_title,                                    p_min_salary in t_min_salary,                                    p_max_salary in t_max_salary) is   begin     forall i in p_job_id.first..p_job_id.last     insert into jobs (job_id,                            job_title,                            min_salary,                            max_salary)               values (p_job_id(i),                          p_job_title(i),                          p_min_salary(i),                          p_max_salary(i));   end array_insert; end associative_array; /  

The associative_array package contains code for both the package specification and the package body. The package specification declares the single procedure that will be implemented in the package body as well as four datatypes that define the parameter types to the procedure. Each type represents a column in the JOBS table, which has the following structure:

SQL> desc jobs   Name            Null?     Type  ----------      -------   --------------  JOB_ID        NOT NULL  VARCHAR2(10)  JOB_TITLE     NOT NULL  VARCHAR2(35)  MIN_SALARY                NUMBER(6)  MAX_SALARY                NUMBER(6) 

Because each datatype in the associative_array package is defined to be a table of each database column type, the package code effectively creates four arrays that match the types of their respective columns in the JOBS table.

For example, the t_job_id type is declared to be a single-column table (a PL/SQL table, not a database table) whose type matches that of the job_id column in the JOBS table.

Each single-column PL/SQL table is essentially an array. The array_insert procedure in the associative_array package body takes four parameters (one for each column in the table); each parameter is an array of values supplied by the .NET client application.

The statement for inserting the rows into the JOBS table resembles a traditional INSERT statement, except that it uses the FORALL keyword and that the inserted values are identified by a lowercase i.

The FORALL keyword allows PL/SQL to process all of the elements in the associative array as a group rather than looping over the array, as with a typical FOR LOOP statement.

The lower and upper bounds of the array are indicated by the first and last methods. The lowercase i in the values clause identifies the correct element in the array for PL/SQL retrieval.

Creating the C# Code

With the PL/SQL interface code available in the Oracle database instance, it's time to deploy the .NET client application code, shown in Listing 2.

Code Listing 2: The .NET C# code

 using System; using System.Data; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types;  namespace JanFeb2007 {   class Program {     static void Main(string[] args) {       // connection string - make sure to adjust for your environment       string constr = "user id=hr; password=hr; data source=oramag; enlist=false; pooling=false";        // create and open connection object       OracleConnection con = new OracleConnection(constr);       con.Open();        // insert the new jobs into the jobs table       // create command object and set attributes       OracleCommand cmd = con.CreateCommand();       cmd.CommandText = "associative_array.array_insert";       cmd.CommandType = CommandType.StoredProcedure;        // create parameter objects for each parameter       OracleParameter p_job_id = new OracleParameter();       OracleParameter p_job_title = new OracleParameter();       OracleParameter p_min_salary = new OracleParameter();       OracleParameter p_max_salary = new OracleParameter();        // set parameter type for each parameter       p_job_id.OracleDbType = OracleDbType.Varchar2;       p_job_title.OracleDbType = OracleDbType.Varchar2;       p_min_salary.OracleDbType = OracleDbType.Decimal;       p_max_salary.OracleDbType = OracleDbType.Decimal;        // set the collection type for each parameter       p_job_id.CollectionType = OracleCollectionType.PLSQLAssociativeArray;       p_job_title.CollectionType = OracleCollectionType.PLSQLAssociativeArray;       p_min_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;       p_max_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;        // set the parameter values       p_job_id.Value = new string[3] { "IT_DBA", "IT_MAN", "IT_VP" };       p_job_title.Value = new string[3] { "Database Administrator", "IT Manager", "IT Vice President" };       p_min_salary.Value = new decimal[3] { 8000, 12000, 18000 };       p_max_salary.Value = new decimal[3] { 16000, 24000, 36000 };        // set the size for each array       p_job_id.Size = 3;       p_job_title.Size = 3;       p_min_salary.Size = 3;       p_max_salary.Size = 3;        // add parameters to command object collection       cmd.Parameters.Add(p_job_id);       cmd.Parameters.Add(p_job_title);       cmd.Parameters.Add(p_min_salary);       cmd.Parameters.Add(p_max_salary);        // execute the insert       cmd.ExecuteNonQuery();        // display the new jobs       cmd.CommandText = "select job_id, job_title from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP') order by job_id";       cmd.CommandType = CommandType.Text;        OracleDataReader dr = cmd.ExecuteReader();        Console.WriteLine("New jobs have been added to the JOBS table:\n");        while (dr.Read()) {         Console.WriteLine("{0,6}: {1}", dr.GetString(0), dr.GetString(1));       }        Console.WriteLine();        // delete the new jobs       cmd.CommandText = "delete from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP')";       cmd.ExecuteNonQuery();        Console.WriteLine("New jobs have been removed from the JOBS table.");        // clean up objects       p_max_salary.Dispose();       p_min_salary.Dispose();       p_job_title.Dispose();       p_job_id.Dispose();       cmd.Dispose();       con.Dispose();        // simple prompt to keep output window from closing when executing from IDE       Console.WriteLine("Press ENTER to continue...");       Console.ReadLine();     }   } } 

Because the data processing logic is in the database, the client code is concerned largely with creating parameters, setting values, and invoking the code in the database. Note that the code for inserting data contains no SQL statements, because the PL/SQL interface has been colocated with the data in the database.

To test the new PL/SQL interface, run the C# console application in Visual Studio.

First, change the connection string to the appropriate values for your Oracle database instance so ODP.NET can pass associative arrays, then compile the code in Visual Studio, and then select Debug -> Step Into from the Visual Studio menu to see how it works.

As you step through the code, note that the application creates an OracleCommand object with the CommandText property value set to the PL/SQL package name and procedure (associative_array.array_insert).

Next, the application creates Oracle-Parameter objects for each parameter to the PL/SQL stored procedure. The application then sets each parameter's type to the OracleDbType to match that of the targeted column for each parameter.

One key point to note as you step through the code is that the application must correctly set the collection type for each parameter to OracleCollectionType.PLSQLAssociativeArray for associative arrays to work correctly. This is what enables the ODP.NET application to bind an OracleParameter object, as a PL/SQL associative array, to the PL/SQL stored procedure

After setting the collection type, the application assigns the values (for the three new jobs) that will be inserted into the JOBS table. Next, the application sets the size of each array, adds the parameters to the OracleCommand object's parameter collection, and invokes the stored procedure to insert the data.

Finally, the application queries the table to show that the three new jobs (IT_DBA, IT_MAN, and IT_VP) were successfully added to the JOBS table.

Going Further

PL/SQL associative arrays are not limited to INSERT operations. You can also use them to perform SELECT, UPDATE, and DELETE operations. I encourage you to experiment further with PL/SQL associative arrays to discover how your applications may benefit from centralized data access via a PL/SQL API as well as from a mechanism for bulk data sharing between Oracle databases and .NET middle tiers.

---

Mark A. Williams is an Oracle ACE, an Oracle Certified Professional DBA, the author of Pro.NET Oracle Programming (Apress, 2004), and a contributor to the Oracle Data Provider for .NET forum on Oracle Technology Network.

ORACLE - Create Sequence

CREATE SEQUENCE

Purpose

To create a sequence. A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

 

Prerequisites

To create a sequence in your own schema, you must have CREATE SEQUENCE privilege.

To create a sequence in another user's schema, you must have CREATE ANY SEQUENCE privilege.

Syntax

 

 

Using Sequences

You can use sequence numbers to automatically generate unique primary key values for your data, and you can also coordinate the keys across multiple rows or tables.

Values for a given sequence are automatically generated by special Oracle routines and, consequently, sequences avoid the performance bottleneck that results from implementation of sequences at the application level. For example, one common application-level implementation is to force each transaction to lock a sequence number table, increment the sequence, and then release the table. Under this implementation, only one sequence number can be generated at a time. In contrast, Oracle sequences permit the simultaneous generation of multiple sequence numbers while guaranteeing that every sequence number is unique.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

 

Accessing Sequence Values

Once a sequence is created, you can access its values in SQL statements with the following pseudocolumns:

CURRVAL	returns the current value of the sequence.
NEXTVAL	increments the sequence and returns the new value.

 

 

Example

The following statement creates the sequence ESEQ:

CREATE SEQUENCE eseq

  INCREMENT BY 10 

The first reference to ESEQ.NEXTVAL returns 1. The second returns 11. Each subsequent reference will return a value 10 greater than the one previous.

 

CURRVAL and NEXTVAL

A sequence is a schema object that generates sequential numbers. When you create a sequence, you can specify its initial value and an increment.

CURRVAL returns the current value in a specified sequence. Before you can reference CURRVAL in a session, you must use NEXTVAL to generate a number. A reference to NEXTVAL stores the current sequence number in CURRVAL. NEXTVAL increments the sequence and returns the next value. To obtain the current or next value in a sequence, you must use dot notation, as follows:

sequence_name.CURRVAL

sequence_name.NEXTVAL

After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. However, you can use CURRVAL and NEXTVAL only in a select list, the VALUES clause, and the SET clause. In the following example, you use a sequence to insert the same employee number into two tables:

INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);

INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);

If a transaction generates a sequence number, the sequence is incremented immediately whether you commit or roll back the transaction.

 

ORACLE - Create Records

Create Records

Records are items of type RECORD. Records have uniquely named fields that can store data values of different types.

Syntax

Parameter Description and Keyword

NOT NULL

This constraint prevents the assigning of nulls to a field. At run time, trying to assign a null to a field defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.

datatype

This is a type specifier. For the syntax of datatype.

record_type_name

This identifies a user-defined type specifier, which is used in subsequent declarations of records.

:= DEFAULT

This operator or keyword allows you to initialize fields to default values.

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression. When the declaration is elaborated, the value of expression is assigned to the field. The value and the field must have compatible datatypes.

Usage Notes

You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package. Also, a record can be initialized in its declaration, as the following example shows:

DECLARE

TYPE TimeTyp IS RECORD(

second SMALLINT := 0,

minute SMALLINT := 0,

hour SMALLINT := 0);

The next example shows that you can use the %TYPE attribute to specify a field datatype. It also shows that you can add the NOT NULL constraint to any field declaration and so prevent the assigning of nulls to that field.

DECLARE

TYPE DeptRecTyp IS RECORD(

deptno NUMBER(2) NOT NULL,

name dept.name%TYPE,

local dept.local%TYPE);

dept_rec DeptRecTyp;

To reference individual fields in a record, you use dot notation. For example, you might assign a value to the dname field in the dept_rec record as follows:

dept_rec.dname := 'PURCHASING';

Instead of assigning values separately to each field in a record, you can assign values to all fields at once. This can be done in two ways. First, you can assign one user-defined record to another if they have the same datatype. (Having fields that match exactly is not enough.) You can assign a %ROWTYPE record to a user-defined record if their fields match in number and order, and corresponding fields have compatible datatypes.

Second, you can use the SELECT or FETCH statement to fetch column values into a record. The columns in the select-list must appear in the same order as the fields in your record.

You can declare and reference nested records. That is, a record can be the component of another record, as the following example shows:

DECLARE

TYPE TimeTyp IS RECORD(

minute SMALLINT,

hour SMALLINT);

TYPE MeetingTyp IS RECORD(

day DATE,

time TimeTyp, -- nested record

place CHAR(20),

purpose CHAR(50));

TYPE PartyTyp IS RECORD(

day DATE,

time TimeTyp, -- nested record

loc CHAR(15));

meeting MeetingTyp;

seminar MeetingTyp;

party PartyTyp;

The next example shows that you can assign one nested record to another if they have the same datatype:

seminar.time := meeting.time;

Such assignments are allowed even if the containing records have different datatypes.

User-defined records follow the usual scoping and instantiation rules. In a package, they are instantiated when you first reference the package and cease to exist when you exit the application or end the database session. In a block or subprogram, they are instantiated when you enter the block or subprogram and cease to exist when you exit the block or subprogram.

Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions. The restrictions that apply to scalar parameters also apply to user-defined records.

You can specify a RECORD type in the RETURN clause of a function specification. That allows the function to return a user-defined record of the same type. When calling a function that returns a user-defined record, you use the following syntax to reference fields in the record:

function_name(parameters).field_name

To reference nested fields in a record returned by a function, you use the following syntax:

function_name(parameters).field_name.nested_field_name

Currently, you cannot use the syntax above to call a parameterless function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal:

function_name().field_name -- illegal; empty parameter list

You cannot just drop the empty parameter list because the following syntax is also illegal:

function_name.field_name -- illegal; no parameter list

Instead, declare a local user-defined record to which you can assign the function result, then reference its fields directly.

Example

In the following example, you define a RECORD type named DeptRecTyp, declare a record named dept_rec, then select a row of values into the record:

DECLARE

TYPE DeptRecTyp IS RECORD(

deptno NUMBER(2),

name CHAR(14),

local CHAR(13));

dept_rec DeptRecTyp;

...

BEGIN

SELECT deptno, name, local INTO dept_rec FROM dept

WHERE deptno = 20;