April 19th 2001.

 Stored Procedures in Omnis

This article was written a few years ago, before the Web, when Client/Server technology was hot. Although dated the material on dealing with Oracle stored procedures is of interest since I address some debugging concerns. Also the part on 3 tiered logic is applicable to Corba solutions. (Over the years it has been interesting to see the hits on this page. If you are still using Omnis, you've been lucky, try Smalltalk instead. However much of what is below is still useful information regardless of the client solution, since the problem is still the same, and so are the solutions. Good luck).

Traditional 2 Tiered Client/Server

In order to understand some of the issue with response time with the 4 G/L applications today you must understand some of the issues facing the client/server programmer.

In SQL Forms V3.0 or earlier your entire application resided on the mainframe. The advantage of this architecture meant the department could scale up the min/mainframe to meet client demands and expectations for response time. In general, SQL Form applications run much faster than PC based applications since they usually ran on a much faster mainframe. In cases where the mainframe was heavily loaded this advantage was lost and in this regard client server applications dominated since the CPU on the desktop could outperform the overloaded CPU of the mainframe.

At Name_of_client_eh the original 4 G/L 7 applications were developed using a classic 2 tier client/server architecture. Tier 1 the application logic resides on the client workstation, and tier 2 the host datastore resides with the Oracle 7 database on the VAX. These applications use the database to store heavily normalized data, however the disassembly, construction, and business rule logic required to manage the data and present it to the client is coded in native 4 G/L procedure code. This approach makes large slow 4 G/L application.

The problem with this 2 tiered model is that 4 G/L being an interpreted 4GL language is relatively slow, and the bulk of the application logic is being executed with the 4 G/L engine. Other languages like C/C++ or a more mature interpreted language like visual basic or Smalltalk are much faster. Although Blyth is working on the interpreter's speed, major performance improvements are not expected any time soon.

Although steps can be taken to improve the performance of 4 G/L, such as reducing the amount of code processed, avoiding redraws, and rewriting algorithms, this effort takes many man hours and may only achieve a few seconds improvement in response time where at least 10 seconds is required. As the business logic and the visual aspect grow more complex, the amount of code you need to run in a transaction can grow very quickly. In fact it can be cheaper to buy pentium or powermac machine versus re-writing a complex 4 G/L application. This is the view taken by Blyth, that is: the hardware will grow to meet the demands required by the 4 G/L engine.

To solve this dilemma you must re-examine how 4 G/L application are be built and consider the 3 tiered client server architecture. At Name_of_client_eh it seems that sufficient backend CPU capacity is available to take advantage of its speed benefits versus relying on the client's workstation.

3 Tiered Client/Server

A 3 tiered client server application is based on 3 building blocks. Tier 1 is the client workstation, it manages the visual aspect/painting and interaction with the user. Tier 2 is a business rule server, it encapsulates the business logic for the application. The client asks this server for decisions by providing input data and a request for a decision, output consists of result sets, and directions for Tier 1 decisions. Finally the 3rd tier, the data store, contains the logic to retrieve and store the data atoms, in most cases it is a SQL database.

As you can surmise the objective is to move the responsibility for the complex business logic to another faster machine. In a true 3 tiered environment the business rule server could be multiple dedicated super minis and load balancing could be used to give the best possible performance. This is much like the approach taken by parallel Oracle. In actuality you are splitting spheres of function between machines that can give the best performance.

Based on our current technology offering we cannot offer a true 3 tiered client server development environment. However since Oracle supports stored procedures we can mimic some of the features of the 3 tiered model by moving the business rules and application logic from the desktop PC into stored procedures and run those stored procedures on the VAX.

Migrating the logic from 4 G/L to stored procedures improves response time because of multiple factors:

  1. Stored procedures on the VAX run at least 50 times faster than 4 G/L code on a 66Mhz PC.
  2. Stored procedure contains complied SQL, so parsing and compilation is a step we avoid each time we use the procedure. SQL submitted by 4 G/L is recompiled if it is not in the cache when Oracle reuses it.
  3. Less 4 G/L code means smaller applications and a reduction in resource requirements such as workstation paging.
  4. PL/SQL exception handling means better error recovery. When errors occur in complex transactions an exception handler can avoid complex if/then/else logic which is normally used to terminate a transaction or to do error recovery.
  5. Reduces client to server handshaking to process multiple rows of data.

The difficult part in developing an 3 tiered application is deciding on how to split the logic between the front end and the stored procedures, since our tool set does not do this for us.

Let's take an edit transaction as an example. A typical transaction means selecting a record from the working set, editing the record, then asking 4 G/L to validate, update and commit the change.

Display record set-> Show the data to the user
User{selection}-> Allow the user to pick a row, then start the edit process
Lock record-> Validation if required, then lock the data element
Redisplay-> Redraw/reshow any values that have changed
User{Edit}-> Allow the user to edit the data
Validate-> Ensure the changes are correct
Update-> Update the data items in the database
Commit-> Commit the changes
Redisplay-> Redraw/reshow any values that have changed

As an ultimate objective you should be able to take all the data you require from the 4 G/L current record buffer (CRB), then supply that information to a stored procedure for it to validation, massage, insert/update or delete data elements and finally return any error information and a decision about what to do next.

A minimal set of code should consist of 2 lines, the execute stored procedure followed by a check for errors.

Now a lot more code is executed by the application to handle visual aspects. However I suggest you attempt to minimized that activity since it is expensive. A more complex application would have the stored procedure build the post-processing 4 G/L code which could be read and executed in real time by the Application.

Dealing with collection sets

When retrieving a record or a set of records, steps should be taken to minimize the amount of processing required on the selection set by the application. In many cases of poor performing 4 G/L applications the developer has decided to run additional SQL per row, or additional business logic is applied to the record set to massage the data into a form presentable to the user. In theory all of the processing should be moved into a stored procedure. Once the stored procedure runs the data in the CRB should be in a form where little or no work is required before returning control to the user. Again the objective should be three lines, one line to execute the stored procedure, a second to check for errors, and finally the third to redraw the screen.

For small sets of data a stored procedure can return a tab delimited string into an updateable bind variable and ~FOOBAR/4 can be used to unpack the string into a 4 G/L list. For example:

Set current list ll_list
Define list (Store long data) {X_Customer.CUST_SHIP_NAME}
Calculate lc_compact_string as 'A-B-C-D-E-F'
Call procedure ~FOOBAR/4 (lc_compact_string,ll_list,'-',1,'U')
Quit procedure
Local variable ll_list (List)
Local variable lc_compact_string (Character)

In this example a compacted string containing A-B-C-D-E-F is unpacked into the ll_list resulting in a list with 6 rows. The '-' character was used as a delimiter however you should use the tab character chr(9).

The ~FOOBAR/4 procedure code will be changed in the future to use an 4 G/L external C program which does the pack/unpack between the list and the stored procedure. This external is part of the FOOBARPC/MAC.DLL and exists on all current workstations.

Limits:

You can only work with 32000 bytes of data.

For much larger sets of data you could insert the data into a working table, do a build list from table, then delete the rows in the table, or do a rollback. For example in your stored procedure you could setup a savepoint, insert the data you are working with into a table, then return to 4 G/L. Once in 4 G/L you select the data out of the table and do a rollback to the savepoint to delete the inserted rows.

execute stored_procedure -> {
savepoint temp_data;
insert into temp_data set username=uid,...;
exit
}
select a,b,c from temp_table where username=uid;
build list from table
perform sql {rollback to savepoint temp_data} -- which rolls back the inserts

A periodic task to lock the table and perform a delete all rows or truncate table could be done to remove spurious rows created by accident.

Packages

A packages is a database object that groups logically related PL/SQL types, objects, and subprograms. A package has two parts, a specification part and a body. The specification part defines the public interface to the bodies procedures. The body fully defines the cursors and subprograms to implement the specification. The use of packages is suggested as an organization factor in the creation of stored procedures for your application.

Pros:

  1. Centralization of procedure code to give the team more control over the source code.
  2. Use of global persistent variables, since they can span each procedure in a package and live for the life of the session.
  3. Ability to pin code in memory if required
  4. Ability to run an initialization procedure to define global variables.
  5. Ability to hide certain procedures/functions from the client by making them private to the other procedures in the package. This makes the procedure/function inaccessible outside the package.

Cons:

  1. The first use of package will require that entire package being read into memory for your session. To avoid this lag time you should invoke a package initialization procedure at application startup time to ensure your package is available. Alternately a DBA level stored procedure command can be used to pin a package into cache memory which will eliminate the time taken to load the package. See dbms_pool.keep().
  2. Dropping, or alter tables will cause a dependent package to be invalidated. A invalidated package is re-complied when referenced, however the client will see a delay, and it is possible that the re-compile will fail. A stored procedure exists to inspect the implications of dropping or altering existing tables. By using this system level stored procedure you can determine the impact of your change.

Naming Conventions:

You should prefix your stored procedure names with your project name, and create one or more packages based on your project name for your project.

dps.dps_generate_materials

Security:

A user must be granted EXECUTE on your stored procedure to use it. You can restrict access to tables by denying access directly, instead you grant that access to the stored procedure using the tables. The user must then use the stored procedure to access the table since the stored procedure retains the rights it was granted rather than the rights of the individual running it.

 

Use of SQL/PLUS commands to show errors

SHOW ERRORS

Use this SQL_PLUS command to show errors after compiling stored procedures. When a command is complied and an error occurs, the results are stored into USER_ERRORS.

Other tables like ALL_SOURCE, USER_SOURCE, DBA_SOURCE store the source for packages or stored procedures. If you are unsure what the code for a procedure is you should reference the data stored in these tables versus relying on file listings.

 

DBMS_OUTPUT to debug stored procedures

Information can be found on DBMS_OUTPUT in the Oracle 7 Application Developer's Guide Page A-25.

This Oracle 7 package is used to improve the debugging process. Unlike 4 G/L 7 you cannot step through Oracle code or set breakpoints. You must resort to time honored traditional debugging processes of printing the result/logic trail. In SQL*PLUS you must invoke the following command to enable your stored procedure to write to the terminal.

set serveroutput on

The following command within your stored procedure allocates the output buffer with 8192 bytes of storage, it should be your first call:

dbms_output.enable(8192);

The following command places 'Hello There' into the buffer:

dbms_output.put_line('Hello There');

If 'set serveroutput on' was used then this output will be written to the screen.

If you are dealing with a stored procedure you have invoked from 4 G/L you would want to retrieve the lines you had put into the output buffer then put them into a character string you would return to 4 G/L via an updateable bind variable. To do this you use the GET_LINES command to put the data into character string.

dbms_output.get_lines(target_updateable_4 G/L_variable,100);

This command will get upwards of 100 lines into my_buffer. The storage area must contain sufficient room to hold all the lines, each line can be as much as 255 bytes.

DBMS_PIPE to debug stored procedures

Information can be found on pipes in the Oracle 7 Application Developer's Guide Page A-20.

You can 'pipe' data between users or sessions within the same instance. For example you could embed pipe statements in your stored procedures and execute them from 4 G/L. Later you can read the data from the pipe in your SQL*PLUS session to see how the stored procedure actually executed the request. The difference between this and using dbms_output is that with output the data goes away after the procedure terminates. With pipes data is stored within oracle until you read it, which gives you the ability to write the information and later read it in SQL*PLUS.

Typically you use the dbms_pipe.pack_message() procedure to pack data into a 4096 byte buffer. If you exceed the buffer size you get an ora-2000 error. Once all the data is in the buffer you send it via a dbms_pipe.send_message function which places the data into the pipe. A pipe can hold as much data as you defined when you use the pipe. Once pipes are empty they age and disappear.

declare
error_number integer;
begin
dbms_pipe.pack_message('hello');
dbms_pipe.pack_message('there');
error_number := dbms_pipe.send_message(user,1,8192);
end;

In this example we place two messages, 'hello' and 'there' in the pipe buffer named by user which is the function that returns your userid. We then use the send_message function to put the data into the pipe. '1' is used to indicate how long to wait in seconds if the pipe is blocked or if it is full. '8192' indicates how much data in bytes we can contain in the pipe. Using a large number like a million as a buffer area will grab far too much shared memory and make the DBA unhappy... Please use this value wisely...

Once the data is in a pipe the following procedure can be used to get data out of it and display it in SQL*PLUS. You could create a procedure for this code example and invoke it in SQL_PLUS versus retyping or pasting it each time you want to use it.

set serveroutput on
declare
buffer varchar2(256);
error_number integer;
begin
dbms_output.enable(8192);
error_number := dbms_pipe.receive_message(user,0);
loop
dbms_pipe.unpack_message(buffer);
dbms_output.put_line(buffer);
end loop;
exception
when others then
dbms_pipe.purge(user);
end;

dbms_output is used to display the information.

dbms_pipe.receive_message gets the data in pipe user. '0' means no wait for data if none is found. The unpack_message procedure is used to put data into the 'buffer' variable, then the data is written via the put_line command to the screen. An exception handler traps any error like 'pipe is empty' and purges the pipe so it is aged out of memory.

If you change the '0' to '60', then the procedure will wait for 60 seconds for output. This means you could start the procedure before you place data into the pipe. Once data arrives the procedure will capture the data and write it out. For example you could start the reader in SQL*PLUS, then switch over to 4 G/L to invoke the stored procedure, then switch back to read the results.

How to execute a stored procedure

Perform SQL {begin my_package.stored_procedure(@[my_file.my_field],
:I1,:lc_error); end;}
if #F=0|len(lc_error)
; Do error recovery stuff.
end if

To avoid an 4 G/L syntax error on execution you must bracket the stored procedure call with a being/end; PL/SQL block. Bind notation @[] can be used to pass in data values. updateable bind variables using ':' can be used to get data back out of a ORACLE variable.

The stored procedure would look like so:

package body my_package is
procedure stored_procedure(p_my_field in varchar2,p_data out varchar2,
p_error out varchar2) as
begin
p_data := p_my_field;
exception
when others then
p_error := SQLERRM;
end;

 

Use of local versus file.field name

I suggest you use a local variable versus a file.field name. Because of how updateable bind variables work a '.' is translated to a '_' and the '#' to a 'z'. This causes the my_file.my_field reference to be invalid and you must use a my_field reference. Although a field name reference works it is expensive since it causes 4 G/L to hunt sequentially for the file.field pair. If you have a lot of files in your application it can be an expensive task. Not only is this expensive you might have a duplicate field name. Thus always use a local variable, don't use # variables either.

 

Updateable Bind Variables

An 4 G/L updateable bind variable is used to get data out of Oracle, it can also put data into oracle but you should typically only use it to get data back. This variable type is new to 4 G/L 7 and behaves differently between version 2 and 3 and has a few bugs. (NOTE I believe that field names cannot be more than 15 characters in length, also you cannot use file.field references since the '.' causes a PL/SQL error, see the above note).

Date Fields:

You should use regular bind variables to get dates into Oracle. A bind variable will ensure the date/time casting is done correctly for your target field. If the 4 G/L field contains date/time then a date/time value will be put into the Oracle variable, if the 4 G/L field is a short date then only the date part is transferred. Although the Oracle DATE format visually implies just a date value it actually contains a time component. Your other choice is to use the TO_DATE oracle function and supply your stored procedure with a character string using the correct date format.

Now for the issue, you CANNOT pass dates via an Updateable bind variable, an 4 G/L error will occur. You must use a character value of the date and have the stored procedure use TO_DATE to cast the date into the target column. For data coming back again you must place the date into a character field then use the 4 G/L dat() function to convert the date to a date value. Don't forget the time component.

Character Fields:

When you return a character field Version 2.21 and v3.0 of 4 G/L will truncate 1 byte off the end of your character string. You should append a ' ' character to the end of your character string before returning it from oracle. This problem may disappear in version 3.0 of 4 G/L so your code should not be dependent on the lack or addition of a ' ' on the character string, or perhaps you will need to change your stored procedure once 4 G/L V3.1 arrives. (Not yet fixed in V3.0, you should test results in V3.1).

Passing character data into Oracle via an updateable bind variable seems to work correctly.

Other fields, lists, pictures, binary:

Nope!

Storage:

The 4 G/L manual states the amount of code allocated to a updateable bind variable is the greater of the current size of the variable, or 256 bytes. It further states that you can use the form :my_var:2048 to override this and dictate a storage size. This doesn't work with V2.21 of 4 G/L and is stated to change in future versions of 4 G/L. (For V3.1 if you use local variables 4 G/L will attempt to allocate ALL the storage as definded by the character field, so 10,000,000 bytes fields attempt a 10MB allocation. Also returned values are now padded with blanks to the length of the field.)

Errors:

if the flag is false and sys(131) is empty or zero then a PL/SQL block error in parsing occurred. Look for invalid PL/SQL, or updateable fields names too long, or usage of fields than aren't number or character. Attempt to change all your updateable bind vars to regular bind vars to track issue with updateable bind var syntax. Have fun...

Error recovery and reporting

Most errors raised will be reported back to the 4 G/L caller and retrieved via sys(131), and sys(132). However in complex procedures it can be difficult to understand which SQL statement triggered the error. In these cases you could use an exception handler to trap the error, then using SQLERRM you can put the original error plus any other diagnostic information into a character updateable bind field for further processing.

You should use the standard error message table when you are creating internal error message. The use of this table avoids embedding error message text in your application. For example if the error message you want is number 13827 then return 13827 as the error text. Your post-error checking code can see if a number was returned, if so then it can call the ~FOOBAR/68 routine and have it display the message for 13827.

Failure to use an exception handier in your procedure will cause all the processing of the stored procedure to be rolled back. You should avoid the use of 'when others NULL'; which will hide system critical errors from the user. Always return the SQLERRM information when dealing with errors you are not expecting.

Avoid the use of commit or rollback in your stored procedure. This transaction level processing should be done outside of your stored procedure.

 

Performance Benefits

The setup/take down time for running a stored procedure is on the order of 1/2 second. The real gain is that a stored procedure runs about 50 times faster than 4 G/L code, and SQL parsing is avoided.

 

Maintain Investigator window Example

This window is used to maintain information about the investigator and contains information about his address, study address, phone numbers, etc. For each investigator 7 child lists are built. This process takes upwards of 11 seconds to complete.

Each investigator has address and phone information. A join is done between the investigator, the address information, and the address type tables to bring back the data in the following format:

Address Information
Address Type
123 Street stuff Mailing
123 Street stuff Financial
123 Street stuff Overnight
Another Address Home
   

However the data is displayed and manipulated with the following window:

This choice of view means that we must combine multiple rows into a single row for each unique address by manipulating the address type flags. This is done by 4 G/L code which runs on the client workstation. Each investigator typically has study site information, normal address information, and phone number information which fit this type of data manipulation, so as many as 3 data transformations are done for each investigator being displayed.

A stored procedure was created to duplicate the functionality of the 4 G/L code. This procedure saves 1/2 seconds for each manipulation, for a total saving of 1.5 seconds.

More work will be done to centralize the other 4 sets of children sets the window works on and to provide support for the Address insert, or update since that process requires the reverse manipulation of the data to take the data from this window and update the 3 tables involved.

 

SPR Administration Function Example

In the SPR system the project manager can perform functions on sets of SPRs for a particular application, like assigning a set of problems a team member :

For example assigning 5 open problems to a particular programmer took about 24 seconds to complete since as many as 15 SQL statements were being setup and sent to the server to process the request.

A stored procedure was written to replace the code run by 4 G/L. For the previous case the stored procedure is passed the set of SPR ids and the person's identifier. The stored procedure takes 3 seconds to complete the update process.

In this example we replaced about 90% of the 4 G/L code with a stored procedure and achieved a significant performance increase.