4 Developing the Ada Application

Program Structure

The developer determines the structure of an application program that uses SQL*Module. A significant advantage that you obtain from using SQL*Module is that it imposes very few special requirements or constraints on the program design, unlike some other SQL programmatic interfaces.

The code that you write is purely in the language of the host application program. There is no need for special declare sections, embedded SQL statements, and special error handling and recovery. Database operations are mostly transparent to the application program developer, being taken care of by the Module Language or PL/SQL stored procedures.

There are, however, some SQL concepts of which the host application developer must be aware

• error handling, and the use of the SQLSTATE or SQLCODE status parameter.

• the concept of null, and how to use indicator variables to handle it

• the concept of a cursor

Error Handling

Each Module Language procedure that is called from the host application must contain a parameter that returns status information to the application. There are two status parameters that you can use: SQLCODE and SQLSTATE. SQLCODE returns an integer value, while SQLSTATE returns a five-character string that contains an alphanumeric code.

SQLCODE is provided for compatibility with applications written to the 1989 SQL standards; new applications should use the SQLSTATE parameter.

When calling stored database procedures through an RPC stub, you include SQLCODE or SQLSTATE in the parameter list of the WITH INTERFACE clause in the procedure's package specification. See "The WITH INTERFACE Clause".

procedure ERROR_MESSAGE (ctx oracle_sqllib.sql_context,
                         msg_buf system.address,
                         msg_buf_len sql_standard.int);

procedure ERROR_MESSAGE (msg_buf:out     system.address,
                         msg_buf_len:out sql_standard.int);

SQLSTATE

SQLSTATE is a five-character alphanumeric output parameter that indicates the completion status of the procedure. It is declared as SQL_STANDARD.SQLSTATE_TYPE.

SQLSTATE status codes consist of a two-character class code followed by a three-character subclass code. Aside from the class code 00 ("successful completion"), the class code denotes the category of the exception. Also, aside from the subclass code 000 ("not applicable"), the subclass code denotes a specific exception within that category. For example, the SQLSTATE value '22012' consists of class code 22 ("data exception") and subclass code 012 ("division by zero").

Each of the five characters in a SQLSTATE value is a digit (0..9) or an uppercase Latin letter (A..Z). Class codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for the predefined conditions (those defined in the SQL92 specification). All other class codes are reserved for implementation-defined sub-conditions. All other subclass codes are reserved for implementation-defined sub-conditions. Table 4-1 shows the coding scheme.

Figure 4-1 SQLSTATE

Description of "Figure 4-1 SQLSTATE"

Table 4-1 Predetermined Classes

Class	Condition
02	no data
07	dynamic SQL error
08	connection exception
0A	feature not supported
21	cardinality violation
22	data exception
23	integrity constraint violation
24	invalid cursor state
25	invalid transaction state
26	invalid SQL statement name
27	triggered data change violation
28	invalid authorization specification
2A	direct SQL syntax error or access rule violation
2B	dependent privilege descriptors still exist
2C	invalid character set name
2D	invalid transaction termination
2E	invalid connection name
33	invalid SQL descriptor name
34	invalid cursor name
35	invalid condition number
37	dynamic SQL syntax error or access rule violation
3C	ambiguous cursor name
3D	invalid catalog name
3F	invalid schema name
40	transaction rollback
42	syntax error or access rule violation
44	with check option violation
HZ	remote database access

Note: The class code HZ is reserved for conditions defined in International Standard ISO/IEC DIS 9579-2, Remote Database Access.

Appendix D, "SQLSTATE Codes" shows how Oracle errors map to SQLSTATE status codes. In some cases, several Oracle errors map to a status code. In other cases, no Oracle error maps to a status code (so the last column is empty). Status codes in the range 60000 .. 99999 are implementation-defined.

Obtaining the Number of Rows Processed

Starting with release 8.0, function rows_processed, in the public package oracle_sqllib, returns the number of rows processed by the last SQL statement.

The prototypes are:

function ROWS_PROCESSED return integer;

and

function ROWS_PROCESSED (ctx oracle_sqllib.sql_context) return integer;

where the context, ctx, has been allocated previously.

Handling Nulls

A database column or a SQL expression can have a value, or it can have a special status called null. A null means the absence of a value. A numeric value or a special string encoding cannot be used to indicate a null, since all allowable numeric or string values are reserved for actual data. In a SQL*Module application, you must use an indicator variable to test a returned value for a null, or to insert a null into a database column.

Note: The term indicator variable is also referred to as an indicator parameter when discussing a variable being passed to or retrieved from a procedure.

PROCEDURE get_commission ( 
                      :commission   REAL, 
                      :comm_ind     SMALLINT, 
                      :emp_number   INTEGER, 
                       SQLSTATE); 
   SELECT comm INTO :commission INDICATOR :comm_ind 
      FROM emp WHERE empno = :emp_number; 

EMPNO := 7499; 
GET_COMMISSION (COMMISSION, COMM_INDICATOR, EMPNO, SQLSTATE); 
if COMM_INDICATOR < 0 then 
    PUT_LINE("Commission is null."); 
else 
    PUT("Commission is "); 
    PUT(COMMISSION); 
    NEW_LINE; 
end if; 

PROCEDURE new_part ( 
        :part_no     INTEGER, 
        :description CHAR(200), 
        :bin_number  INTEGER, 
        :bin_no_ind  SMALLINT, 
         SQLSTATE); 
 
   INSERT INTO inventory (part_number, description, bin_no) 
       VALUES (:part_no, :description, 
               :bin_number INDICATOR :bin_no_ind); 

Cursors

Programs that retrieve data from a table can work in two different ways. In one case, a query might be constructed that expects either one row of data to be returned, or no row. For example, if the program performs a request such as ''give me the name of the employee whose employee number is 7499", where the employee number is a primary key of the table (and hence, by definition, unique), the request either returns the name of the employee whose employee number is 7499, or returns an indication that no such employee exists in the table.

If no employee exists with that number, the query procedure returns a ''no data found" indication in the SQLCODE or SQLSTATE parameter.

For Oracle to process any SQL statement, a cursor is required. However, SQL*Module implicitly defines a cursor for INSERT, UPDATE, and DELETE statements, as well as SELECT statements.

However for queries that can return multiple rows, an explicit cursor must be defined in the module or stored package to fetch all the rows. You can use static cursors, or cursor variables. See "Cursors" for a description of cursor variables.

See the code in "Module Language Sample Program" for several examples that use explicit cursors.

Specification Files

The SQL*Module compiler generates specification files. These are text files that contain declarations for the module or interface procedures that SQL*Module generates.

You must include the specification file directly in the source of your host application. The name of the specification file is the base name of the Module Language output file for SQL*Module, with a system-specific extension. These extensions are documented in "Specification File".

In Ada applications, you must compile the specification file (or files) that SQL*Module generates. You then include the specification for the module procedures or stubs in each application that calls module procedures or stubs using the with context clause.

The naming of specification files is discussed in detail in Chapter 6, "Demonstration Programs".

Calling a Procedure

You call procedures generated by SQL*Module using the normal procedure call format of the host language. Procedures can only return values in parameters, including the SQLCODE and SQLSTATE parameters. The generated procedures are not functions.

Arrays as Procedure Arguments

SQL*Module supports array bind and define variables as arguments to procedures and functions:

PROCEDURE foo (:arrname ARRAY(n) OF type, SQLCODE);

where n is the size of arrname, and type is listed in "Globalization Support".

For example:

PROCEDURE selempno (:eno ARRAY(14) of INTEGER, SQLCODE);
   SELECT empno INTO :eno FROM emp;

Note: Host arrays are allowed in SELECT, FETCH, INSERT, UPDATE and DELETE statements only.

Restrictions:

1. Arrays may not be specified when RPC_GENERATE=yes or STORE_PACKAGE=yes. See "Stored Packages" for more information. See both these command-line options in Chapter 5, "Running SQL*Module".

2. The maximum dimension of an array is 32000

3. SQL*Module does not allow multi-dimension arrays.

Globalization Support

Not all writing systems can be represented using the 7-bit or 8-bit ASCII character set. Some languages require multibyte character sets. Also, countries have differing ways of punctuating numbers, and representing dates and currency symbols.

Oracle provides Globalization Support, which lets you process single-byte and multibyte character data and convert between character sets. It also lets your applications run in different language environments. With Globalization Support, number and date formats adapt automatically to the language conventions specified for a user session. Thus, Globalization Support allows users around the world to interact with Oracle in their native languages.

You control the operation of language-dependent features by specifying various Globalization Support parameters. Default values for these parameters can be set in the Oracle initialization file. The following table shows what each Globalization Support parameter specifies:

The main parameters are NLS_LANGUAGE and NLS_TERRITORY. NLS_LANGUAGE specifies the default values for language-dependent features, which include

• language for Server messages

• language for day and month names

• sort sequence

NLS_TERRITORY specifies the default values for territory-dependent features, which include

• date format

• decimal character

• group separator

• local currency symbol

• ISO currency symbol

You can control the operation of language-dependent Globalization Support features for a user session by specifying the parameter NLS_LANG as follows:

NLS_LANG = <language>_<territory>.<character set> 

where language specifies the value of NLS_LANGUAGE for the user session, territory specifies the value of NLS_TERRITORY, and character set specifies the encoding scheme used for the terminal. An encoding scheme (usually called a character set or code page) is a range of numeric codes that corresponds to the set of characters a terminal can display. It also includes codes that control communication with the terminal.

You define NLS_LANG as an environment variable (or the equivalent on your system). For example, on UNIX using the C shell, you might define NLS_LANG as follows:

setenv NLS_LANG French_Canadian.WE8ISO8859P1 

SQL*Module fully supports all the Globalization Support features that allow your applications to process multilingual data stored in an Oracle Database version 8 database. For example, you can run a SQL*Module-derived client application that interacts with a remote server, where the client and the server are using different character sets, possibly with a different number of bytes each character. In these contexts, remember that specification of the lengths of string types, such as the SQL datatype CHARACTER(N), is always specified in bytes, not characters.

You can even pass Globalization Support parameters to the TO_CHAR, TO_DATE, and TO_NUMBER functions. For more information about Globalization Support, see the Oracle Database Application Developer's Guide - Fundamentals.