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 SQL standard Module Language, including Oracle's extensions to standard Module Language. It discusses the following topics:
This chapter does not cover the use of SQL*Module to provide interfaces to stored procedures. See Chapter 3, "Accessing Stored Procedures" for information about calling stored procedures from a host application.
A module is a single file that contains
The easiest way to see how a module is constructed is to look at an example. The small module below contains a cursor declaration, procedures that open and close the cursor, and a procedure that uses the cursor to fetch data from the familiar EMP table. Two consecutive dashes (- -) begin a comment, which continues to the end of the line. Case is not significant.
-- the preamble (contains three clauses) -- MODULE EXAMPLE1_MOD -- Define a module named example1_mod. LANGUAGE Ada -- The procedures are compiled into -- Ada, and called from an -- Ada application. AUTHORIZATION SCOTT/TIGER -- Use Scott's tables. -- His password is "tiger" -- (the password does not have to -- be specified in the module file). -- Declare a cursor for a SELECT statement that -- fetches four columns from the EMP table. -- dept_number will be specified -- in the procedure that opens the cursor. DECLARE cursor1 CURSOR FOR SELECT ename, empno, sal, comm FROM emp WHERE deptno = :dept_number -- Define a procedure named "open_cursor1" to open the cursor. -- After the procedure name is a comma-separated parameter list -- enclosed in ()'s. PROCEDURE open_cursor1 ( :dept_number INTEGER, SQLCODE); OPEN cursor1; -- The "fetch_emp_data" procedure gets data from the cursor. -- SQLCODE will return as 100 when there -- is no more data. PROCEDURE fetch_emp_data ( :empno INTEGER, :empname VARCHAR2(10), :sal REAL, :commission REAL, :comm_ind SMALLINT, SQLCODE); -- the SQL command is a FETCH on the cursor FETCH cursor1 INTO :empname, :empno, :sal, :commission INDICATOR :comm_ind; -- Define a procedure to close the cursor. PROCEDURE close_cursor1 (SQLCODE); CLOSE cursor1; -- Connect to a database PROCEDURE do_connect ( :dbname VARCHAR2(12), :username VARCHAR2(12), :passwd VARCHAR2(12), SQLCODE); CONNECT TO :dbname USER :username USING :passwd; -- Disconnect PROCEDURE do_disconnect (SQLCODE); DISCONNECT CURRENT;
Note: If you are familiar with the syntax of SQL89 Module Language, you should note that the newer SQL92 syntax is used in this example. This includes parentheses around a comma-separated parameter list, colons before parameters, and use of the INDICATOR keyword. This syntax is supported by the current release of SQL*Module, and is used throughout this Guide.
In this example, the LANGUAGE clause (in the second line of the module) specifies "Ada". This module will be compiled into Ada code. For SQL*Module, this clause is optional. When present, it is, in effect, a comment.
When SQL*Module compiles this module, it transforms the procedures into Ada language procedures that open the cursor and call library routines to fetch the data. SQL*Module also generates a package specification file, which must be compiled into the Ada library and referenced in the host application using a with context clause. See Chapter 5, "Running SQL*Module" in this Guide for information on running SQL*Module, and Chapter 6, "Demonstration Programs" for information about Ada specification files.
To complete the example, a short Ada program that calls the procedures defined in the module file in "An Example Module" follows.
-- Include TEXT_IO,SQL_STANDARD and EXAMPLE1_MOD package specs. with SQL_STANDARD, TEXT_IO, EXAMPLE1_MOD; use SQL_STANDARD; -- Define the main procedure. procedure EXAMPLE1_DRV is -- Instantiate new packages for I/O on SQL_STANDARD datatypes. package STD_INT_IO is new text_io.integer_io(SQL_STANDARD.INT); use STD_INT_IO; package SQLCODE_IO is new text_io.integer_io(SQL_STANDARD.SQLCODE_TYPE); use SQLCODE_IO; package STD_FLOAT_IO is new text_io.float_io(SQL_STANDARD.REAL); use STD_FLOAT_IO; -- Begin with declarations of all program variables, -- including parameters for the module procedures. SERVICE_NAME : string(1..12) := "INST1_ALIAS "; USERNAME : string(1..12) := "SCOTT "; PASSWORD : string(1..12) := "TIGER "; DEPT_NUMBER : SQL_STANDARD.INT; EMPLOYEE_NUMBER : SQL_STANDARD.INT; EMPLOYEE_NAME : string(1..10); SALARY : SQL_STANDARD.REAL; COMMISSION : SQL_STANDARD.REAL; COMM_IND : SQL_STANDARD.SMALLINT; SQLCODE : SQL_STANDARD.SQLCODE_TYPE; LENGTH : integer; CONNECT_ERROR : exception; SQLCODE_ERROR : exception; begin -- Call a module procedure to connect -- to the Oracle server. EXAMPLE1_MOD.DO_CONNECT (SERVICE_NAME, USERNAME, PASSWORD, SQLCODE); -- Test SQLCODE to see if the connect succeeded. if SQLCODE /= 0 then raise CONNECT_ERROR; end if; TEXT_IO.NEW_LINE(2); -- Get an input value from user to use -- in opening the cursor. TEXT_IO.PUT("Enter department number: "); STD_INT_IO.GET(DEPT_NUMBER); TEXT_IO.NEW_LINE; -- Call the module procedure to open the cursor. -- You open the cursor using the input parameter (dept_number). EXAMPLE1_MOD.OPEN_CURSOR1(DEPT_NUMBER, SQLCODE); -- If SQLCODE indicates error, call exception handler. if SQLCODE < 0 then raise SQLCODE_ERROR; end if; TEXT_IO.PUT_LINE("Employee ID Number Salary Commission"); TEXT_IO.PUT("----------------------------------------"); -- Call the FETCH procedure in a loop, to get -- the employee data. loop EXAMPLE1_MOD.FETCH_EMP_DATA (EMPLOYEE_NUMBER, EMPLOYEE_NAME, SALARY, COMMISSION, COMM_IND, SQLCODE); TEXT_IO.NEW_LINE; -- When SQLCODE = 100, no more rows to fetch. exit when SQLCODE = 100; -- When SQLCODE less than 0, an error occurred. if SQLCODE < 0 then raise SQLCODE_ERROR; end if; TEXT_IO.PUT(string(EMPLOYEE_NAME)); STD_INT_IO.PUT(EMPLOYEE_NUMBER, WIDTH => 9); STD_FLOAT_IO.PUT(SALARY, FORE => 6, AFT => 2, EXP => 0); if COMM_IND = 0 then STD_FLOAT_IO.PUT(COMMISSION, FORE => 9, AFT => 2, EXP => 0); else TEXT_IO.PUT(" Null"); end if; end loop; TEXT_IO.NEW_LINE(2); -- Call the procedure to close the cursor. EXAMPLE1_MOD.CLOSE_CURSOR1(SQLCODE); -- Call the procedure to disconnect. EXAMPLE1_MOD.DO_DISCONNECT(SQLCODE); -- Handle the error exception. exception when SQLCODE_ERROR => TEXT_IO.NEW_LINE(2); TEXT_IO.PUT("Error fetching data, SQLCODE returns "); PUT(SQLCODE); TEXT_IO.NEW_LINE(2); when CONNECT_ERROR => TEXT_IO.PUT("Connect failure to " & string(SERVICE_NAME)); TEXT_IO.NEW_LINE(2); end EXAMPLE1_DRV;
This example demonstrates several important points about SQL*Module:
This section is an informal guide to Module Language syntax, including Oracle's extensions to the language. See Appendix B to this Guide for a formal description of Module Language syntax.
A module is contained in a single file and consists of
The preamble is a set of clauses at the beginning of a module file that describes the module. The clauses are
The MODULE clause gives a name to the module. The argument is the module name, which must be a legal SQL identifier.
Note: A SQL identifier is a string containing only the 26 letters A through Z, the digits 0 through 9, and the underscore ("_"). The identifier must start with a letter, and cannot be longer than 30 characters (18 characters to comply with the SQL89 standard). You can use lowercase letters (a..z), but a SQL identifier is not case sensitive. So, the identifiers "THIS_IDENTIFIER" and "this_identifier" are equivalent. The characters'$' and'#' are also allowed in a SQL identifier, but you should avoid using them, as they have special meaning in many languages and operating systems.
The module name must be supplied. The module name must be the same as the name of the Ada library unit that you use when you store the Ada-compiled output in your Ada library.
The LANGUAGE clause specifies the target language. It takes one argument -- the language name, which must be Ada (case is not significant).
The LANGUAGE clause is optional in SQL*Module. See Chapter 5, "Running SQL*Module" for more information about running SQL*Module.
The AUTHORIZATION clause is required. This clause can determine, or help to determine, the database and schema that SQL*Module uses at compile time.
The argument to the AUTHORIZATION clause can take one of four forms:
- scott
- scott/tiger
scott@{instance_alias | net8_connection_string} }
- scott/tiger@{instance_alias | net8_connection_string}
The instance_alias is an alias containing a database name defined in the tnsnames.ora file. For more information, talk to your database administrator, or see the manual Net8 Administrator's Guide.
If you do not include the password in the AUTHORIZATION clause, you can specify it when you run SQL*Module, using the USERID command line option. If you do not specify a USERID value that contains a password, SQL*Module prompts you for a password. If a database name is not supplied, SQL*Module connects using the default database for the specified user.
Note: For security reasons, omit the password in the SQL*Module or in any configuration file. You will be prompted for the password at runtime.
An application that calls module procedures is in effect submitting SQL cursor declarations and SQL statements to Oracle for processing at runtime. The application runs with the privileges of the user executing the application, not the schema specified either in the AUTHORIZATION clause or at runtime, when the Module Language code was compiled by SQL*Module.
So, when the application is executed, the user must be able to connect to the database using the same schema as that specified when the modules were compiled, in order to guarantee access to all database objects referenced in the modules.
When a query can return multiple rows of data, you must declare a cursor for the statement. There are two different kinds of cursor that you can use in Module Language. You can declare an ANSI-standard cursor in your module file, and write module language procedures to OPEN the cursor, FETCH from it, and CLOSE it. Or, you can use a cursor variable. Cursor variables are described in "Cursor Variables".
Using a cursor, you retrieve data one row at a time using the SQL FETCH command. Standard cursors are established in Module Language using the DECLARE CURSOR statement.
Note: The cursor name is a SQL identifier; it is not a procedure name. So, the cursor name does not need to be a valid Ada identifier.
The SQL statement in a DECLARE CURSOR clause must not include an INTO clause. The INTO clause is used in the FETCH command in the procedure that references the cursor.
You cannot use parameters when you declare a standard cursor. A placeholder is used instead. Placeholders in a cursor declaration must correspond by name to the parameters in the procedure that opens the cursor.
Note the way the variable dept_number is used in the following module fragment, taken from the example used earlier in this chapter:
DECLARE cursor1 CURSOR FOR SELECT ename, empno, sal, comm FROM emp WHERE deptno = :dept_number PROCEDURE open_cursor1 (:dept_number INTEGER, SQLCODE); OPEN cursor1;
The cursor declaration is NOT terminated with a semicolon. If you do so, SQL*Module generates a syntax error.
Caution: If the application consists of several modules, a cursor cannot be declared in one module, then referenced in another. Nevertheless, cursor names must be unique across all modules in the application.
A procedure definition consists of a
The procedure name is a SQL identifier, and should also be a legal Ada identifier. Procedure names must be unique in the application.
Each procedure in a module must have a distinct name. The procedure name space extends over all modules of an application, so when the application uses more than one module, each procedure must still have a distinct name.
The parameter list contains one or more formal parameters. Each parameter must have a distinct name, which is a SQL identifier. One of the parameters must be a status parameter: SQLSTATE or SQLCODE. It can appear anywhere in the parameter list. You can include both. See "Status Parameters" for more information about status parameters.
In SQL89 Module Language, you defined a procedure using the syntax
PROCEDURE proc_name <param_1> <datatype_1> <param_2> <datatype_2> SQLCODE; <sql_statement>;
where <param_n> is a formal parameter name and <datatype_n> is a SQL datatype. Following the SQL92 standard, SQL*Module allows the syntax
PROCEDURE proc_name ( :<param_1> <datatype_1>, :<param_2> <datatype_2>, SQLSTATE | SQLCODE ); <sql_statement>;
where the parameter list is surrounded by parentheses, and parameters are separated by commas.
Note: You cannot mix SQL89 and SQL92 syntax. If you separate the elements of the parameter list using commas, you must also place parentheses around the parameter list.
When SQL*Module generates the output procedures, the formal parameters appear with the same names, and in the same order, as they appear in the module procedure. You can use the parameter access conventions appropriate for the Ada language when calling the output procedure from the application. Thus Ada programmers can use named parameter association in place of, or in addition to, positional parameter association.
Table 2-1 lists the SQL and Oracle datatypes that you can use in a module parameter list.For more information about arrays, see "Arrays as Procedure Arguments".
The SQL standard for Module Language allows the use of only a subset of the SQL datatypes for Ada.
Note: All parameters for Module Language procedures must be scalars, arrays, or strings. Records and access types are not supported.
Module Language supports the following SQL statement:
The DELETE and UPDATE commands may be either searched (the normal mode) or positioned (using the WHERE CURRENT OF <cursor_name> clause). You can use the OPEN command only for ANSI-standard cursors. You must open cursor variables on the Oracle Server, using PL/SQL code.
In general, Module Language is not case sensitive. You can enter keywords and identifiers in any mixture of uppercase and lowercase. Case is significant, however, in character string literals.
Text in a module file is free form. White space (spaces, tabs, and new lines) can be placed anywhere in the file to improve readability. The only exception to this is that identifiers, keywords, and string literals cannot be continued to a new line.
The maximum length of a line in a module is 512 characters.
SQL*Module allows comments in a module file. You can place comments anywhere that white space can appear, except in string literals.
There are two comment styles: SQL-style comments and C-style comments. A SQL-style comment starts with two consecutive dashes, which can appear anywhere on a line, and ends at the end of the line. For example:
-- This is a SQL(or Ada)style comment. -- For multiline comments, you must place the comment -- dashes on each line.
A C-style comment begins with a slash immediately followed by an asterisk (/*), and ends at the next occurrence of an asterisk immediately followed by a slash (*/). C-style comments can span more than one line. C-style comments cannot be nested.
The following example demonstrates the C-style comment:
/* * This comment style is often used to * introduce a procedure. */
You use indicator parameters to set the null/not null status of another (associated) parameter, or to "indicate" if a column value retrieved on a query was null. In the module procedure parameter list, an indicator parameter always has a SMALLINT type. In the SQL statement, the indicator parameter follows the associated parameter, with no comma separation. The SQL92 standard allows the keyword INDICATOR to be used to separate the indicator parameter and its associated parameter.
In the following example, grade_indic is an indicator parameter:
PROCEDURE get_grade ( :grade REAL, :grade_indic SMALLINT, :class_number INTEGER, :student_id INTEGER, SQLCODE); SELECT grade INTO :grade INDICATOR :grade_indic FROM enrollment WHERE class_no = :class_number AND student_id = :student_id;
Following the SQL89 standard, the SELECT statement above would be written without the INDICATOR keyword, as follows:
SELECT grade INTO :grade :grade_indic FROM enrollment WHERE class_no = :class_number AND student_id = :student_id;
SQL*Module allows both uses of indicator parameters.
When an indicator parameter is returned from a procedure (an OUT parameter), as in the query example above, its returned value has the following meanings:
-1
The Oracle column or expression is null. The value of the associated parameter (grade in this example) is indeterminate.
0
Oracle assigned a column or expression value to the associated parameter.
> 0
For character data, Oracle passed a truncated column value in the associated parameter. The value of the indicator parameter shows the original length of the value in the database column.
When indicator parameters are passed as IN parameters, you must set the value in your Ada program. A value of -1 means that Oracle will assign null to the column (regardless of the value of the associated parameter), and a value of zero or greater means that Oracle will use the value of the associated parameter in the UPDATE or INSERT command. Positive values greater than zero have no special meaning; they are equivalent to zero.
Caution:If you try to assign a null to a database column that has the NOT NULL constraint, an Oracle error is returned at runtime.
The following code fragment shows an Ada driver that calls a Module Language procedure with a null indicator parameter value:
with SQL_STANDARD; procedure DRV is SQLCODE : SQL_STANDARD.SQLCODE_TYPE; EMPLOYEE : string(1..10) := "SCOTT "; COMMISSION : SQL_STANDARD.REAL := 2000.0; COMM_IND : SQL_STANDARD.SMALLINT := -1; begin . . . UPDATE_COMMISSION(EMPLOYEE, COMMISSION, COMM_IND, SQLCODE); . . . end;
The corresponding Module Language procedure is:
PROCEDURE update_commission ( :employee_name VARCHAR2(10), :commission REAL, :comm_ind SMALLINT, SQLCODE); UPDATE emp SET comm = :commission INDICATOR :comm_ind WHERE ename = :employee_name;
In this example, the parameter commission with a value of 2000.0 is passed to the update_commission procedure. But, since the indicator parameter is set to -1, employee Scott's commission is set to null in the EMP table.
There are two special status parameters: SQLSTATE and SQLCODE. The status parameters return values to the calling Ada application that show if
You can place either one or both of the status parameters anywhere in the parameter list. They are always shown last in this Guide, but that is just an informal coding convention. The status parameters are not preceded by a colon, and they do not take a datatype specification. You cannot directly access the status parameters in the SQL statement in the procedure; they are set by SQL*Module.
SQLSTATE is the preferred status parameter; SQLCODE is retained for compatibility with older standards.
For more information about the status parameters and the values they return, see "Error Handling".
SQL*Module compile time error messages have the MOD prefix. The codes and messages are listed in Oracle8 Messages.
The connect statement associates a program with a database, and sets the current connection. The syntax of the command is shown in the following syntax diagram. Key words, which you must spell exactly as shown, are in upper case; tokens you supply are in lower case. If a clause is optional, it is shown off the main path, which flows to from left to right. For a more complete discussion of syntax diagrams, see Appendix B, "Module Language Syntax".
A db-env (database environment) is a Net8 connect string or instance-alias. The conn-name (connection name) is optional. For multiple connections, you must specify the connection names. You can omit it if you will only use a single connection. The USING clause is optional. A passwd is the password..
Connecting as DEFAULT results in a connection to Oracle using either TWO_TASK (if it applies to your operating system) or ORACLE_SID and the account specified by the parameter os_authent_prefix in your file init.ora. The optional token passwd is the password.
The ANSI SQL92 standard does not allow db-env to be optional. This is an Oracle extension (which will be flagged by the FIPS option) which enables a connection to the default server as a specific user.
You must use Net8 in SQL*Module applications. Note that passwd can only be a variable and not a character string literal. All other variables can be either a character string literal or a variable previously defined, preceded by ":".
Here is a illustative code fragment from a module named demo.mad, which contains the following procedure to do a connect:
... PROCEDURE ora_connect (:dbid VARCHAR2(14), :dbname VARCHAR2(14), :uid VARCHAR2(14), :pwd VARCHAR2(14), SQLCODE); CONNECT TO :dbid AS :dbname USER :uid USING :pwd; ...
An Ada application can contain these statements to do the connect:
... pwd : constant string := "tiger"; ... DEMO.ORA_CONNECT("inst1","RMT1","scott",PWD,SQLCODE); if SQLCODE /= 0 then ...
For more information, see the Net8 Administrator's Guide and the Oracle8 Administrator's Guide.
The set connection statement sets the current connection. Its syntax is:
SET CONNECTION { connection-name | DEFAULT }
DEFAULT is a special case of the connection-name, '/', at the current ORACLE_SID.
The disconnect command ends an association between an application and a database environment. It can be summarized as:
DISCONNECT { connection-name | ALL | CURRENT | DEFAULT}
The full ANSI semantics are not followed. Instead of raising an exception condition if the connection has an active transaction, the transaction is (implicitly) rolled back and the connection(s) disconnected.
DISCONNECT ALL only disconnects connections which are established by SQLLIB (that is, by SQL*Module).
DISCONNECT DEFAULT and DISCONNECT connection-name terminate only the specified connection.
DISCONNECT CURRENT terminates the connection regarded by SQLLIB as the "current connection" (either the last connection established or the last connection specified in a SET CONNECTION statement).
After the current connection is disconnected, you must execute a set connection or a connect statement to establish a new current connection. Then you can execute any statement that requires a valid database connection.
You must explicitly commit or roll back the current transaction before disconnecting. If you do not commit the transaction before disconnecting, or if the program exits before committing, the current transaction is automatically rolled back.
Here is an example of two procedures from the module demo.mad illustrating the disconnect command:
... PROCEDURE ora_disconnect (:dbname VARCHAR2(14), SQLCODE) ; DISCONNECT :dbname; ... PROCEDURE ora_commit(SQLCODE); COMMIT WORK;
these procedures are called from your application as follows:
... DEMO.ORA_COMMIT(SQLCODE); DEMO.ORA_DISCONNECT("RMT1", SQLCODE); ...
A required commit command was executed using the procedure ora_commit (which is also in the file demo.mad) just before the disconnect. .
Starting with release 8.0, multi-tasking Ada programs are supported by SQL*Module. The new commands that you use in a multi-tasking program are described in the following sections:
This command initializes the process for later use with Ada tasks. It must be called prior to the creation of any Ada tasks. It is:
ENABLE THREADS;
The datatype, SQL_CONTEXT, is used to support multi-tasking applications. It points to SQLLIB's runtime context. You pass the context as a parameter to SQL*Module procedures. If it is passed, then it indicates which SQLLIB runtime context will be used for execution. If no SQL_CONTEXT parameter is passed, then SQL*Module uses the global runtime context of SQLLIB.
For example, here is a procedure that uses SQL_CONTEXT:
PROCEDURE seldept (:ctx SQL_CONTEXT, :dno INTEGER, SQLCODE); SELECT deptno INTO :dno FROM emp WHERE dname = 'SALES';
In this example, the select statement will use the runtime context pointed to by the variable ctx. ctx must have been previously allocated in a CONTEXT ALLOCATE statement. Note that you never reference the SQL_CONTEXT variable directly. It appears only in the code that SQL*Module generates.
This command allocates storage in memory for a SQLLIB runtime context that is used to support multi-tasking. An example is:
CONTEXT ALLOCATE :ctxvar;
ctxvar is of type SQL_CONTEXT. If sufficient storage is available, ctxvar will contain the address of the runtime context. Otherwise, ctxvar will be zero and an error will be returned in the provided status variables.
CONTEXT FREE frees all storage associated with the SQLLIB runtime context. It does not disconnect any active connection. Prior to deallocating the runtime context, you must execute the DISCONNECT command for each active connection.
The CONTEXT FREE statement always succeeds and ctxvar is zero after execution. If the context is ctxvar, then, an example is:
CONTEXT FREE :ctxvar;
Here is part of a module, adademo.mad:
PROCEDURE enable_threads (SQLCODE); ENABLE THREADS; PROCEDURE allocate_context (:ctx SQL_CONTEXT, SQLCODE); CONTEXT ALLOCATE :ctx; PROCEDURE free_context (:ctx SQL_CONTEXT, SQLCODE); CONTEXT FREE :ctx; PROCEDURE disconn_db (:ctx SQL_CONTEXT, :dbname VARCHAR2(14), SQLCODE); DISCONNECT :dbname;
these procedures are called as follows:
with ADADEMO; -- Declare contexts CTX1, CTX2 ... ADADEMO.ENABLE_THREADS(SQLCODE): ADADEMO.ALLOCATE_CONTEXT(CTX1, SQLCODE); ADADEMO.ALLOCATE_CONTEXT(CTX2, SQLCODE); -- Spawn tasks that process data: ...
An example of explicitly disconnecting and freeing contexts is:
-- After processing data: ... ADADEMO.DISCONN_DB(CTX1, DBNAM1); ADADEMO.DISCONN_DB(CTX2, DBNAM2); ADADEMO.FREE_CONTEXT(CTX1); ADADEMO.FREE_CONTEXT(CTX2);
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|