Release 8.0
Part Number A58231-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| SQL*Module for Ada Programmer's Guide Release 8.0 Part Number A58231-01 |
|
This chapter describes the criteria that a Ada application must meet when accessing module procedures, or when calling RPC stubs generated by SQL*Module. Topics covered include
The sample programs in this chapter are source code listings for the Module Language procedures that are called by the sample programs in Chapter 6, "Demonstration Programs", and a set of SQL statements that create and partially populate the example tables. These sources are also available on-line, in the demo directory.
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
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 and/or SQLSTATE in the parameter list of the WITH INTERFACE clause in the procedure's package specification. See "The WITH INTERFACE Clause".
SQLCODE is an output parameter that can be included in a module procedure, and in the WITH INTERFACE clause in PL/SQL stored package specifications. SQLCODE returns a value that indicates whether a procedure completed successfully, completed with warnings, or did not complete due to an error.
SQLCODE returns three kinds of values:
0
Indicates that the procedure completed with no errors or warnings.
< 0
Indicates that an error occurred during execution of the procedure.
+100
Indicates that a SQL statement did not find a row on which to operate.
Negative SQLCODE values are Oracle message numbers. See the Oracle8 Messages manual for a complete list of Oracle codes and their accompanying messages. See the next section, "SQLSTATE", for mappings between Oracle error numbers and SQLSTATE values.
The procedure error_message in the public package oracle_sqllib was introduced in release 8.0. This procedure obtains the text associated with the SQLCODE of the latest error returned. The prototypes are (with and without a runtime context):
procedure ERROR_MESSAGE (ctx oracle_sqllib.sql_context, msg_buf system.address, msg_buf_len sql_standard.int);
and:
procedure ERROR_MESSAGE (msg_buf:out system.address, msg_buf_len:out sql_standard.int);
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. Figure 4-1 shows the coding scheme.
Table 4-1 shows the classes predefined by SQL92.
Table 4-1 Predefined Classes| Class | Condition |
|---|---|
| 00 | successful completion |
| 01 | warning |
| 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.
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.
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.
From the host language point of view, an indicator variable is a small integer that is passed to a procedure. In the SQL statement of the procedure, the indicator is associated with the corresponding host parameter. For example, the Module Language procedure below performs a simple one-row SELECT (the host parameter in the WHERE clause is assumed to be a primary key):
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;
In an Ada application, you call this procedure and test for a possible null in the returned COMMISSION as follows:
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;
So if an indicator variable is less than zero when a procedure returns, the associated host parameter has an undefined value.
You can also associate indicator variables with input parameters, for column values that are used to insert a new row into a table, or update an existing row. If the value in the indicator variable is greater than or equal to zero, the value in the associated parameter is used as the input value. If the indicator variable is set to -1, the value in the associated parameter is ignored, and a null is inserted as the column value.
For example, the following module procedure inserts a new row into an inventory table:
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);
When you call this procedure with the parameter bin_no_ind set to -1, any value in the parameter bin_number is ignored, and a null is inserted into the BIN_NO column of the table.
If the host language parameter is a character type, and has an associated indicator variable, a returned indicator value greater than zero indicates that the returned value was truncated. The value of the indicator is the original (un-truncated) length, in bytes, of the column or expression value.
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.
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".
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.
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 "National Language 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.
Not all writing systems can be represented using the 7-bit or 8-bit ASCII character set. Some languages require multi-byte character sets. Also, countries have differing ways of punctuating numbers, and representing dates and currency symbols.
Oracle provides National Language Support (NLS), which lets you process single-byte and multi-byte character data and convert between character sets. It also lets your applications run in different language environments. With NLS, number and date formats adapt automatically to the language conventions specified for a user session. Thus, NLS allows users around the world to interact with Oracle in their native languages.
You control the operation of language-dependent features by specifying various NLS parameters. Default values for these parameters can be set in the Oracle initialization file. The following table shows what each NLS parameter specifies:
The main parameters are NLS_LANGUAGE and NLS_TERRITORY. NLS_LANGUAGE specifies the default values for language-dependent features, which include
NLS_TERRITORY specifies the default values for territory-dependent features, which include
You can control the operation of language-dependent NLS 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 NLS features that allow your applications to process multilingual data stored in an Oracle8 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 per 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 NLS parameters to the TO_CHAR, TO_DATE, and TO_NUMBER functions. For more information about NLS, see the ">Oracle8 Application Developer's Guide.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|