• 50
• Oracle Precompilers
• Oracle Precompilers
• Why Precompile Source Code?
• General Precompiler Features
• Precompiler Languages
• ANSI/IO Standards
• Precompiler Options
• How to Precompile a Program
• Conditional Precompiling
• Embedded SQL Host Program
• Basics of a Host Program
• Naming Conventions
• Embedded SQL
• Executable Statements
• Declarative Statements
• PL/SQL Blocks
• Benefits of PL/SQL Blocks
• Including PL/SQL Blocks
• Host Program Requirements
• Data Declaration Area
• Declare Section
• SQL Include Area
• SQL Connect Area
• Data Manipulation Area
• Types of Executable SQL Statements
• Logical Unit of Work
• Controlling Transactions
• Locking
• Exit Command
• Error Handling
• General Guidelines
• SQLCA Variables
• WHENEVER statement
• Indicator Variables
• Host Arrays
• Dynamic SQL
• Coding Methods for Dynamic SQL Statements
• Method One
• Method Two
• Method Three
• Method Four
• User Exits
• Performance Tuning
• Poor Performance
• Improving Performance
• Reducing Oracle Communication Overhead
• Reducing Processing Overhead
• New Features in Version 1.4
• Summary

50

Oracle Precompilers

Extracting and manipulating data from a relational database would be very difficult if not for tools such as precompilers. The precompiler allows SQL statements to be embedded within procedural languages. Applications developed with this tool can use the best features of SQL, combined with the best features of the procedural language, creating a powerful module. This chapter discusses Oracle precompilers, creating a source program and some of the new features that Version 1.4 precompilers have.

Oracle Precompilers

The precompiler is a mechanism that allows SQL statements to be embedded within procedural languages. This tool provides the programmer with unlimited capabilities. To fully understand all the benefits of this tool, this chapter will focus on why we precompile, general features of the precompiler, what languages source code can be written in, how standardized are the precompilers, what options do they have, how to precompile your source code, and what is conditional precompiling.

Why Precompile Source Code?

Embedded SQL statements are not something a high-level language understands. The precompiler must therefore take the SQL statements and translate them into something the high-level language will understand. Libraries contain data structures that help guide the translation process for consistency. This general concept will help in understanding the precompiler features.

General Precompiler Features

One of the primary reasons to use a precompiler is to have the capability of utilizing SQL statements in a high-level language. Oracle precompilers offer many benefits and features that can help in this development. These features include the following:

• Source code can be written in six different high-level languages, which all have ANSI/ISO precompilers.
  
  
• Using a precomplier enables a user to take advantage of dynamic SQL, which allows for a more highly customized application.
  
  
• [lb] Precompilers enable a user to process PL/SQL transaction blocks and use host arrays to process large amounts of data quickly; both dramatically improve performance.
  
  
• Precompilers make conversions between Oracle internal datatypes and language datatypes, and check syntax and semantics.
  
  
• Precompilers may also include conditional parameters to enable the program to be executed in different environments.
  
  
• Direct interfaces with SQL*Forms can be done using user exits.
  
  
• Precompilers provide variables to handle warning and error conditions, which are included in the ORACLE Communication Area (ORACA).
  
  

Each of these features will be discussed in further detail later in the chapter. But you should know how many precompilers Oracle has before too much more detail is given.

Precompiler Languages

There are six procedural languages for which Oracle has precompilers available. During installation of Oracle, the host language is determined and the appropriate compiler is installed. The six languages include the following:

• Ada
  
  
• C
  
  
• COBOL
  
  
• FORTRAN
  
  
• Pascal
  
  
• PL/I
  
  

With Oracle precompilers, you have the capabilities of precompiling and compiling several host programs, then linking them together into one module. Each program does not need to be written in the same language to be linked together, so standardization is a must. Oracle precompilers do meet national standards and will be discussed in the next section.

ANSI/IO Standards

Because SQL has become the standard language for relational databases, standardization has become an important issue. Oracle precompilers have taken exceptional efforts to meet standards set by the following organizations:

• American National Standards Institute (ANSI)
  
  
• International Standards Organization (ISO)
  
  
• U.S. National Institute of Standards and Technology (NIST)
  
  

Compliance for these requirements is measured by NIST, which uses over 300 test programs. Oracle precompilers have conformed to these standards by checking the SQL statements that are being embedded for standard syntax and semantics. But there is much more to precompiler function than syntax checking.

Precompiler Options

To increase flexibility among applications, Oracle precompilers have options that can be manipulated by the programmer to allow for certain conditions. For example, if a line of the source program exceeds 80 characters, an error occurs while precompiling. There is a precompiler option that allows the programmer to extend that line to 132 characters. These controls, along with others, are outlined in Table 50.1. The * character next to an item indicates that it can be entered inline.

Table 50.1. Precompiler options.

Syntax

Default

Specifies

A precompiler command line might look something like the following:

proc iname=example.pc include=ora_pcc: include=clib: ireclen=132

There is only one required argument, INAME. This argument tells the precompiler what the input filename is. So the minimum requirements for any precompiler command line could look like the following:

proc iname=example.pc

All of these precompiler options can be accessed online. Just enter the precompiler option without any argument at your operating system prompt. This help feature will display the name, syntax, default value and purpose of each option.

There are some general guidelines to follow when setting options for precompiling:

• When you precompile the program module that CONNECTs to Oracle, specify a value for MAXOPENCURSORS that is high enough for any of the program modules that will be linked together.
  
  
• If you want to use just one SQLCA, you must declare it as a global in one of the program modules and as an external in the other modules. In C, for example, this is done by using the external storage class, which tells the precompiler to look for the SQLCA in another program module. Unless you declare the SQLCA as external, each program module will use its own local SQLCA.
  
  
• You cannot DECLARE a cursor in one file and reference it in another.
  
  

All these options can be utilized in several ways when precompiling your program, but what is the best way to precompile, compile, and link your source program?

How to Precompile a Program

In Version 1.4 of the precompilers, executables exists for each of the six languages previously mentioned. So each host language has a different command to run the precompiler. Table 50.2 shows which command to use for a specific language.

Table 50.2. Precompiler commands.

Host Language

Precompiler Command

There are several ways an embedded SQL program can be precompiled, compiled, and linked. Compiling multiple embedded SQL programs can become very time consuming if each step is done per program. This approach of issuing individual commands can lead to inconsistency between program modules. Executing each step of the compilation process should probably be used when it doesn't matter if standardization is met. For example, it doesn't matter if a common library is linked into your program but all other programs for this system depend on it. Therefore, utilizing a control file simplifies compiling your source program. This control file will evaluate what tasks need to be accomplished while adhering to company standards. Depending upon the platform that you are working on an executable file such as a .BAT or .COM can be created to accept parameters. The following example shows what a command line using a control file might look like.

                                             @PCL  TEST  TEST100  PCL.

Control Filename:  @PCL

System Name:        TEST

Host Program:       TEST100

Options:            P(recompile)

                    C(compile)

                    L(ink)

After receiving the parameters to evaluate, the control file can determine what needs to be done. This approach helps keep all the programs consistent with others in terms of linking current libraries, objects, or other files. The following PCL.COM file is an example taken from a VAX platform to show how executable files can be created to control precompiling, compiling, and linking host programs.

$ write sys$output "PCL.COM Version 2.3"

$

$ write sys$output " "

$       set noverify

$ assign $disk7:[vaxprod.com.ccom],$disk7:[vaxprod.'P1'.c'P1'] clib

$ if "''P1'" .eqs "" then goto USAGE

$ if "''P2'" .eqs "" then goto USAGE

$!

$ if "''P3'" .eqs "" then goto precompile

$ if "''P3'" .eqs "P" then goto precompile

$ if "''P3'" .eqs "p" then goto precompile

$ if "''P3'" .eqs "PC" then goto precompile

$ if "''P3'" .eqs "pc" then goto precompile

$ if "''P3'" .eqs "PCL" then goto precompile

$ if "''P3'" .eqs "pcl" then goto precompile

$ if "''P3'" .eqs "PCLR" then goto precompile

$ if "''P3'" .eqs "pclr" then goto precompile

$!

$ if "''P3'" .eqs "c" then goto compile

$ if "''P3'" .eqs "C" then goto compile

$ if "''P3'" .eqs "cl" then goto compile

$ if "''P3'" .eqs "CL" then goto compile

$ if "''P3'" .eqs "clr" then goto compile

$ if "''P3'" .eqs "CLR" then goto compile

$!

$ if "''P3'" .eqs "l" then goto link

$ if "''P3'" .eqs "L" then goto link

$ if "''P3'" .eqs "lr" then goto link

$ if "''P3'" .eqs "LR" then goto link

$!

$ if "''P3'" .eqs "r" then goto run

$ if "''P3'" .eqs "R" then goto run

$ goto USAGE

$!

$!

$! *************************************************************************

$! ***************************   Precompile   ******************************

$! *************************************************************************

$ precompile:

$  proc iname='P2'.pc include=ora_pcc: include=clib: ireclen=132

$

$ if "''P3'" .eqs "p" then goto continue

$ if "''P3'" .eqs "P" then goto continue

$

$

$! *************************************************************************

$! *****************************   Compile   *******************************

$! *************************************************************************

$ compile:

$ write sys$output "Compiling ''P2'.C"

$ cc/noopt/include_directory=clib:/define=VMS 'P2'

$!

$ if "''P3'" .eqs "pc" then goto continue

$ if "''P3'" .eqs "PC" then goto continue

$ if "''P3'" .eqs "c" then goto continue

$ if "''P3'" .eqs "C" then goto continue

$!

$!

$! *************************************************************************

$! *******************************   Link  *********************************

$! *************************************************************************

$ link:

$!

$ @ora_rdbms:loutl 'P2' 'P2''P4',clib:c'P1'.olb/lib,clib:ccom.olb/lib-

,ora_util:sqllib.olb/lib,ora_rdbms:oci/lib/include=(ocicee) 'P2' s

$!

$ endlink:

$ if "''P3'" .eqs "PCL" then goto continue

$ if "''P3'" .eqs "pcl" then goto continue

$ if "''P3'" .eqs "cl" then goto continue

$ if "''P3'" .eqs "CL" then goto continue

$ if "''P3'" .eqs "l" then goto continue

$ if "''P3'" .eqs "L" then goto continue

$!

$!

$! *************************************************************************

$! ********************************   Run  *********************************

$! *************************************************************************

$ run:

$ P2 P1/P1

$!

$!

$! *************************************************************************

$! *************************      continue      ****************************

$! *************************************************************************

$ continue:

$ goto exit

$!

$! *************************************************************************

$! ***************************      USAGE      *****************************

$! *************************************************************************

$ USAGE:

$ write sys$output " "

$ write sys$output " "

$ write sys$output "Usage: @PCL [SYSTEM] [PROGRAM NAME] [options] [d] [libinfo]

$ write sys$output " "

$ write sys$output "Where: APPLICATION - Application system:  (TEST,  etc)

$ write sys$output "                     Used to link the system library (ie.LTEST.OLB/LIB)

$ write sys$output "      PROGRAM NAME - (TEST100, TEST200, etc)

$ write sys$output "           options - Options (PCLR):  P = precompile

$ write sys$output "                                      C = compile

$ write sys$output "                                      L = link

$ write sys$output "                                      R = run

$ write sys$output "                                   PCLR = all the above   (default)

$ write sys$output "                     Options entered must appear in above order.

$ write sys$output "                        valid: P PC C CL PCL    not valid: CP LC PL

$ write sys$output "                 d - Compile with #define DEBUG   (default is nodebug)

$ write sys$output "           libinfo - Additional User link libraries: ,mydir:mylib.olb/lib

$ write sys$output " "

$ write sys$output "*Note: Upper case are REQUIRED, Lower case are optional

$ write sys$output " "

$ write sys$output "Example: @PCL TEST TEST100 PCL

$ write sys$output "       (compile TEST100.C, nodebug, links TEST200.OBJ including MYLIB.OLB)

$ write sys$output " "

$ goto exit

$

$

$ exit:

$ deassign clib

Compiling using a control file such as PCL.COM can be used even if you don't have embedded SQL in your program. This keeps all applications created by your shop consistent with each other.

But a programmer can do still more to control how and what is precompiled in his or her source. The next section of the chapter discusses conditional precompiling.

Conditional Precompiling

The precompiler also allows for conditional precompiling. This gives you the ability to write a program for several different platforms. For example, you might want to include some section of your program for a UNIX platform but not for a VMS platform. Oracle precompilers recognize conditional sections of code. These sections are indicated by statements that define the environment and what actions to take. In this section, procedural and embedded SQL statements can be used to perform platform specific operations.

Remember that conditional statements must be included in your source code, not the control file that will compile your code.

The following Oracle statements are utilized when creating a conditional section.

Some symbols are port-specific and predefined for you when the Oracle precompilers are installed. Predefined operating-system symbols include CMS, MVS, DOS, UNIX and VMS. In the following example, conditional precompiling is shown using a predefined symbol.

#include <stdio.h>

#include <string.h>

#include <stdlib.h>

#ifdef DOS

   #include <dos.h>

#endif

/* DECLARE AREA */

EXEC SQL BEGIN DECLARE SECTION;

  VARCHAR user_id[20];

  VARCHAR passwd[20]

EXEC SQL END DECLARE SECTION;

/* INCLUDE AREA */

EXEC SQL INCLUDE SQLCA;

/* FILE DECLARATIONS */

FILE *t_file;

main()

{

    printf("/n What is your User ID: ");

    scanf("%s",user_id.arr);

    printf("\nEnter Password: ");

    scanf("%s",passwd.arr);

    user_id.len = strlen(user_id.arr);

    passwd.len = strlen(passwrd.arr);

/* CONNECTS TO DATABASE */

    EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;

    if (sqlca.sqlcode < 0)

    {

       printf("\n%s",sqlca.sqlerrm.sqlerrmc);

       EXEC SQL ROLLBACK WORK RELEASE;

       exit(1);

    }

    else

    {

      printf("\nSuccessfully connected to Oracle.");

      #ifdef DOS

         t_file = fopen("\\login.lst","w");

         fprintf(t_file,"log in by %s",:userid);

         fclose(t_file);

     #end if;

     EXEC SQL COMMIT WORK RELEASE;

     exit(0);

    }

  }

Now that you have an understanding of what the precompiler does and what you can control, creating a embedded SQL program will take front stage. The next section of this chapter focuses just on an embedded SQL program. This section will look at the basics of a source program, program requirements and how to handle errors.

Embedded SQL Host Program

Creating a host program that utilizes embedded SQL statements can be very beneficial. There are some specific guidelines and requirements that must be included in an embedded SQL program. This section of the chapter focuses on the basics of a precompiled program, program requirements, handling errors, host arrays, dynamic SQL, user exits, and performance tuning. As each topic is introduced, code examples will be provided in C for that section which will build into a completed program by chapters end. You then can precompile, compile, link, and execute it. This section begins with some basic concepts.

Basics of a Host Program

Three basic concepts must be discussed to ensure an understanding of the material that will be presented later in this chapter. These concepts are naming conventions, embedded SQL, and using PL/SQL blocks.

Naming Conventions

All embedded SQL programs must have the appropriate extension for the host language. This notifies the precompiler that embedded SQL is included and the program must be translated (precompiled) into the host language format. Table 50.3 indicates what extension should be used for the host language.

Table 50.3. Host language file extensions.

Host Language

Standard File Extension

After your program has been precompiled, a new file is created with a different extension; normal compiling and linking can then continue.

When you modify your source code, remember to change the original file. In C, for example, the .PC file should be modified and not the .C file; otherwise your changes will not go into effect.

The second basic concept, which is the focus of these programs, is embedded SQL statements.

Embedded SQL

Embedded SQL refers to SQL statements that are placed within an application program. The program itself is referred to as the host program. All standard SQL commands can be used in the embedded statement. There are two types of embedded SQL statements: executable and declarative.

Any SQL command can be embedded within a high-level language, but not the extended SQL commands that are included in SQL*PLUS. These include report formatting, editing SQL statements, and environment-setting commands.

Executable Statements

Executable statements are used to connect to Oracle; query, manipulate and control access to Oracle data; and to process data. These statements result in calls to and return codes from Oracle. There are four types of executable embedded SQL statements: data definition, data control, data manipulation and data retrieval. Later in the chapter each of these statements will be discussed in further detail.

Declarative Statements

Declarative statements are used to declare Oracle objects, communication areas, and SQL variables. These statements do not result in Oracle calls or return codes and do not operate on Oracle data. Now that you know there are two sections involved in writing an embedded SQL host program, we should look at exactly what is required.

The last concept is utilizing PL/SQL blocks. The next section will cover how PL/SQL blocks are included into your source program.

PL/SQL Blocks

Oracle precompilers treat PL/SQL blocks as a single SQL statement. This can greatly enhance the performance of your program.

Benefits of PL/SQL Blocks

Using PL/SQL blocks can drastically reduce processing overhead, improve performance, and increase productivity. For example, each SQL statement that is executed generates a certain amount of communication and processing overhead. Depending upon what type of environment you are working in, this could create an enormous amount of traffic—simply slowing everything down to a crawl. If you use a PL/SQL block, then you can pack multiple SQL statements into a section of code and execute the entire block. Because Oracle treats the entire block as a single statement, you can reduce overhead multiple times.

Including PL/SQL Blocks

To include PL/SQL in your host program, you need to first DECLARE the host variables that you want to use in the PL/SQL block. Next, you need to bracket the SQL statement that will be included in the PL/SQL blocks with the keywords EXEC SQL EXECUTE and END-EXEC. The following code example shows how a PL/SQL block is incorporated into your program.

#include <stdio.h>

/* DECLARE AREA */

EXEC SQL BEGIN DECLARE SECTION;

  VARCHAR   user_id[20];

  VARCHAR   passwd[20]

   int                 emp_number;

   VARCHAR  job_title[20];

   VARCHAR  hire_date[10];

   real               salary;

EXEC SQL END DECLARE SECTION;

/* INCLUDE AREA */

EXEC SQL INCLUDE SQLCA;

/* FUNCTION DECLARATIONS */

void get_employee_data();

main()

{

    printf("/n What is your User ID: ");

    scanf("%s",user_id.arr);

    printf("\nEnter Password: ");

    scanf("%s",passwd.arr);

    user_id.len = strlen(user_id.arr);

    passwd.len = strlen(passwrd.arr);

/* CONNECTS TO DATABASE */

    EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;

    if (sqlca.sqlcode < 0)

    {

       printf("\n%s",sqlca.sqlerrm.sqlerrmc);

       EXEC SQL ROLLBACK WORK RELEASE;

       exit(1);

    }

    else

    {

      printf("\nSuccessfully connected to Oracle.");

     EXEC SQL COMMIT WORK RELEASE;

     get_employee_data();

    }

exit(0);

  }

void get_employee_data()

{

     printf("\nEmployee Number? ");

     scanf("%d\n",empl_number);

/* BEGIN OF PL/SQL BLOCK */

  EXEC SQL EXECUTE

    BEGIN

      SELECT JOB, HIREDATE, SAL

           INTO :job_title, :hire_date, :salary

          FROM EMP

         WHERE EMPNO = :emp_number;

    END;

  END-EXEC;

/* END OF PL/SQL BLOCK */

  printf("%s %s %d\n",:job_title.arr,:hire_date.arr,:salary);

}

If you are working with a system that is database intensive, utilizing PL/SQL blocks will improve performance.

Host Program Requirements

Being able to pass data between Oracle and your application requires a variety of tasks to be completed successfully. There are two primary parts of an embedded SQL program that must be included: the data declaration area (which involves the declarative statements) and the data manipulation area (which involves the executable statements). Before you can execute anything in your program, you must make your program aware of the players.

Data Declaration Area

This area is used to define all host variables, include extra files, and establish a connection to the database. Sometimes this area is referred to as the program prologue. There are three required sections within the data declaration area: the DECLARE section, SQL Include section, and the SQL connect area.

Declare Section

All host language variables referenced in a SQL statement must be declared to Oracle; otherwise, an error message will be issued at precompile time. These variables are declared in the DECLARE section. Most host languages will allow multiple DECLARE sections per precompiled unit, but you must have at least one. These sections can be defined locally or globally. Host variables within this section can be of any length, but only the first 31 characters are evaluated. These variables can consist of letters, digits, and underscores, but they must begin with an alpha character. To store a datatype, Oracle must know the format and valid range of values. Oracle recognizes only two kinds of datatypes: internal and external. Internal datatypes indicate how Oracle will store the data, and external specifies how the data is stored in the host variable. Table 50.4 shows the internal datatypes.

Table 50.4. Internal datatypes.

Name

Code

Description

Table 50.5 shows the external datatypes.

Table 50.5. External datatypes.

Name

Code

Description

It is important that the host variables (external) within the DECLARE section match the database datatype (internal). For example, if you declare a host variable such as receipt_date a character string and the database has it declared as a DATE type, you will receive an error. The following guidelines are recommended when declaring and referencing host variables. A host variable must be

• explicitly declared in the DECLARE section
  
  
• referenced with a colon(:) in all SQL statements and PL/SQL blocks
  
  
• of a datatype supported by the host language
  
  
• of a datatype compatible with that of its source or target database column
  
  

A host variable must not be

• subscripted
  
  
• prefixed with a colon in the host language statement
  
  
• used to identify a column, table, or other Oracle object
  
  
• used in data definition statements such as ALTER, CREATE, and DROP
  
  

A host variable can be

• used anywhere an expression can be used in a SQL statement
  
  

At precompile time, an association is made between variables declared and the database column type. If there is a discrepancy, a runtime error will occur; otherwise, the datatype is converted. VARCHAR variables are converted into the following structure:

struct {

  unsigned short   len;

  unsigned char     arr[20];

} username;

This structure helps eliminate character-counting algorithms. You can reference each element of the structure and manipulate it. The following example shows the syntax for the DECLARE section and how the elements of a VARCHAR can be used.

#include <stdio.h>

/* DECLARE SECTION */

EXEC SQL BEGIN DECLARE SECTION;

  VARCHAR user_id[15];

EXEC SQL END DECLARE SECTION;

main()

{

    printf("/n What is your User ID: ");

    scanf("%s",user_id.arr);                      /* referencing the character string element */

    user_id.len = strlen(user_id.arr);        /* referencing the length element */

}

After declaring a variety of host variables communication between the host program and Oracle needs to be established to monitor successes and failures. This communication is made by utilizing the SQL Include area.

SQL Include Area

This section of the program enables the user to include copies of files into the host program. Any file can be included if it contains embedded SQL statements. The most common include file is the SQL Communication Area file (SQLCA).

The SQLCA is a data structure that handles certain events and provides diagnostic checking between the Oracle RDMBS and the host program. SQLCA variables maintain valuable runtime statistics such as: warning codes with text, Oracle error codes and number of rows processed are convenient for handling special conditions within the host program. The following examples shows the syntax for including this file in the host program.

-- declare section --

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

  VARCHAR user_id[15];

EXEC SQL END DECLARE SECTION;

/* SQL INCLUDE AREA */

EXEC SQL INCLUDE SQLCA;

main()

{

    printf("/n What is your User ID: ");

    scanf("%s",user_id.arr);

    user_id.len = strlen(user_id.arr);

}

After establishing communication capabilities, the program must connect to the database to actually start communicating.

SQL Connect Area

The host program must log on to Oracle before you will be able to manipulate data. A CONNECT statement must be issued and be the first statement to be executed. The userID and password must be host-language variables and cannot exceed 20 characters. SQL*Net will allow you to concurrently access any combination of local and remote databases, or you can make multiple connections to the same database. You should contact your network manager for specific connect guidelines. The following examples shows how to connect to Oracle.

#include <stdio.h>

/* DECLARE AREA */

EXEC SQL BEGIN DECLARE SECTION;

  VARCHAR user_id[20];

  VARCHAR passwd[20]

EXEC SQL END DECLARE SECTION;

/* INCLUDE AREA */

EXEC SQL INCLUDE SQLCA;

main()

{

    printf("/n What is your User ID: ");

    scanf("%s",user_id.arr);

    printf("\nEnter Password: ");

    scanf("%s",passwd.arr);

    user_id.len = strlen(user_id.arr);

    passwd.len = strlen(passwrd.arr);

/* CONNECTS TO DATABASE */

    EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;

    if (sqlca.sqlcode < 0)

    {

       printf("\n%s",sqlca.sqlerrm.sqlerrmc);

       EXEC SQL ROLLBACK WORK RELEASE;

       exit(1);

    }

    else

    {

      printf("\nSuccessfully connected to Oracle.");

     EXEC SQL COMMIT WORK RELEASE;

     exit(0);

    }

  }

Now that you have declared host variables, established a communication channel and connected to the database, you can start to manipulate data.

Data Manipulation Area

The data manipulation area is where SQL statements are executed. This section is often referred to as the program body. This section of the chapter focuses on the types of SQL statements, the logical unit of work, controlling transactions, locking data, and the EXIT command.

Types of Executable SQL Statements

Several different types of SQL statements that can be executed in the data manipulation area: data manipulation statements, data definition statements and data control statements.

Data manipulation (DML) statements are used to change the data. The following is a list of commands that are considered DML statements:

• UPDATE column values in existing rows
  
  
• DELETE rows from a table
  
  
• COMMIT WORK writes data to the table
  
  
• ROLLBACK WORK removes any changes made to the data
  
  
• LOCK reserves the row or table exclusively for the user
  
  

Data definition (DDL) statements are used to define and maintain database objects. Some common uses of the DDL statements are to create tables or views. The following is a list of commands that are considered DDL:

• CREATE TABLE
  
  
• CREATE VIEW
  
  
• ALTER table or view
  
  
• DROP table, view, grant, or sequence number
  
  

Data control (DCL) statements are used to access tables and the data associated with them. There are two types of access that these statements will control. The first type is connecting to the database; the CONNECT and GRANT commands enable a user to do this. The second type of control is access to the data; the GRANT SELECT and REVOKE DELETE commands are examples of this.

Pulling together a combination of all three of these statements creates what is known as a logical unit of work.

Logical Unit of Work

A logical unit of work is defined as a group of SQL statements treated as a single transaction to the Oracle kernal. This unit of work begins with any valid SQL DML statement and ends with either an implicit or explicit release of work. An implicit commit release is performed by the execution of any DDL statement, whereas an implicit rollback release is performed upon abnormal termination of your program. A program may explicitly release the logical unit of work, which is discussed in the section "Controlling Transactions."

Controlling Transactions

Because Oracle is transaction oriented and processes information in logical units of work, controlling these statements is essential to data integrity. A transaction begins with the first EXEC SQL statement issued in your program. When one transaction ends, the next begins. You can end a transaction in one of two means: COMMIT or ROLLBACK. If you do not subdivide your program with a COMMIT or ROLLBACK statement, Oracle will treat the whole program as one transaction.

Remember to commit what you are not willing to recreate. Losing one large transaction could be dangerous; smaller transaction losses are easier to recover from.

To make changes to the database permanent, use the COMMIT command. The COMMIT command does the following:

• Makes permanent all changes to the databases during the current transactions
  
  
• Makes these changes visible to other users
  
  
• Erases all savepoints
  
  
• Releases all row and table locks, but not parse locks
  
  
• Closes cursors referenced in a CURRENT OF clause
  
  
• Ends the transaction
  
  

The COMMIT statements has no effect on the values of host variables or on the flow of control in your program. This statement should be placed in the main path through your program. The following example shows the syntax of the COMMIT command:

EXEC SQL COMMIT WORK RELEASE;

The RELEASE option on COMMIT or ROLLBACK releases all process resources and provides a clean exit from Oracle. After the release, there is no further access to the Oracle database until another connect is issued.

To undo pending changes, use the ROLLBACK statement. This statement lets you return to the starting point, so the database is not left in an inconsistent state. The ROLLBACK statement does the following:

• Undoes all changes made to the database during the current transaction
  
  
• Erases all savepoints
  
  
• Ends the transaction
  
  
• Releases all row and table locks, but not parse locks
  
  
• Closes cursors referenced in a CURRENT OF clause
  
  

The ROLLBACK statement has no effect on the values of host variables or on the flow of control in your program. The following example shows the syntax for the ROLLBACK statement.

EXEC SQL ROLLBACK WORK RELEASE;

Committing and releasing changes that have been made to the database gives the program enormous control over data, but what happens when someone else executes a program and it tries to manipulate the same data? Oracle has provided a mechanism that enables you to reserve the data just for your use.

Locking

Oracle uses locks (sometimes called enqueues) to control access to the data. When a row of data is being sought, Oracle will lock the row until the user has completed the current transaction. A user can get hung up in an application when two or more sources are trying to access the data at the same time. Because of data-integrity constraints, Oracle will give temporary ownership of a row to whomever has completed the most amount of work or got to the row first. The following guidelines should be followed when using locks:

• Unless specified, any DML operation will acquire a table-level lock.
  
  
• A DML lock can be bypassed by explicitly requesting a row-level lock. See the following code example:
  
  

/* LOCKS ALL ROWS */

EXEC SQL LOCK TABLE STUDENT

    IN SHARE UPDATE MODE[NOWAIT];

/*   IN THE FOLLOWING SELECT STATE THE [NOWAIT] */

/* PARAMETER TELLS ORACLE  NOT TO WAIT FOR THE TABLE IF IT HAS BEEN */

/*LOCKED BY ANOTHER USER */

EXEC SQL SELECT FNMAE

    FROM STUDENT

    WHERE BIRTHDATE < '01-JAN-60'

    FOR UPDATE OF LNAME[NOWAIT];

Exit Command

The last statement in a Pro*C program should be the EXIT command. This command has the option of returning a status check—especially useful when used as a SQL*Forms user exit. Table 50.6 shows the return code.

Table 50.6. Return codes.

Error Handling

What can be done if a program does exit with a value of 1 (unsuccessful termination)? There are many types of errors that your program may encounter. Error handling should be a major part of every application, in order to anticipate when errors will occur. Because errors can occur from a variety of sources—design fault, coding mistakes, hardware failure, and invalid user input—it is advisable to try and handle them. This section will look at some general guidelines for error handling and three ways of handling errors: SQLCA variables, WHENEVER statements, and indicator variables.

General Guidelines

The following guidelines will help avoid some common pitfalls:

• Code a WHENEVER statement before your first executable statement. This will ensure that all ensuing errors are trapped, because WHENEVER statements stay in effect to the end of the file.
  
  
• When using a cursor to fetch rows of data, your program should be able to handle an end-of-data condition.
  
  
• Try to avoid branching to error routines with GOTOs.
  
  

Oracle has provided variables that help monitor when one of these guidelines has not been followed or some other type of error has occurred. These variables are included in the SQLCA file.

SQLCA Variables

The SQLCA is a data structure that is updated after every executable SQL statement. SQLCA variables can be implicitly or explicitly checked. Some of the components that can be checked are described in the Table 50.7.

Table 50.7. SQLCA processing static variables.

Name

Type

Description

The following code demonstrates how SQLCA variables can be used to detect errors.

#include <stdio.h>

/* DECLARE AREA */

EXEC SQL BEGIN DECLARE SECTION;

  VARCHAR user_id[20];

  VARCHAR passwd[20];

   char            pssn[9];

   char            pfname[12], plname[20];

   char            pmajor[4], pdegree_program[1];

   char            pccode[4], pcname[30];

EXEC SQL END DECLARE SECTION;

/* INCLUDE AREA */

EXEC SQL INCLUDE SQLCA;

main()

{

    printf("/n What is your User ID: ");

    scanf("%s",user_id.arr);

    printf("\nEnter Password: ");

    scanf("%s",passwd.arr);

    user_id.len = strlen(user_id.arr);

    passwd.len = strlen(passwrd.arr);

    EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;

    if (sqlca.sqlcode < 0)

    {

       printf("\n%s",sqlca.sqlerrm.sqlerrmc);

       EXEC SQL ROLLBACK WORK RELEASE;

       exit(1);

    }

    else

       printf("\nSuccessfully connected to Oracle.");

  printf("\nEnter data for new student -- 'Q' to quit");

  printf("\nSSN: ");

  scanf("%s",pssn);

  while (*pssn != 'Q')

  {

     printf("\n First Name: ");

     scanf("%s",pfname);

     printf("\n Last Name: ");

     scanf("%s",plname);

     printf("\n Major: ");

     scanf("%s",pmajor);

     printf("\nDegree Program: ");

     scanf("%s",pdegree_program);

/* INSERT NEW RECORD INTO DATABASE */

     EXEC SQL INSERT INTO STUDENT

       (SSN,FNAME,LNAME,MAJOR,DEGREE_PROGRAM)

        VALUES

        (:pssn,:pfname,:plname,:initcap(:pmajor),:pdegree_program);

    if (sqlca.sqlcode < 0)

    {

         printf("\n%s",sqlca.sqlerrm.sqlerrmc);

         EXEC SQL ROLLBACK WORK RELEASE;

         exit(1);

    }

    else

       printf("\nSuccessfully inserted record.");

/* VERIFY MAJOR IS VALID */

  EXEC SQL SELECT DEPT FROM DEPT WHERE DEPT= initcap(:pmajor);

 if (sqlca.sqlcode == 1403)

{

    printf("\nInvalid Major");

    EXEC SQL ROLLBACK WORK;

}

/* ENROLL STUDENT IN ALL ENTRY LEVEL CLASSES */

  EXEC SQL INSERT INTO GRADE (SSN,CCODE)

    SELECT :pssn, CCODE

    FROM CLASS

    WHERE PREREQ IS NULL;

if (sqlca.sqlcode < 0)

    {

         printf("\n%s",sqlca.sqlerrm.sqlerrmc);

         EXEC SQL ROLLBACK WORK RELEASE;

         exit(1);

    }

    else

       printf("\Student %s is now enrolled in %d classes.",plname,sqlca.sqlerrd[2]);

} /* END WHILE */

EXEC SQL COMMIT WORK RELEASE;

exit(0);

}

If you need more Oracle-specific diagnostic information, you can include the ORACA file. This file contains additional system statistics, option settings, and extended diagnostic variables. ORACA is declared just as the SQLCA file is. The following example shows the syntax for declaring ORACA:

EXEC SQL INCLUDE oraca;

To enable the ORACA file, you must set the precompiler option to YES. The following example shows two ways to set this option:

/* OFF LINE */

ORACA = YES

/* IN LINE */

EXEC ORACLE OPTION (ORACA=YES)

Certain flags need to be set for the runtime options. By setting these flags to a non-zero value, you are able to do the following:

• Save the text of SQL statements
  
  
• Enable DEBUG operations
  
  
• Check cursor cache consistency
  
  
• Check heap consistency
  
  
• Gather cursor statistics
  
  

Some of the flags are shown here:

Flag Name

Description

WHENEVER statement

WHENEVER statements are declarative in nature; therefore, their scope is positional—not logical. These statements test all executable SQL statements that follow it until another WHENEVER statement is encountered. This type of error detection is preferable because it is easier, more portable, and ANSI-compliant. These statements are used to direct a program's execution whenever an error or warning condition occurs. Some of the conditions that this statement can detect are Oracle errors, Oracle warnings, and no data found. If a WHENEVER statement is omitted, the process will continue. The following example shows the syntax of the WHENEVER statement.

EXEC SQL WHENEVER <condition> <action>

Careless use of WHENEVER statements may cause problems such as infinite looping.

All the possible error conditions are described in following list.

• SQLERROR Sqlcode is negative
  
  
• SQLWARNING Sqlwarn[0] is set to "W"
  
  
• NOT FOUND Sqlcode is +1403
  
  

Several different actions can be taken when one of the error conditions is encountered. The following list describes those actions:

• Continue
  
  
• Do function_call() | break
  
  
• Goto statement_label
  
  
• Stop
  
  

Indicator Variables

An indicator variable is associated with a host variable. Each time the host variable is used in an executable SQL statement, a return code is assigned to the indicator variable. This enables the user to monitor the host variables. There are two primary uses for indicator variables: detecting null values or truncated data values with the "SELECT INTO" clause, and setting columns to null values without explicitly hard-coding them for use with the UPDATE and INSERT statements. Indicator variables must be explicitly declared in the DECLARE section as a 2-byte integer (short), and it is good practice to declare them after the host variable. The following example shows how to declare these variables.

EXEC SQL BEGIN DECLARE SECTION;

  int    emp_number;

  float salary;

  short sal_ind;   /* indicator variable */

EXEC SQL END DECLARE SECTION;

Indicator variables can be used in VALUES, INTO and SET clauses in executable SQL statements. To reference these variables, you must prefix them with a colon and append them to their associate host variables; they can not be referenced by themselves. See the following syntax guideline.

:host_variable:indicator_variable

Some of the possible values for indicator variables are outlined in the following list:

• Return codes for SELECTs (output)
  
  

0 :Successful return of value into host variable

-1 :Returned value of null

>0 :Returned value was truncated

• Return codes for UPDATE or DELETE (input)
  
  

>=0 :Value of host variable will be used

-1 :Null value will be used in place of host variable

The following example shows how indicator variables can be used.

-- declare section --

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

  VARCHAR user_id[20];

  VARCHAR passwd[20];

   char            pssn[9];

   char            pfname[12], plname[20];

   char            pmajor[4], pdegree_program[1];

   char            pccode[4], pcname[30];

    short          imajor;

EXEC SQL END DECLARE SECTION;

-- SQL include section --

EXEC SQL INCLUDE SQLCA;

main()

{

    printf("/n What is your User ID: ");

    scanf("%s",user_id.arr);

    printf("\nEnter Password: ");

    scanf("%s",passwd.arr);

    user_id.len = strlen(user_id.arr);

    passwd.len = strlen(passwrd.arr);

    EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;

    if (sqlca.sqlcode < 0)

    {

       printf("\n%s",sqlca.sqlerrm.sqlerrmc);

       EXEC SQL ROLLBACK WORK RELEASE;

       exit(1);

    }

    else

       printf("\nSuccessfully connected to Oracle.");

  printf("\nEnter data for new student -- 'Q' to quit");

  printf("\nSSN: ");

  scanf("%s",pssn);

  while (*pssn != 'Q')

  {

     printf("\n First Name: ");

     scanf("%s",pfname);

     printf("\n Last Name: ");

     scanf("%s",plname);

     printf("\n Major: ");

     scanf("%s",pmajor);

     printf("\nDegree Program: ");

     scanf("%s",pdegree_program);

/* INSERT NEW RECORD INTO DATABASE */

     EXEC SQL INSERT INTO STUDENT

       (SSN,FNAME,LNAME,MAJOR,DEGREE_PROGRAM)

        VALUES

        (:pssn,:pfname,:plname,:initcap(:pmajor),:pdegree_program);

    if (sqlca.sqlcode < 0)

    {

         printf("\n%s",sqlca.sqlerrm.sqlerrmc);

         EXEC SQL ROLLBACK WORK RELEASE;

         exit(1);

    }

    else

       printf("\nSuccessfully inserted record.");

/* VERIFY MAJOR IS VALID */

  EXEC SQL SELECT DEPT FROM DEPT WHERE DEPT= initcap(:pmajor);

/* The indicator variable is set after each select for :pmajor */

switch (imajor)

{

    case 0:  printf("\nMajor is valid");

                  break;

     case -1: printf("\nMajor is invalid");

                  break;

    default:  printf("\nMajor is truncated");

                  break;

}

/* ENROLL STUDENT IN ALL ENTRY LEVEL CLASSES */

  EXEC SQL INSERT INTO GRADE (SSN,CCODE)

    SELECT :pssn, CCODE

    FROM CLASS

    WHERE PREREQ IS NULL;

if (sqlca.sqlcode < 0)

    {

         printf("\n%s",sqlca.sqlerrm.sqlerrmc);

         EXEC SQL ROLLBACK WORK RELEASE;

         exit(1);

    }

    else

       printf("\Student %s is now enrolled in %d classes.",plname,sqlca.sqlerrd[2]);

} /* END WHILE */

EXEC SQL COMMIT WORK RELEASE;

exit(0);

}

Host Arrays

A host array is a collection of related data items, called elements, which is associated with a single variable name. Arrays ease programming and improve performance, because large amounts of data can be manipulated at one time with a single Oracle statement. You declare arrays in the DECLARE section just as you do for simple variables, only you must set the size of the array. The maximum dimension of a host array is 32,767 elements. The following example shows the DECLARE section for host arrays.

EXEC SQL BEGIN DECLARE SECTION;

    int    emp_number[50];

    char emp_name[50];

    float salary[50];

EXEC SQL END DECLARE SECTION;

If you exceed the maximum dimension of a host array, you will receive the "parameter out of range" runtime error. Also, IF you use multiple host arrays in a single SQL statement, then all the arrays must be of the same dimension; otherwise, you will receive a runtime error of "array size mismatched."

Some of the restrictions that are imposed include not declaring a two-dimensional array or not declaring an array of pointers.

Arrays can be used as input variables in the INSERT, UPDATE, and DELETE statements, and as output variables in the INTO clause of SELECT and FETCH statements. When using the arrays as an output variable, if you know the exact number of rows that will be returned, you will want to dimension the array to that number. You need not process host arrays in a loop, unless you are batching. Simply use the unsubscripted array names in your SQL statement. The following example shows the syntax for array processing:

EXEC SQL BEGIN DECLARE SECTION;

    int    emp_number[50];

    char emp_name[50];

    float salary[50];

EXEC SQL END DECLARE SECTION;

/* POPULATE HOST ARRAY HERE */

EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO)

    VALUES (:emp_number, :emp_name, :dept_number);

If you are unsure of the number of rows that will be returned, you should do batch FETCHes. Batch FETCHes within a loop will all allow you to retrieve a large amount of data without knowing exactly how many rows you will be retrieving. Each FETCH returns the next batch of rows from the current active set. The maximum rows returned is dependent on the array size. Fewer rows are returned under the following conditions:

• The end of the active set is reached
  
  
• Fewer than a full batch of rows remain to be FETCHed
  
  
• An error is detected while processing a row
  
  

Dynamic SQL

Dynamic SQL is nothing more than SQL statements that are not known at compile time. Unlike static SQL statements, dynamic SQL statements are not embedded in your source program. These statements are stored in a character string or built by the program at runtime. Dynamic SQL statements can be built interactively or read from a file. The primary advantage of dynamic SQL is that it is much more versatile than plain embedded SQL. One of the disadvantages of dynamic SQL is it requires added processing time. Dynamic SQL should be utilized when one of the following is unknown:

• Text of the SQL statement
  
  
• The number of host variables
  
  
• The datatypes of host variables
  
  
• References to database objects such as columns, indexes, sequences, tables, usernames, and views
  
  

In order to represent a dynamic SQL statement, a character string must contain the required text of a valid SQL statement, without the EXEC SQL clause, host-language delimiters or statement terminators, or any of the following embedded SQL commands:

• CLOSE
  
  
• DECLARE
  
  
• DESCRIBE
  
  
• EXECUTE
  
  
• FETCH
  
  
• INCLUDE
  
  
• OPEN
  
  
• PREPARE
  
  
• WHENEVER
  
  

These character strings are manipulated by Oracle via cursors. Cursors are areas of memory used for processing SQL statements. The scope of the cursors is the entire Pro*C program. There are no limits to the number of cursors a Pro*C program can have, but there is a maximum number on how many cursors can be open at one time. The INIT.ORA file contains a parameter that will set how many open cursors are allowed at one time. There are two types of cursors: implicit and explicit. Implicit cursors are automatically declared and can be used for all SQL statements. Explicit cursors are required to manipulate multi-rowed SELECTs. They are also used to keep track of the current set or row. Four Oracle statements are associated with cursors.

• DECLARE: Associate SQL statement with cursor
  
  
• OPEN: Execute SQL statement
  
  
• FETCH: Retrieve next row and store in buffer
  
  
• CLOSE: Release cursor area back to Oracle.
  
  

The DECLARE statement defines the cursor by giving it a name which is associated with the SQL statement. This name is used by the precompiler and should not be defined in the DECLARE section. Cursor names should not be hyphenated and can be any length, but only the first 31 characters are evaluated. Because this statement is a declarative statement, it must precede all other SQL statements and must be declared only once.

The following example shows how to DECLARE a cursor:

EXEC SQL DECLARE Class_Students CURSOR FOR

                    SELECT FNAME, LNAME, GRADE

                    FROM STUDENT S, GRADE G

                    WHERE S.SSN = G.SSN

                    AND G.CCODE = :class_code

                    ORDER BY LNAME;

After declaring a cursor, it must be opened so that Oracle can parse it, replace variables and parameters, and execute the query. The OPEN command positions the cursor just before the first row that will be retrieved from the active set. It also zeroes the row-processed count kept by the third element of the SQLERRD in the SQLCA. Remember that rows have not been retrieved at this point. After a cursor has been opened, the variables are not reexamined until it is opened again; therefore, if you want a different active set, you must reopen the cursor. The amount of work done by the OPEN command depends on the values of three precompiler options: HOLD_CURSOR, RELEASE_CURSOR and MAXOPENCURSORS. The following example shows the syntax of the OPEN command.

EXEC SQL OPEN Class_Students;

After the cursor has been opened, data can be retrieved using the FETCH command. The FETCH command requires that you specify the output host variables that will contain the results from the query by using the INTO clause. The first time you execute the FETCH command the cursor moves to the first row in the active set. With each execution of FETCH the cursor advances one row until it encounters no rows to retrieve. The cursor can only move forward. If you want a previous row you must reOPEN the cursor. After the last row has been retrieved, SQLCODE in the SQLCA has a value of 1403. Only one FETCH statement can be associated with an open cursor. The following example shows the syntax for the FETCH command.

EXEC SQL FETCH Class_Students

                      INTO  :first, :last, :grade;

The last statement used is the CLOSE command. This command releases all cursor resources back to Oracle. The active set will become undefined and an invalid cursor error will occur if a FETCH is executed against a closed cursor. A cursor can be closed multiple times, but it usually is closed only when there is no further need for the SQL statement. The following example shows the syntax for the CLOSE command.

EXEC SQL CLOSE Class_Students;

There are four methods for programming a dynamic SQL statement. With all four methods you must store dynamic SQL statements in a character string, which must be a quoted literal or a host variable. Choosing the right method can be confusing, but Figure 50.1 will help make this decision.

Figure 50.1. This logic flow chart will help decide what method is appropriate for your statement.

After deciding what method is appropriate for your situation, try to avoid some of the common errors described in the following list:

• When using a character array to store the dynamic SQL, blank-pad the array before starting.
  
  
• Do not null-terminate the host string. Oracle does not recognize the null terminator as an end-of-string; instead it treats it as part of the SQL statement.
  
  
• If you use a VARCHAR variable to store the dynamic SQL statement, make sure the length of the VARCHAR is set correctly before you execute the PREPARE or EXECUTE IMMEDIATE command.
  
  

Coding Methods for Dynamic SQL Statements

Now let's look at the four different methods that can be used to code dynamic SQL statements.

Method One

Method one lets you build a dynamic SQL statement and immediately execute it using the EXECUTE IMMEDIATE command. This method results in success or failure and uses no host variables. Statements in method one are parsed every time they are executed. These statements do not allow SELECTs. The following example shows a method-one statement:

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

   VARCHAR  sql_statement[300];

   VARCHAR  username[20];

   VARCHAR  password[20];

EXEC SQL END DELCAR SECTION;

search_cond    char[40];

EXEC SQL INCLUDE SQLCA;

main()

{

    printf("\nUsername: ");

    scanf("%s\n",username.arr);

    printf("\n"Password: ");

    scanf("%s\n"password.arr);

   EXEC SQL CONNECT :username IDENTIFIED BY :password;

   if (sqlca.sqlcode < 0)

  {

       printf("\n%s",sqlca.sqlerrm.sqlerrmc);

       EXEC SQL ROLLBACK WORK RELEASE;

       exit(1);

    }

    else

       printf("\nSuccessfully connected to Oracle.");

  }

   strcpy(sql_statement.arr,"UPDATE EMP SET COMM = 500 WHERE ";

    printf("\nEnter a search condition for the following statement: ");

    printf("\n%s",sql_statement.arr);

    scanf("%s\n",search_cond);

    strcat(sql_statement.arr,search_cond);

  EXEC SQL EXECUTE IMMEDIATE :sql_statment;

  EXEC SQL COMMIT WORK RELEASE;

  exit(0);

}

Method Two

Method two is identical to method one except that you may use host variables, which need to be known at precompile time. With method two, the SQL is parsed just once but can be executed many times. This method enables the user to use the USING clause. Every placeholder in the prepared dynamic SQL statement must match a corresponding host variable in the USING clause. The following examples shows a method-two statement:

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

   VARCHAR  sql_statement[300];

   VARCHAR  username[20];

   VARCHAR  password[20];

    REAL          commission;

EXEC SQL END DELCAR SECTION;

search_cond    char[40];

EXEC SQL INCLUDE SQLCA;

main()

{

    printf("\nUsername: ");

    scanf("%s\n",username.arr);

    printf("\n"Password: ");

    scanf("%s\n"password.arr);

   EXEC SQL CONNECT :username IDENTIFIED BY :password;

   if (sqlca.sqlcode < 0)

  {

       printf("\n%s",sqlca.sqlerrm.sqlerrmc);

       EXEC SQL ROLLBACK WORK RELEASE;

       exit(1);

    }

    else

       printf("\nSuccessfully connected to Oracle.");

  }

   strcpy(sql_statement.arr,"UPDATE EMP SET COMM = :c WHERE ";

    printf("\nEnter a search condition for the following statement: ");

    printf("\n%s",sql_statement.arr);

    scanf("%s\n",search_cond);

    strcat(sql_statement.arr,search_cond);

  EXEC SQL C1 FROM :sql_statment;

  printf("\nCommission: ");

  scanf("%d\n",commission);

  EXEC SQL EXECUTE C1 USING :commission;

  EXEC SQL COMMIT WORK RELEASE;

  exit(0);

}

Method Three

Method three allows your program to accept or build a dynamic query, then process it using PREPARE command with the DECLARE, OPEN, FETCH and CLOSE cursor commands. At precompile time the following need to be known: select-list items, number of placeholders and datatypes of host variables. Method three is used for dynamic SQL with a known select list, giving the programmer more flexibility to build SQL statements on the fly. The following example shows a method-three statement:

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;

  VARCHAR userid[20]

  VARCHAR passwd[20];

  VARCHAR student_id[10];

  VARCHAR student_name[30];

  VARCHAR classes[30];

  VARCHAR sql_statement[600];

EXEC SQL END DECLARE SECTION;

/* PROGRAM VARIABLES */

int employee_number;

/* PROCEDURE DECLARATION */

void log_on();

void build_select();

main()

{

   char    where_clause[80];

/* LOG ONTO DATABASE */

    log_on();

    build_select();

    EXEC SQL PREPARE C1 FROM :sql_statement.arr;

    EXEC SQL DECLARE C1 CURSOR FOR S1;

    sql_statement.len := length(sql_statement.arr);

    EXEC SQL OPEN C1 USING employee_number;

    EXEC SQL FETCH C1 INTO :student_name;

    if (sqlca.sqlcode < 0)

    {

       printf("\n%s",sqlca.sqlerrm.sqlerrmc);

       EXEC SQL ROLLBACK WORK RELEASE;

       exit(1);

    }

    else

       printf("\n%s\n",:student_name.arr);

       EXEC SQL CLOSE C1;

    exit(0);

}

void log_on()

{

    printf("/n What is your User ID: ");

    scanf("%s",user_id.arr);

    printf("\nEnter Password: ");

    scanf("%s",passwd.arr);

    user_id.len = strlen(user_id.arr);

    passwd.len = strlen(passwrd.arr);

    EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;

    if (sqlca.sqlcode < 0)

    {

       printf("\n%s",sqlca.sqlerrm.sqlerrmc);

       EXEC SQL ROLLBACK WORK RELEASE;

       exit(1);

    }

    else

       printf("\nSuccessfully connected to Oracle.");

}

void build_select()

{

    strcpy(sql_statement.arr,"SELECT FIRST_NAME || ' '|| LAST_NAME, ");

    strcat(sql_statement.arr,"FROM EMP ");

    strcat(sql_statement.arr,"WHERE EMPNO = ");

    printf("\nEnter employee number: ");

    scanf("%d",employee_number);

    strcat(sql_statement.arr,employee_number);

}

Method Four

Method four is probably the more complex of the four methods, but it is very diverse in its use. It allows the program to accept or build a dynamic SQL statement, then process it using descriptors. At precompile time, the following are unknown: select-list items, number of placeholders, and datatypes of host variables. To process this type of dynamic query, you must be able to use the DESCRIBE SELECT LIST command and be able to declare a data structure called the SQL descriptor area (SQLDA). Descriptors are a segment of memory used by the computer and Oracle to hold a complete description of the variables in a dynamic SQL statement. Descriptor variables are defined as a data structure containing the following information:

• Maximum number of columns that can be evaluated
  
  
• Actual number of columns in the SELECT list
  
  
• Array of pointers to column names
  
  
• Array of maximum lengths of columns
  
  
• Array of actual column lengths
  
  
• Array of data types for each column
  
  
• Array of pointers to data values
  
  
• Array of pointers to indicator variables
  
  

To process the dynamic SQL statement, your program must issue the DESCRIBE BIND VARIABLES command and declare another kind of SQLDA, called a bind descriptor, to hold descriptions of the placeholders for input. If you have more than one method for SQL statements, each statement will require its own SQLDA(s); nonconcurrent cursors, however, can reuse SQLDAs. There is no set limit on the number of SQLDAs in a program.

The DESCRIBE command is useful in determining what the SQL statement contains. DESCRIBE instructs Oracle to provide the host variables for any select statement. It examines the select statement to determine the number of columns and type of each in the select list. Oracle must define a storage area to hold fetched rows from the database, and actual data returned from the select is stored in descriptor variables.

The following are steps in coding for method four:

1. Define a descriptor variable:
   
   

         SQLDA *descr_var

1. Place a SQL SELECT statement into a host variable:
   
   

         scanf("%[^\n",sql_statement);

1. Prepare the SQL statement:
   
   

         EXEC SQL PREPARE S1 FROM sql_statement;

1. Declare a cursor area for the SELECT statement:
   
   

         EXECSQL DECLARE C1 CURSOR FOR S1

1. Execute the query and create an active set:
   
   

         EXEC SQL OPEN C1 USING DESCRIPTOR descr_var;

1. DESCRIBE the SELECT into the descriptor variable:
   
   

         EXEC SQL DESCRIBE SELECT LIST FOR S1 INTO desc_var

1. FETCH rows from active set:
   
   

         EXEC SQL FETCH C1 USING DESCRIPTOR descr_var;

1. CLOSE the cursor:
   
   

         EXEC SQL CLOSE C1;

But is it possible to write a program that can be used by other Oracle tools, such as SQL*Forms? The answer is yes, and user exits provide this functionality.

User Exits

A user exit is a host-language subroutine that is called by SQL*Forms to do special processing. Sometimes SQL*Forms triggers are unable to perform complex calculations or field validations. Embedded SQL commands and PL/SQL blocks are allowed in user exits. User exits are invoked in the form but execute outside the SQL*Form. Usually, user exits are faster than SQL commands but their down side is that they are more difficult to debug and are more complicated to write. Some of the common uses of user exits include the following: controlling real time devices or processes, data manipulations that need extended procedural capabilities, or special file I/O operations. The following list is some general rules and guidelines to use when writing user exits:

• User exits are written in a 3rd-generation language, with SQL commands embedded inside. This code is precompiled in order to translate the SQL commands into host-language statements.
  
  
• 3rd-generation language statements (host-language statements) are written as an ordinary host-language program. Upper- and lowercase are used as normal.
  
  
• SQL commands are customarily written entirely in uppercase.
  
  
• User-exit variables must be written according to the rules of the host language.
  
  
• Connecting to the database via EXEC SQL CONNECT is not necessary as in the Pro*USE. Connection is made through SQL*Forms.
  
  
• Any user-exit variables that receive/write values from/to SQL*Forms or data tables must be declared in the DECLARE section of the user exit.
  
  
• The name of the user exit should not be a reserved word in Oracle. Also, avoid names that conflict with SQL*Forms commands, function codes, and externally defined names. SQL*Forms will convert the name of the user exit to uppercase before searching for the exit; therefore, the EXIT command must be in uppercase.
  
  
• Sometimes SQL*Forms I/O calls interfere with printer I/O routines. If they do, the user exit will be unable to utilize the printer. This restriction does not apply to user exits written in C.
  
  
• A user exit should not UPDATE a database table that is associated with a form; when the form COMMITs the data, it could overwrite what the user exit has saved.
  
  

It is possible to pass values to a user exit and receive a value in return. When a SQL*Forms trigger calls a user exit, it passes the following information:

• a character string that contains the exit name and specified parameters
  
  
• the trigger step failure message, if one is defined
  
  
• a flag indicating whether the user exit was called in normal or query mode
  
  

Global values can also be passed to a user exit by using IAF GETS.

The returning values from a user exit indicate whether it succeeded or failed. The return code is an integer constant, which is defined in SQL*Forms. There are three possible return values, which are outlined in Table 50.8.

Table 50.8. User-exit return values.

Return Type

Description

To incorporate a user exit into a form, take the following steps:

1. Write the user exit in a supported host language.
   
   
2. Precompile the source code.
   
   
3. Compile the modified source code.
   
   
4. Use the GENXTB utility to add an entry to the IAP program table IAPXTB in the module IAPXIT. (IAP is the component of SQL*Forms that runs a form).
   
   
5. Create a new IAP by linking the standard IAP modules, the modified IAPXIT module, and the new user-exit module.
   
   
6. In the form, define a trigger step to call the user exit.
   
   
7. Instruct operators to use the new IAP when running the form.
   
   

The following example shows a user exit that calculates an order total:

/**********************************************************************/

int

order_totl( void )

/**********************************************************************/

{

  EXEC SQL BEGIN DECLARE SECTION;

    int   order_num;

    ASCIZ_8 status_code;

    ASCIZ_80  msg;

    float merch_gross;

    float mult_prod_disc;

    float cust_disc;

    float merch_total;

    float reg_shipping;

    float upg_shipping;

    float frgn_shipping;

    float taxes;

    float grand_total;

  EXEC SQL END DECLARE SECTION;

  EXEC SQL WHENEVER SQLERROR CONTINUE;

  EXEC IAF GET GLOBAL.OT_ORDER_NUM INTO :order_num;

  EXEC SQL ALTER SESSION ENABLE COMMIT IN PROCEDURE;

  EXEC SQL EXECUTE

    BEGIN

      order_totaling.get_order_total( :order_num,

                                      :status_code,

                                      :msg,

                                      :merch_gross,

                                      :mult_prod_disc,

                                      :cust_disc,

                                      :merch_total,

                                      :reg_shipping,

                                      :upg_shipping,

                                      :frgn_shipping,

                                      :taxes,

                                      :grand_total

                                    );

    END;

  END-EXEC;

  if (sqlca.sqlcode != 0){

    sprintf(sql_errmsg,"order total: procedure call failed");

    sql_errlen=strlen(sql_errmsg);

    sqliem(sql_errmsg,&sql_errlen);

    return(IAPFAIL);

  }

  EXEC IAF PUT GLOBAL.OT_STATUS_CODE VALUES ( :status_code );

  EXEC IAF PUT GLOBAL.OT_MSG VALUES ( :msg );

  if (sqlca.sqlcode != 0){

    sprintf(sql_errmsg,"order total: procedure call failed");

    sql_errlen=strlen(sql_errmsg);

    sqliem(sql_errmsg,&sql_errlen);

    return(IAPFAIL);

  } else {

  EXEC IAF PUT GLOBAL.OT_MERCH_GROSS VALUES ( :merch_gross );

  EXEC IAF PUT GLOBAL.OT_MULT_PROD_DISC VALUES ( :mult_prod_disc );

  EXEC IAF PUT GLOBAL.OT_CUST_DISC VALUES ( :cust_disc );

  EXEC IAF PUT GLOBAL.OT_MERCH_TOTAL VALUES ( :merch_total );

  EXEC IAF PUT GLOBAL.OT_REG_SHIPPING VALUES ( :reg_shipping );

  EXEC IAF PUT GLOBAL.OT_UPG_SHIPPING VALUES ( :upg_shipping );

  EXEC IAF PUT GLOBAL.OT_FRGN_SHIPPING VALUES ( :frgn_shipping );

  EXEC IAF PUT GLOBAL.OT_TAXES VALUES ( :taxes );

  EXEC IAF PUT GLOBAL.OT_GRAND_TOTAL VALUES ( :grand_total );

  return(IAPSUCC);

  }

}

To call this user exit from a SQL*Forms trigger, you insert the following code:

user_exit('order_total');

Utilizing dynamic SQL or user exits can create problems with performance, and if your program is in a high-demand system this could become very undesirable. The next section of this chapter will look how to tune your SQL to improve performance.

Performance Tuning

When developing applications with embedded SQL, performance can become a major issue depending on what type of platform you may be using. This section provides easy-to-apply methods for improving the performance of your applications. It looks at what causes poor performance and how performance can be improved.

Poor Performance

One of the first causes of poor performance is high Oracle communication overhead. Oracle processes each SQL statement one at a time, which results in numerous calls to Oracle. If you are operating in a network environment, each call creates additional traffic on the network. The more traffic you have, the slower the performance will become.

The second cause of poor performance is inefficient SQL statements. Just because SQL statements can be written in several different ways and still get the same results, this does not mean that every statement is running efficiently. In some cases, full table scans will be occurring (which is time consuming if the table is large); in other cases, using indexes greatly speeds up the search.

The third cause of poor performance is managing cursors inefficiently. The result of not managing cursors correctly is additional parsing and binding, which adds noticeable processing overhead for Oracle.

These problems can be improved by reducing Oracle communication overhead or reducing processing overhead. The next section provides methods that will help reduce overhead and improve performance.

Improving Performance

Improving performance can make a dramatic difference in the way your application functions under normal or high usage. Two areas always should be considered when writing an SQL statement: Oracle communications and processing overhead.

Reducing Oracle Communication Overhead

There are two methods that can be used to reduce Oracle communication overhead: host arrays and PL/SQL blocks.

Using host arrays can dramatically boost your applications performance. You can issue one SQL statement to manipulate numerous rows, instead of issuing a SQL statement for each row. For example, if you wanted to update 1200 student grades, you could issue one SQL statement with a host array instead of 1200 with just a host variable. For more information, see the section on host arrays.

The second method to reducing Oracle communication overhead is to use embedded PL/SQL. If your application is database intensive, you can utilize PL/SQL blocks to group SQL statements together and then send the block to Oracle for processing.

After reducing the Oracle communication overhead, your next step should be to reduce processing overhead.

Reducing Processing Overhead

In order to reduce processing overhead, your SQL statement should be analyzed to ensure it is using the appropriate indexes, it is using row-locking properly, and it is managing cursors effectively. To ensure that indexes are being used properly, Oracle has provided tools that will help to identify problem areas.

The trace facility in conjunction with the EXPLAIN PLAN statement will generate statistics enabling you to identify which SQL statements are taking a lot of time to execute. This explain plan describes what database operations need to be carried out by Oracle to complete processing of the SQL statement that you have written. One of the most common problems with SQL statements is that full table scans are being done instead of indexes being utilized. The explain plan indicates if full table scans are being done; from this you can alter the SQL statements to utilize indexes.

Another area that can improve performance is how the database is locking data. To increase performance you want to lock only at the row level. This will enable many users (instead of just one) to access the table. Applications that do online transactions can drastically benefit from row locking verses table locking. The default value is different depending on what version of Oracle you are using. In Oracle Version 6, row-locking is the default.

Managing cursors can create an enormous amount of processing overhead. The easiest way to manage cursors is to declare them explicitly. This gives you the flexibility to control them as you need resources. Remember that you need to PREPARE, DECLARE, OPEN, and CLOSE explicit cursors in dynamic SQL—especially with methods three and four. After a cursor has been PREPAREd (which does the parsing), it can be used multiple times until it is CLOSEd. This can drastically reduce the parsing and binding that is done with each cursor.

Now that you have stepped through each part of creating an embedded SQL host program, it would be advisable to stay current on what new features the precompilers have. Oracle has taken extra effort in improving its tools with each step; as a programmer, you should capitalize on these features.

New Features in Version 1.4

The new features in Version 1.4 precompilers help meet the needs of professional software developers. Some of the features are as follows:

• New debugging aid. The SQLCA stores additional runtime information about the outcome of SQL operations.
  
  
• Enhanced WHENEVER statement. The improved WHENEVER statement now lets you take actions when an error or warning is detected. With previous versions you only had three choices: GOTO, CONTINUE, or STOP. Added to version 1.4 is the DO statement, which allows for procedural functions to be taken.
  
  
• Revised HOST option. With previous versions of precompilers, the HOST parameter indicated what host language was being used. Version 1.4 uses separate precompilers executables each designed for a specify language.
  
  
• In previous versions of Oracle precompilers, options for setting the area size (which is initially set for cursor) had to be specified. With the current version of precompilers, resizing is automatically done. This feature makes the AREASIZE and REBIND options obsolete.
  
  
• Previous versions of precompilers generated several database calls per embedded SQL statement. In Version 1.4, precompilers generate only one (bundled) database call per embedded SQL statement.
  
  

Remember to try and keep current on the new features Oracle includes in its precompilers. This could make a dramatic difference in the performance and functionality of your program.

Summary

ORACLE precompilers provide an excellent tool for programmers to create dynamic applications. This chapter provided information on what a precompiler does, the benefits of being able to embed SQL statements, how to use it a precompiler, and how to create a host program.

This concludes the section on precompilers. I hope that the information has been beneficial to you and has given you some quick tips to enhance performance.