Programmer's Guide to the Pro*Ada Precompiler Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

An Overview of Embedded SQL


This chapter provides an overview of embedded SQL concepts and discusses the major steps in developing a Pro*Ada application. The chapter ends with a sample Pro*Ada program that illustrates key concepts. The following topics are included in this chapter:


Embedded SQL

Embedded SQL involves the placement of SQL language constructs in procedural language code. Using Pro*Ada, you embed SQL statements directly in your Ada program, then precompile the source. Precompilation translates the embedded SQL into calls to Pro*Ada runtime library procedures that handle the interaction between your program and the Oracle Server. After precompilation, you simply compile the resulting source files using your standard, supported Ada compiler, then build the application in the normal way. Pro*Ada supplies all the necessary library procedures. You must incorporate these into your normal Ada environment library.

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.

Datatypes

The Pro*Ada Precompiler performs conversions between internal Oracle datatypes and Ada host variable datatypes. For example, salaries in the EMP table are stored in Oracle NUMBER format. However, the EMPLOYEE_SALARY host variable does not have to be an Ada numeric scalar. If it were a string of appropriate length, Oracle would convert the numeric salary value to an ASCII string.

SQL Commands

Only embedded SQL commands, not SQL*Plus commands, are valid in a Pro*Ada program. SQL*Plus includes extra commands for report formatting, describing tables, and setting environment variables. You cannot use these in embedded SQL.

Embedded SQL Syntax

The only special requirement for building SQL statements into your host program is that you begin them with the keywords EXEC SQL and end them with a semicolon. The precompiler translates all EXEC SQL statements into calls to Pro*Ada library procedures.

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.

Executable Versus Declarative SQL Statements

There are two types of embedded SQL statements: executable and declarative. Executable statements result in calls to Oracle and return codes and data from Oracle. You use them to connect to Oracle, to define, query, manipulate, and control access to Oracle data, and to process transactions.

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.

Kinds of Executable Statements

Executable embedded SQL statements include:

DDL statements define and remove objects in the database, such as tables and views. DDL statements also control access to data. DML statements INSERT, DELETE, and UPDATE data. Queries SELECT data into scalar variables or arrays in your program.

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.

Declarative Statements
DECLARE Optional, to declare host variables and cursors
WHENEVER For error handling
Executable Statements
Data Definition Language
ALTER DROP To define or alter schemas
CREATE RENAME
CONNECT LOCK TABLE To control access
GRANT REVOKE
AUDIT NOAUDIT Other Data Definition Language commands
COMMENT ANALYZE
RENAME REVOKE
TRUNCATE
Data Manipulation and Query
DELETE INSERT To manipulate
UPDATE
CLOSE FETCH To retrieve data from tables
OPEN SELECT
COMMIT ROLLBACK To process transactions
SAVEPOINT
SET TRANSACTION
EXPLAIN PLAN Other Data Manipulation
LOCK TABLE Language
Other Embedded SQL Commands
DESCRIBE EXECUTE For use with dynamic SQL
PREPARE
Table 2 - 1. SQL Statements Grouped by Type

Static and Dynamic SQL Statements

Most application programs process static SQL statements and fixed transactions. In this case, you know the makeup of each SQL statement and transaction before runtime. You know which SQL commands will be issued, which database tables might be changed, which columns will be updated, and so on.

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."

Embedded PL/SQL Blocks

PL/SQL provides procedural extensions to SQL. The Pro*Ada Precompiler treats a PL/SQL block like a single embedded SQL statement. You can place a PL/SQL block anywhere in your application program that you can place a SQL statement. To embed PL/SQL in your Pro*Ada program, you simply declare the variables to be shared with PL/SQL, and bracket the PL/SQL block with the keywords EXEC SQL EXECUTE and END-EXEC.

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.

Host and Indicator Variables

Host variables are the key to communication between Oracle and your program. A host variable is a scalar or array variable declared in the normal way and shared with Oracle, meaning that both your program and SQL statements can reference its value.

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" [*].

Arrays

The Pro*Ada Precompiler lets you define array host variables called host arrays, and operate on them with a single SQL statement. Using the array SELECT, FETCH, DELETE, INSERT, and UPDATE statements, you can query and manipulate large volumes of data with ease.

Private Areas, Cursors, and Active Sets

To process a SQL statement, Oracle opens a work area called a private SQL area, which stores information needed to execute the SQL statement. An Oracle object called a cursor lets you name a private SOL area, access its stored information, and, to some extent, control its processing.

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.

Transactions

A transaction is a series of logically related SQL statements, such as two UPDATEs that credit one bank account and debit another. Oracle treats a transaction as a unit so all changes brought about by the statements are made permanent or undone at the same 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.

Errors and Warnings

When you execute an embedded SQL statement, it either succeeds or fails, and might result in an error or warning. You need a way to handle these results. The Pro*Ada Precompiler provides several error handling mechanisms:

The SQLCA is a data structure, defined in the Operating System Dependent (OSD) package supplied with Pro*Ada. It defines program variables used by Oracle to pass runtime status information to the program. With the SQLCA, you can take different actions based on feedback from Oracle about work just attempted. For example, you can check to see if a DELETE statement succeeded, and if so, how many rows were deleted.

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.


The Pro*Ada Development Process

The steps in the Pro*Ada application development process are as follows:

When you design your Pro*Ada application, you should be aware of several restrictions on the use of compilation units. These restrictions are described [*]. For more information about compiling and linking a Pro*Ada program, see Chapter 11, "Running the Pro*Ada Precompiler."

Additional Information: Consult your platform-specific Oracle documentation for specific instructions about compiling and linking a Pro*Ada program.


A Pro*Ada Demo Program

If you have not worked with a precompiler before, you should find the following example a good introduction to using Pro*Ada. This exercise is a simple Ada program that connects to Oracle and prompts the user to enter an employee's last name. If the employee name exists in the EMP table, the program selects and displays the department name and location. If the name does not exist, an error message is displayed with the appropriate error codes from the SQLSTATE and SQLCODE status variables. For more information about handling program errors, see Chapter 5, "Handling Runtime Errors."

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; 




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index