Programmer's Guide to the Pro*Ada Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Additional Information: Consult your platform-specific Oracle documentation for details on creating an Ada library for use with Pro*Ada.
Embedded SQL is an ANSI and ISO standard. It supports an extended method of database access above and beyond interactive SQL. The SQL statements that you can embed in an Ada program are a superset of the SQL statements that are supported by interactive SQL, and may have a slightly different syntax. For example, using an interactive SQL tool such as SQL*Plus, you can issue the statement
SELECT ename, sal
FROM emp
WHERE empno = &EMP_NUMBER;
SQL*Plus prompts you for the value of the substitution variable EMP_NUMBER, and once you have entered this, it displays the results. In a Pro*Ada program, the equivalent embedded SQL statement is:
EXEC SQL SELECT ename, sal
INTO :EMPLOYEE_NAME, :EMPLOYEE_SALARY
FROM emp
WHERE empno = :EMP_NUMBER;
In this case, the program must supply a valid employee number, storing it in the Ada host variable EMP_NUMBER, which must be declared and be in scope of the embedded SQL statement. When the statement is executed, the name and salary information that satisfy the query are placed into the Ada host variables EMPLOYEE_NAME and EMPLOYEE_SALARY. See the embedded SQL sample program for a complete example.
Most embedded SQL statements differ from their interactive counterparts only by the addition of a new clause, or the use of program variables. For a summary of embedded SQL syntax, see Appendix B, "Quick Reference to Embedded SQL." For more information about embedded SQL commands, refer to the Oracle7 Server SQL Reference.
Declarative statements; however, do not cause calls to the Oracle Ada library, and do not operate on Oracle data. You use them to declare Oracle objects and as precompiler pragmas (directives).
You can place declarative SQL statements anywhere in the program text. Executable SQL statements can be placed wherever executable Ada statements can be placed; that is, in a block between begin and end statements.
Table 2 - 1 groups the SQL commands into these categories. For a complete description of these commands, see Appendix B and the Oracle7 Server SQL Reference.
However, some applications need to accept and process any valid SQL statement at runtime. In this case, you might not know until runtime all the SQL commands, database tables, and columns involved.
Dynamic SQL is an advanced programming technique that lets your program accept or build SQL statements at run time. For more information on dynamic SQL, see Chapter 8, "Introduction to Dynamic SQL," and Chapter 9, "Implementing Dynamic SQL Method 4."
From embedded PL/SQL blocks, you can manipulate Oracle data flexibly and safely because PL/SQL supports all SQL data manipulation and transaction processing commands.
For more information about PL/SQL, see the PL/SQL User's Guide and Reference.
Your program uses input host variables to pass data to Oracle. Oracle uses output host variables to pass data and status information to your program. The program assigns values to input host variables. Oracle assigns values to output host variables.
Host variables can be used anywhere an expression can be used. In SQL statements, host variables must be prefixed with a colon (:) to set them apart from Oracle objects.
You can associate any host variable with an optional indicator variable. An indicator variable is a variable declared as an INDICATOR type (defined in the supplied Oracle package) that indicates the value or condition of its host variable. You use indicator variables to assign nulls to input host variables, and to detect nulls or truncated values in output host variables. A null represents a missing, unknown, or inapplicable value.
In SQL statements, an indicator variable must be prefixed with a colon and appended to its associated host variable. You may insert the optional SQL keyword INDICATOR between the host and indicator variables.
Note: If you use the MODE=ANSI option when you invoke the Pro*Ada Precompiler, you must use the INDICATOR keyword. For more information about the use of indicator variables for ANSI SQL standard programs, see "Declaring and Referencing Indicator Variables" .
For static SQL statements, there are two types of cursors: implicit and explicit. Oracle implicitly obtains a cursor for all data definition and data manipulation statements, including SELECT statements (queries) that return only one row. However, for queries that return more than one row, you must explicitly obtain a cursor to process beyond the first row, or you must use host arrays.
The set of rows returned is called the active set; its size depends on how many rows meet the query search condition. You use an explicit cursor to identify the row currently being processed, called the current row.
Imagine the set of rows being returned to a terminal screen. A screen cursor can point to the first row to be processed, then the next row, and so on. In the same way, an explicit cursor points to the current row in the active set. This allows your program to process the rows one at a time.
All the data manipulation statements executed since the last data definition, COMMIT, or ROLLBACK statement was executed make up the current transaction. To help ensure the consistency of your database, Oracle lets you define transactions using the COMMIT, ROLLBACK, and SAVEPOINT statements.
COMMIT makes permanent any changes made during the current transaction. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in the processing of a transaction; used with ROLLBACK, it undoes part of a transaction.
With the WHENEVER statement, you can specify actions to be taken automatically when Oracle detects an error or warning condition. These actions include raising an exception, branching, or continuing if possible.
Additional Information: Consult your platform-specific Oracle documentation for specific instructions about compiling and linking a Pro*Ada program.
This sample program is available on-line, as simple.pad, in the Pro*Ada demo directory.
-- Copyright (c) 1994 by Oracle Corporation -- SIMPLE : -- 1) Logon to the ORACLE database -- 2) Prompt user for a name that exists -- in the EMP table -- 3) Do a simple SQL join that finds the -- employee's department and location with text_io, integer_text_io, sql_standard; -- Note: the precompiler "with's" the other required ORACLE -- packages. procedure SIMPLE is use text_io, integer_text_io; -- declare host and program variables ORACLE_ID : constant string := "SCOTT/TIGER"; ENAME : string(1..20); ENAME_LEN : integer; DEPT_NAME : string(1..14); LOCATION : string(1..13); EXEC SQL BEGIN DECLARE SECTION; SQLCODE : sql_standard.sqlcode_type; -- for ANSI mode SQLSTATE : sql_standard.sqlstate_type; -- ANSI mode EXEC SQL END DECLARE SECTION; SQL_ERROR : exception; SQL_WARNING : exception;
-- Check to see if the last database -- operation returned any rows. -- (See Chapter 5 on error handling.) function EMPLOYEE_EXISTS return boolean is begin return (not(ORACLE.ERROR.IF_NOT_FOUND)); end EMPLOYEE_EXISTS; begin -- Direct the precompiler to insert "if" logic that -- checks the ORACLE return code and raises an exception -- if needed. EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; -- Check for warnings, such as data truncation, also. EXEC SQL WHENEVER SQLWARNING raise SQL_WARNING; -- Connect to ORACLE EXEC SQL CONNECT :ORACLE_ID; NEW_LINE; PUT_LINE("Connected to ORACLE as " & ORACLE_ID); NEW_LINE; PUT_LINE("*** ORACLE DEMO #1 ***"); NEW_LINE; loop PUT("Enter employee last name (CR to exit): "); GET_LINE(ENAME, ENAME_LEN); exit when ENAME_LEN = 0; -- SELECT statements that return one row can use a -- simple SELECT statement. Otherwise, a cursor must be -- declared for the SELECT, and a FETCH statement is used. EXEC SQL SELECT INITCAP(loc), INITCAP(dname) INTO :LOCATION, :DEPT_NAME FROM emp, dept WHERE dept.deptno = emp.deptno AND EMP.ENAME = upper(:ENAME(1..ENAME_LEN)); if EMPLOYEE_EXISTS then NEW_LINE; PUT("Employee "); PUT(ENAME(1..ENAME_LEN)); PUT(" works for department " & DEPT_NAME); PUT(" in " & LOCATION); else PUT_LINE("Sorry, no such employee (try ALLEN or JONES)"); NEW_LINE; -- The following lines illustrate the use of SQLSTATE and SQLCODE PUT("The SQLSTATE error code is: "); PUT_LINE(string(sqlstate)); PUT("The SQLCODE error code is: "); PUT(integer(sqlcode),1); end if; NEW_LINE; NEW_LINE; end loop; NEW_LINE; PUT_LINE("Good bye."); -- Disconnect from the database. EXEC SQL COMMIT RELEASE; exception -- Turn off error checking, since we do not want -- to raise an exception when logging out under -- any circumstance. EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL WHENEVER SQLWARNING CONTINUE; when SQL_ERROR => PUT_LINE(" ** ORACLE ERROR OCCURRED **"); NEW_LINE; PUT_LINE(ORACLE.ERROR.MESSAGE); EXEC SQL ROLLBACK RELEASE; when SQL_WARNING => PUT_LINE(" ** ORACLE WARNING OCCURRED **"); NEW_LINE; EXEC SQL ROLLBACK RELEASE; end SIMPLE;
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |