OS/2 Shareware BBS: 18 REXX/18-REXX.zip/orarex.zip/RXORA.INF

OS/2 Help File | 1995-07-11 | 34KB | 1,435 lines

ΓòÉΓòÉΓòÉ 1. Oracle-REXX Interface ΓòÉΓòÉΓòÉ

Oracle-REXX Interface for OS/2 Users's Guide

Beta Version

Please send comments regarding this document or the Oracle-REXX Interface to

    David Tom
    [e-mail]


ΓòÉΓòÉΓòÉ 2. Getting Started ΓòÉΓòÉΓòÉ

This chapter describes how to get started using the Oracle-REXX Interface.
Topics include:

    o Loading the Oracle-REXX Interface

    o Calling the Oracle-REXX Interface

    o Embedded SQL vs SQL

    o Using REXX variables in Embedded SQL

    o Connecting to Oracle

    o Disconnecting from Oracle


ΓòÉΓòÉΓòÉ 2.1. Loading the Oracle-REXX Interface ΓòÉΓòÉΓòÉ

Before you can use the Oracle-REXX Interface in a REXX program, you must first
define the SQLEXEC function using the built-in REXX function RxFuncAdd.

Use the following REXX statement to define the SQLEXEC function.

CALL RXFUNCADD 'SQLEXEC', 'RXORA', 'SQLEXEC'


ΓòÉΓòÉΓòÉ 2.2. Calling the Oracle-REXX Interface ΓòÉΓòÉΓòÉ

Calling the Oracle-REXX Interface uses the function SQLEXEC. The SQLEXEC
function takes a single argument which is a string containing an embedded SQL
statement.

CALL SQLEXEC "embedded_SQL"

The call to SQLEXEC returns a return code in the variable result. The variable
result will be set to zero if the embedded SQL statement was valid and passed
to the Oracle database for execution. Any error from the Oracle database will
be returned in the SQL Communications Area (SQLCA). The variable sqlca.sqlcode
will contain the Oracle return code and the variable sqlca.sqlerrmc will
contain any error message text.

The following sample code shows how to handle the return information from a
call to SQLEXEC.

CALL SQLEXEC "embedded_SQL"
IF result <> 0 THEN
  SAY "Invalid embedded SQL statement"
ELSE IF sqlca.sqlcode <> 0 THEN
  SAY sqlca.sqlerrmc


ΓòÉΓòÉΓòÉ 2.3. Embedded SQL vs SQL ΓòÉΓòÉΓòÉ

Embedded SQL statements are the commands used by the Oracle-REXX Interface to
control and process SQL statements. Embedded SQL statements allow the
Oracle-REXX Interface to:

    o Connect to an Oracle database
    o Parse and prepare SQL statements
    o Execute SQL statements
    o Fetch the results of a query
    o Commit or rollback transactions

 All of the commands passed to the Oracle-REXX Interface are embedded SQL
 statements. Embedded SQL statements should not be confused with standard SQL
 statements such as INSERT or SELECT. Embedded SQL statements may specify as
 part of the embedded SQL statement a standard SQL statement to be prepared or
 executed.


ΓòÉΓòÉΓòÉ 2.4. Using REXX variables in Embedded SQL ΓòÉΓòÉΓòÉ

Some embedded SQL statements allow REXX variables to be used to specify values
for the statement. For example, in the CONNECT statement, the user and password
values are specified by REXX variables. Generally, when a REXX variable is used
in an embedded SQL statement, the name of the REXX variable is preceded by a
colon.

CONNECT :user IDENTIFIED BY :password

In the above example, user is the name of a REXX variable which contains the
user name, and password is the name of a REXX variable which contains the
user's password.


ΓòÉΓòÉΓòÉ 2.5. Connecting to Oracle ΓòÉΓòÉΓòÉ

Use the embedded SQL statement CONNECT, to connect to Oracle. The format of the
CONNECT statement is:

CALL SQLEXEC "CONNECT :user IDENTIFIED BY :password USING :connect"

where

    user        is the name of a REXX variable containing the user name

    password    is the name of a REXX variable containing the user's password

    connect     is the name of a REXX variable containing the Oracle connect
                string

 If user contains both the user and password in the form user/password then the
 IDENTIFIED BY :password clause may be omitted. The USING :connect clause is
 optional and the default connect string will be used if not specified.


ΓòÉΓòÉΓòÉ 2.6. Disconnecting from Oracle ΓòÉΓòÉΓòÉ

To disconnect from Oracle, use the RELEASE option on the COMMIT or ROLLBACK
embedded SQL statements. The COMMIT statement will commit the current changes
before disconnecting from Oracle and the ROLLBACK statement will roll back the
current changes before disconnecting from Oracle.

CALL SQLEXEC "COMMIT RELEASE"
CALL SQLEXEC "ROLLBACK RELEASE"


ΓòÉΓòÉΓòÉ 3. Processing SQL Statements ΓòÉΓòÉΓòÉ

This chapter describes how to process SQL statements using the Oracle-REXX
Interface. The Oracle-REXX Interface processes SQL statements in a manor
similar to the other Oracle Precompilers, such as Pro*C and Pro*COBOL. There is
one major difference. Since REXX is an interpreted language, there is no
precompile step. The Oracle-REXX Interface uses dynamic SQL to process SQL
statements. Dynamic SQL allows SQL statements to be processed without requiring
a precompile step.

Dynamic SQL has four methods for processing SQL statements depending upon the
type of SQL statement. This chapter describes which of the four methods to use
and how to use each of the four methods. Topics include:

    o Statement Names and Cursor Names

    o Variable Placeholders in SQL Statements

    o Indicator Variables

    o Which Method to Use

    o Using Method 1

    o Using Method 2

    o Using Method 3

    o Using Method 4


ΓòÉΓòÉΓòÉ 3.1. Statement Names and Cursor Names ΓòÉΓòÉΓòÉ

Statement names are identifiers used by the Oracle-REXX Interface to refer to
the SQL statements that are being processed. Cursor names are identifiers used
by the Oracle-REXX Interface to refer to cursors. Cursors are used for query
statements and are used to identify the current row in the query results.

Statement and cursor names are identifiers used only by the Oracle-REXX
Interface. Statement and cursor names are not REXX variables. The DECLARE
statement is used to declare identifiers as statement or cursor names. Once
declared, statement and cursor names are defined for the REXX procedure in
which they were declared and may not be re-defined.


ΓòÉΓòÉΓòÉ 3.2. Variable Placeholders in SQL Statements ΓòÉΓòÉΓòÉ

Variable placeholders are used in dynamic SQL to represent values in a SQL
statement that will be replaced by REXX variables when the SQL statement is
executed. A variable placeholder is a name preceded by a colon. For example,
the following is a valid variable placeholder.

:empno_val

A SQL statement will contain a variable placeholder where ever the value of a
REXX variable is to be substituted. In the following example, the insert
statement will insert the values for three columns into the emp table. The
values to be inserted will be specified by REXX variables when the statement is
executed. Therefore, the statement uses three variable placeholders to
represent those values.

insert into emp(empno,ename,dept) values(:empno_val,:ename_val,:dept_val)

The names of the variable placeholders do not represent the names of REXX
variables. The variable placeholders merely represent places in the SQL
statement where the value of a REXX variable will be substituted. REXX
variables are substituted for the variable placeholders according to position.
The first REXX variable specified will be substituted for the first variable
placeholder and the second REXX variable will be substituted for the second
variable placeholder. Each variable placeholder represents a different place in
the SQL statement where a REXX variable may be substituted, even if the
variable placeholder names are the same.

For example, the following insert statement is equivalent to the previous
example. The insert statement contains three variable placeholders where the
values of three different REXX variables may be substituted even though the
variable placeholders all have the same name.

insert into emp(empno,ename,dept) values(:a,:a,:a)


ΓòÉΓòÉΓòÉ 3.3. Indicator Variables ΓòÉΓòÉΓòÉ

Indicator variables are used in an embedded SQL statement to indicate a null
value. An indicator variable is a REXX variable which is assigned a value to
indicate whether an associated variable contains a null value.

An Indicator variable is associated with a REXX variable by appending the name
of the indicator variable to the REXX variable. Remember that when REXX
variables are used in an embedded SQL statement, the REXX variable name is
preceded by a colon. In the following example, empno is a REXX variable
containing the value and empno_ind is a REXX variable used as the indicator
variable for empno.

:empno:empno_ind

When used as an input variable, the indicator variable is interpreted as:

    -1        The input is a null value.

    0 or >0   The input is the value of the associated REXX variable.

 When used as an output variable, the indicator variable is assigned:

    -1        The output is a null value.

    0         The output has been assigned to the associated REXX variable.

    >0        The output has been assigned to the associated REXX variable but
              was truncated during the assignment. The value is the original
              length of the output value.


ΓòÉΓòÉΓòÉ 3.4. Which Method to Use ΓòÉΓòÉΓòÉ

Dynamic SQL provides four methods for processing a SQL statement. Which method
to use depends upon the type of SQL statement being processed and whether or
not the SQL statement contains any variable placeholders. Method 1 is the
simplest method to use and method 4 is the most complex. Use the following to
determine which method is appropriate.

  1. Is the type of SQL statement unknown or the number of variable
     placeholders unknown?

    Yes:  Use method 4 for non-query statements, or
          Use method 4 for query statements

  2. Is the SQL statement a SELECT statement?

    Yes:  Use method 3

  3. Does the SQL statement have any variable placeholders?

    Yes:  Use method 2

    No:   Use method 1


ΓòÉΓòÉΓòÉ 3.5. Using Method 1 ΓòÉΓòÉΓòÉ

Dynamic SQL method 1 is used to execute SQL statements which are not query
statements and which do not use any variable placeholders. Method 1 is
generally used for SQL statements which are data control statements or data
definition statements such as GRANT and CREATE.

Dynamic SQL method 1 is the simplest way to execute an SQL statement and
requires only an EXECUTE IMMEDIATE statement.

Syntax

CALL SQLEXEC "EXECUTE IMMEDIATE SQL_stmt"

Description

The EXECUTE IMMEDIATE statement parses and executes the SQL statement. The SQL
statement can not contain any variable placeholders.

Example


ΓòÉΓòÉΓòÉ <hidden> Method 1 Example ΓòÉΓòÉΓòÉ

This is an example for using dynamic SQL method 1.

CALL SQLEXEC "EXECUTE IMMEDIATE create table temp (empno number(4), ename varchar2(40), dept number(4))"


ΓòÉΓòÉΓòÉ 3.6. Using Method 2 ΓòÉΓòÉΓòÉ

Dynamic SQL method 2 is used to execute SQL statements which are not query
statements and which contain a known number of variable placeholders. Method 2
is generally used for SQL statements which are data manipulation statements
such as INSERT and DELETE.

Dynamic SQL method 2 requires separate statement preparation and statement
execution steps. The preparation step parses the SQL statement and prepares it
for execution. The execution step executes the SQL statement with a given set
of values for the variable placeholders. Once the SQL statement has been
prepared, it may be executed several times using different values for the
variable placeholders.

Syntax

CALL SQLEXEC "DECLARE stmt_name STATEMENT"
CALL SQLEXEC "PREPARE stmt_name FROM SQL_stmt"
CALL SQLEXEC "EXECUTE stmt_name USING variable_list"

Description

The DECLARE statement declares the identifier as a statement name. The
statement name is used to identify the SQL statement in the PREPARE and EXECUTE
statements.

The PREPARE statement parses the SQL statement and prepares it for execution.

The EXECUTE statement executes the SQL statement substituting the values from
the variable list for the variable placeholders in the SQL statement.

Example


ΓòÉΓòÉΓòÉ <hidden> Method 2 Example ΓòÉΓòÉΓòÉ

This is an example for using dynamic SQL method 2.

CALL SQLEXEC "DECLARE stmt STATEMENT"
CALL SQLEXEC "PREPARE stmt FROM insert into temp(empno,ename,dept) values(:a,:b,:c)"

empno = 1021
ename = 'David'
dept  = 20
CALL SQLEXEC "EXECUTE stmt USING :empno, :ename, :dept"

empno = 1021
ename = 'Beverly'
dept  = 20
CALL SQLEXEC "EXECUTE stmt USING :empno, :ename, :dept"


ΓòÉΓòÉΓòÉ 3.7. Using Method 3 ΓòÉΓòÉΓòÉ

Dynamic SQL method 3 is used to execute SQL statements which are query
statements and which contain a known number of variable placeholders. Method 3
is used for SELECT statements.

Dynamic SQL method 3 requires separate statement preparation and statement
execution steps. Method 3 also requires the use of a cursor to return the rows
from the query results. The preparation step parses the SQL statement and
prepares it for execution. The execution step executes the SQL statement with a
given set of values for the variable placeholders and generates the query
results. The cursor is then used to return rows from the query results. Once
the SQL statement has been prepared, it may be executed several times using
different values for the variable placeholders and generating new query
results.

Syntax

CALL SQLEXEC "DECLARE stmt_name STATEMENT"
CALL SQLEXEC "DECLARE cursor_name CURSOR FOR stmt_name"

CALL SQLEXEC "PREPARE stmt_name FROM SQL_stmt"

CALL SQLEXEC "OPEN cursor_name USING variable_list"
CALL SQLEXEC "FETCH cursor_name INTO variable_list"
CALL SQLEXEC "CLOSE cursor_name"

Description

The DECLARE STATEMENT declares the identifier as a statement name. The
statement name is used to identify the SQL statement in the PREPARE statement
and in the declaration of the cursor.

The DECLARE CURSOR declares the identifier as a cursor name and associates the
cursor name with the statement name. The cursor name is used to identify the
cursor and SQL statement in the OPEN, FETCH and CLOSE statements.

The PREPARE statement parses the SQL statement and prepares it for execution.

The OPEN statement executes the SQL statement substituting the values from the
variable list for the variable placeholders in the SQL statement and positions
the cursor before the first row of the query results.

The FETCH statement moves the cursor to the next row of the query results and
returns the column values in the variable list. The FETCH statement is executed
repeatedly to return the rows of the query results.

The CLOSE statement terminates the query and releases all resources associated
with the cursor and statement. After the cursor for a statement is closed, the
statement must be prepared again before being executed.

Example


ΓòÉΓòÉΓòÉ <hidden> Method 3 Example ΓòÉΓòÉΓòÉ

This is an example for using dynamic SQL method 3.

CALL SQLEXEC "DECLARE stmt STATEMENT"
CALL SQLEXEC "DECLARE csr CURSOR FOR stmt"

CALL SQLEXEC "PREPARE stmt FROM select empno, ename from temp where dept=:a"

dept = 20
CALL SQLEXEC "OPEN csr USING :dept"

CALL SQLEXEC "FETCH csr INTO :empno, :ename"
DO WHILE (result = 0) & (sqlca.sqlcode = 0)
  SAY empno ename
  CALL SQLEXEC "FETCH csr INTO :empno, :ename"
END

CALL SQLEXEC "CLOSE csr"


ΓòÉΓòÉΓòÉ 3.8. Using Method 4 ΓòÉΓòÉΓòÉ

Dynamic SQL method 4 is used when the SQL statement and the number of variable
placeholders is not known in advance.

    o Using Method 4 for Non-Query Statements

    o Using Method 4 for Query Statements

    o Bind Descriptor Variables

    o Select Descriptor Variables


ΓòÉΓòÉΓòÉ 3.8.1. Using Method 4 for Non-Query Statements ΓòÉΓòÉΓòÉ

Dynamic SQL method 4 for non-query statements is used to execute SQL statements
which are not query statements and which have an unknown number of variable
placeholders. Method 4 is generally used when the SQL statement and the number
of variable placeholders is not known in advance.

Determining if a SQL statement is a query statement.

Syntax

CALL SQLEXEC "DECLARE stmt_name STATEMENT"

CALL SQLEXEC "PREPARE stmt_name FROM :stmt_var"
CALL SQLEXEC "DESCRIBE BIND VARIABLES FOR stmt_name INTO bind_desc"

CALL SQLEXEC "EXECUTE stmt_name USING DESCRIPTOR bind_desc"

Description

The DECLARE statement declares the identifier as a statement name. The
statement name is used to identify the SQL statement in the PREPARE, DESCRIBE
and EXECUTE statements.

The PREPARE statement parses the SQL statement and prepares it for execution.

The DESCRIBE BIND statement returns information about the variable placeholders
in the SQL statement into the bind descriptor variable. The bind descriptor
variable is a compound REXX variable which will contain information about each
of the variable placeholders. After doing the DESCRIBE BIND and before the
EXECUTE statement, a value should be assigned for each of the variable
placeholders in the bind descriptor variable.

The EXECUTE statement executes the SQL statement substituting the values from
the bind descriptor variable for the variable placeholders in the SQL
statement.

Example


ΓòÉΓòÉΓòÉ <hidden> Method 4 Non-Query Example ΓòÉΓòÉΓòÉ

This is an example for using dynamic SQL method 4 for non-query statements.

CALL SQLEXEC "DECLARE stmt STATEMENT"

SAY "Enter a non-query SQL statement"
PULL sql

DO WHILE sql <> ""
  CALL SQLEXEC "PREPARE stmt FROM :sql"
  CALL SQLEXEC "DESCRIBE BIND VARIABLES FOR stmt INTO bind_desc"

  DO i = 1 TO bind_desc.sqld
    SAY "Enter value for" bind_desc.i.sqlname
    PULL bind_desc.i.sqldata
    bind_desc.i.sqltype = 5
    bind_desc.i.sqllen  = 0
    bind_desc.i.sqlind  = 0
  END

  CALL SQLEXEC "EXECUTE stmt USING DESCRIPTOR bind_desc"

  SAY "Enter a non-query SQL statement"
  PULL sql
END


ΓòÉΓòÉΓòÉ 3.8.2. Using Method 4 for Query Statements ΓòÉΓòÉΓòÉ

Dynamic SQL method 4 for query statements is used to execute SQL statements
which are query statements and which have an unknown number of variable
placeholders. Method 4 is generally used when the SQL statement and the number
of variable placeholders is not known in advance.

Determining if a SQL statement is a query statement.

Syntax

CALL SQLEXEC "DECLARE stmt_name STATEMENT"
CALL SQLEXEC "DECLARE cursor_name CURSOR FOR stmt_name"

CALL SQLEXEC "PREPARE stmt_name FROM :stmt_var"
CALL SQLEXEC "DESCRIBE BIND VARIABLES FOR stmt_name INTO bind_desc"
CALL SQLEXEC "DESCRIBE SELECT LIST FOR stmt_name INTO select_desc"

CALL SQLEXEC "OPEN cursor_name USING DESCRIPTOR bind_desc"
CALL SQLEXEC "FETCH cursor_name USING DESCRIPTOR select_desc"
CALL SQLEXEC "CLOSE cursor_name"

Description

The DECLARE STATEMENT declares the identifier as a statement name. The
statement name is used to identify the SQL statement in the PREPARE statement,
DESCRIBE statement and in the declaration of the cursor.

The DECLARE CURSOR declares the identifier as a cursor name and associates the
cursor name with the statement name. The cursor name is used to identify the
cursor and SQL statement in the OPEN, FETCH and CLOSE statements.

The PREPARE statement parses the SQL statement and prepares it for execution.

The DESCRIBE BIND statement returns information about the variable placeholders
in the SQL statement into the bind descriptor variable. The bind descriptor
variable is a compound REXX variable which will contain information about each
of the variable placeholders. After doing the DESCRIBE BIND and before the OPEN
statement, a value should be assigned for each of the variable placeholders in
the bind descriptor variable.

The DESCRIBE SELECT statement returns information about the columns being
returned by the query into the select descriptor variable. The select
descriptor variable is a compound REXX variable which will contain information
about each of the columns being returned.

The OPEN statement executes the SQL statement substituting the values from the
bind descriptor variable for the variable placeholders in the SQL statement and
positions the cursor before the first row of the query results.

The FETCH statement moves the cursor to the next row of the query results and
returns the column values in the select descriptor variable. The FETCH
statement is executed repeatedly to return the rows of the query results.

The CLOSE statement terminates the query and releases all resources associated
with the cursor and statement. After the cursor for a statement is closed, the
statement must be prepared again before being executed.

Example


ΓòÉΓòÉΓòÉ <hidden> Method 4 Query Example ΓòÉΓòÉΓòÉ

This is an example for using dynamic SQL method 4 for query statements.

CALL SQLEXEC "DECLARE stmt STATEMENT"
CALL SQLEXEC "DECLARE csr CURSOR FOR stmt"

SAY "Enter a query SQL statement"
PULL sql

DO WHILE sql <> ""
  CALL SQLEXEC "PREPARE stmt FROM :sql"
  CALL SQLEXEC "DESCRIBE BIND VARIABLES FOR stmt INTO bind_desc"
  CALL SQLEXEC "DESCRIBE SELECT LIST FOR stmt INTO sel_desc"

  DO i = 1 TO bind_desc.sqld
    SAY "Enter value for" bind_desc.i.sqlname
    PULL bind_desc.i.sqldata
    bind_desc.i.sqltype = 5
    bind_desc.i.sqllen  = 0
    bind_desc.i.sqlind  = 0
  END

  DO i = 1 TO sel_desc.sqld
    sel_desc.i.sqltype = 5
    sel_desc.i.sqllen  = 0
  END

  CALL SQLEXEC "OPEN csr USING DESCRIPTOR bind_desc"

  CALL SQLEXEC "FETCH csr USING DESCRIPTOR sel_desc"
  DO WHILE (result = 0) & (sqlca.sqlcode = 0)
    DO i = 1 TO sel_desc.sqld
      SAY sel_desc.i.sqlname "is" sel_desc.i.sqldata
    END
    CALL SQLEXEC "FETCH csr USING DESCRIPTOR sel_desc"
  END

  CALL SQLEXEC "CLOSE csr"

  SAY "Enter a query SQL statement"
  PULL sql
END


ΓòÉΓòÉΓòÉ 3.8.3. Bind Descriptor Variables ΓòÉΓòÉΓòÉ

Bind descriptor variables are used in dynamic SQL method 4 to get information
about the variable placeholders in a SQL statement and to provide values to be
bound to the variable placeholders. A bind descriptor variable is a compound
REXX variable representing a SQLDA structure.

Used in DESCRIBE BIND

When used in a DESCRIBE BIND statement to obtain information about the variable
placeholders in a SQL statement, the following fields of the bind descriptor
variable are set.

    xx.SQLD           Set to the number of variable placeholders. Each of the
                      variable placeholders is described by xx.1 to xx.n.

    xx.n.SQLNAME      Set to the name of the variable placeholder.

    xx.n.SQLINAME     Set to the name of the variable placeholder indicator
                      variable.

 Used in EXECUTE or OPEN

 When used in an EXECUTE or OPEN statement to bind values to the variable
 placeholders in a SQL statement, the following fields of the bind descriptor
 variable are used.

    xx.SQLD           The number of values to bind to the variable
                      placeholders. Each of the values are specified in xx.1 to
                      xx.n.

    xx.n.SQLTYPE      The type of the value.

    xx.n.SQLLEN       The length of the value.

    xx.n.SQLDATA      The value to be used.

    xx.n.SQLIND       The indicator value to be used.


ΓòÉΓòÉΓòÉ 3.8.4. Select Descriptor Variables ΓòÉΓòÉΓòÉ

Select descriptor variables are used in dynamic SQL method 4 to get information
about the columns returned by a query and to receive the value of the columns
when a row is fetched. A select descriptor variable is a compound REXX variable
representing a SQLDA structure.

Used in DESCRIBE SELECT

When used in a DESCRIBE SELECT statement to obtain information about the
columns returned by a query, the following fields of the select descriptor
variable are set.

    xx.SQLD           Set to the number of columns. Each of the columns is
                      described by in xx.1 to xx.n.

    xx.n.SQLNAME      Set to the name of the column.

    xx.n.SQLTYPE      Set to the type of the column.

    xx.n.SQLLEN       Set to the length of the column.

       xx.n.SQLLEN.PRECISION
       xx.n.SQLLEN.SCALE

 Used in FETCH

 When used in a FETCH statement to receive the values when a row is fetched,
 the following fields of the select descriptor variable are used or set.

    xx.SQLD           The number of column to be returned. Each column is
                      returned in xx.1 to xx.n.

    xx.n.SQLTYPE

    xx.n.SQLLEN

    xx.n.SQLDATA      Set the the value of the column.

    xx.n.SQLIND       Set to the indicator value for the column.


ΓòÉΓòÉΓòÉ 4. Advanced Features ΓòÉΓòÉΓòÉ

This chapter describes using the advanced features of the Oracle-REXX
Interface. The advanced features are:

    o Using Arrays

    o Using Multiple Database Sessions

    o PL/SQL

    o CURRENT OF Clause


ΓòÉΓòÉΓòÉ 4.1. Using Arrays ΓòÉΓòÉΓòÉ

Arrays can be used to simplify the processing of a SQL statement by the
Oracle-REXX Interface. For example, an array can be used to simplify retrieving
query results by allowing multiple rows to be retrieved in a single FETCH
statement.  Arrays can also be used to simplify the processing of INSERT,
UPDATE and DELETE statements by allowing these statements to process an array
of data in a single statement.

Arrays in REXX are represented as compound variables. A compound variable
consist of a stem followed by a tail delimited by a period. For example, an
array of employee numbers would be represented by the compound variables

  empno.1
  empno.2
  empno.3
  ...
  empno.n


ΓòÉΓòÉΓòÉ 4.1.1. Fetching With Arrays ΓòÉΓòÉΓòÉ

Arrays can be used to retrieve multiple rows in a single FETCH statement. A FOR
clause is required on the FETCH statement to retrieve multiple rows. The syntax
is as follows:

CALL SQLEXEC "FOR :count FETCH cursor_name INTO variable_list"

or

CALL SQLEXEC "FOR :count FETCH cursor_name USING DESCRIPTOR select_desc"

The FOR clause specifies the name of a REXX variable which contains the number
of rows to retrieve. When the FOR clause is specified, the values are returned
as arrays. If a variable list is used, each variable in the list will be a
compound variable representing an array from 1 to count. If a select descriptor
is used, each SQLDATA component of the select descriptor will be a compound
variable representing an array from 1 to count.

Fewer than count rows may be returned if there is an error or there is no more
data to return. The cumulative number of rows that have been returned is set in
the SQL Communications Area (SQLCA). The variable sqlca.sqlerrd.3 will contain
the cumulative number of rows returned. To find the number of rows returned by
the current FETCH statement, it is necessary to subtract the number of rows
previously returned from sqlca.sqlerrd.3.


ΓòÉΓòÉΓòÉ 4.1.2. Executing With Arrays ΓòÉΓòÉΓòÉ

Arrays can be used to execute a SQL statement several times with different data
values. When using arrays, the SQL statement is executed once for each row of
the array. A FOR clause is required on the EXECUTE statement to do an array
execute. The syntax is as follows:

CALL SQLEXEC "FOR :count EXECUTE stmt_name USING variable_list"

or

CALL SQLEXEC "FOR :count EXECUTE stmt_name USING DESCRIPTOR bind_desc"

The FOR clause specifies the name of a REXX variable which contains the number
of times to execute the SQL statement. When the FOR clause is specified, the
values to be substituted for the variable placeholders must be arrays. If a
variable list is used, each variable in the list must be a compound variable
representing an array from 1 to count, If a bind descriptor is used, each
SQLDATA component of the bind descriptor must be a compound variable
representing an array from 1 to count.

The SQL statement may be executed fewer than count times if an error occurs.
The number of times the statement has been executed is set in the SQL
Communications Area (SQLCA). The variable sqlca.sqlerrd.3 will contain the
number of times the SQL statement was executed.


ΓòÉΓòÉΓòÉ 4.2. Using Multiple Database Sessions ΓòÉΓòÉΓòÉ

Multiple database sessions allows you to simultaneously connect to several
different Oracle databases or to use multiple connections to a single Oracle
database.

Each connection to an Oracle database is identified by a database session name.
Embedded SQL statements can be executed at different databases or using
different connections by using the AT clause to specify a database session for
the embedded SQL statement.


ΓòÉΓòÉΓòÉ 4.2.1. AT Clause ΓòÉΓòÉΓòÉ

The AT clause is used to specify the name of a database session. The syntax of
the AT clause is:

AT db_name

or

AT :db_var

If db_name is used, db_name must be a name that has been declared as a database
session by a DECLARE DATABASE statement.

If db_var is used, db_var is the name of a REXX variable which contains the
name of the database session. In this case, the name of the database session
can be any valid name and does not need to be declared by a DECLARE DATABASE
statement.


ΓòÉΓòÉΓòÉ 4.2.2. Creating a Named Database Session ΓòÉΓòÉΓòÉ

To create a named database session, use the AT clause on the CONNECT statement.
Once connected, the database session identifier can be used to refer to this
database connection.

CALL SQLEXEC "DECLARE session1 DATABASE"
CALL SQLEXEC "CONNECT :user IDENTIFIED BY :password AT session1 USING :connect"

or

session = "session2"
CALL SQLEXEC "CONNECT :user IDENTIFIED BY :password AT :session USING :connect"


ΓòÉΓòÉΓòÉ 4.2.3. Executing At a Named Database Session ΓòÉΓòÉΓòÉ

To execute an embedded SQL statement at a named database session, use the AT
clause to identify the database session where the statement is to be executed.

Execute Immediate

CALL SQLEXEC "AT db_name EXECUTE IMMEDIATE SQL_stmt"

Preparing and Executing

CALL SQLEXEC "AT db_name DECLARE stmt_name STATEMENT"
CALL SQLEXEC "PREPARE stmt_name FROM SQL_stmt"
CALL SQLEXEC "EXECUTE stmt_name USING variable_list"

Preparing, Executing and Fetching

CALL SQLEXEC "AT db_name DECLARE stmt_name STATEMENT"
CALL SQLEXEC "DECLARE cursor_name CURSOR FOR stmt_name"

CALL SQLEXEC "PREPARE stmt_name FROM SQL_stmt"

CALL SQLEXEC "OPEN cursor_name USING variable_list"
CALL SQLEXEC "FETCH cursor_name INTO variable_list"
CALL SQLEXEC "CLOSE cursor_name"


ΓòÉΓòÉΓòÉ 4.3. PL/SQL ΓòÉΓòÉΓòÉ

Use of PL/SQL is not implemented at this time.


ΓòÉΓòÉΓòÉ 4.4. CURRENT OF Clause ΓòÉΓòÉΓòÉ

The CURRENT OF clause is not implemented at this time.


ΓòÉΓòÉΓòÉ 5. Reference ΓòÉΓòÉΓòÉ

This chapter contains reference information for the Oracle-REXX Interface.


ΓòÉΓòÉΓòÉ 5.1. Data Structures ΓòÉΓòÉΓòÉ

This section describes the data structures used by the Oracle-REXX Interface.


ΓòÉΓòÉΓòÉ 5.1.1. SQLCA - SQL Communications Area ΓòÉΓòÉΓòÉ

SQLCA.SQLCODE      Oracle return code
                     0   Oracle executed the statement without
                         any errors
                     >0  Oracle detected an exception when the
                         statement was executed, such as no
                         data found
                     <0  An error occurred while executing the
                         statement.

SQLCA.SQLERRMC     Error message text
SQLCA.SQLERRML     Error message length

SQLCA.SQLERRD.1    Reserved
SQLCA.SQLERRD.2    Reserved
SQLCA.SQLERRD.3    Number of rows processed
SQLCA.SQLERRD.4    Reserved
SQLCA.SQLERRD.5    Offset of parse error
SQLCA.SQLERRD.6    Reserved

The following are warning flags which are set to 'W' to indicate
a warning.

SQLCA.SQLWARN.1    Set if any other flag is set
SQLCA.SQLWARN.2    Set if a column value was truncated
SQLCA.SQLWARN.3    Not used
SQLCA.SQLWARN.4    Set if the number of columns exceeds the
                   number of host variables specified
SQLCA.SQLWARN.5    Set for UPDATE or DELETE without a WHERE clause
SQLCA.SQLWARN.6    Set for a PL/SQL compilation error
SQLCA.SQLWARN.7    Not used
SQLCA.SQLWARN.8    Not used

SQLCA.SQLERRP      Reserved
SQLCA.SQLEXT       Reserved


ΓòÉΓòÉΓòÉ 5.1.2. SQLDA - SQL Descriptor ΓòÉΓòÉΓòÉ

xx.SQLD                Number of values in descriptor
xx.n.SQLNAME           Name
xx.n.SQLINAME          Indicator name
xx.n.SQLTYPE           Data type
xx.n.SQLLEN            Length
xx.n.SQLLEN.PRECISION  Precision (if numeric data type)
xx.n.SQLLEN.SCALE      Scale (if numeric data type)
xx.n.SQLDATA           Data value
xx.n.SQLIND            Indicator value


ΓòÉΓòÉΓòÉ 5.2. Embedded SQL Statements ΓòÉΓòÉΓòÉ

This section provides a reference for the embedded SQL statements supported by
the Oracle-REXX Interface.


ΓòÉΓòÉΓòÉ 5.2.1. Variable List ΓòÉΓòÉΓòÉ

A variable list is used to provide a list of REXX variables and optionally the
associated REXX indicator variables.

Syntax

   ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇ , ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
                                                  Γöé
ΓöÇΓöÇΓöÇ :variable ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓö┤ΓöÇ
                 Γöé                               Γöé
                 Γö£ΓöÇ :indicator_var ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
                 Γöé                               Γöé
                 ΓööΓöÇ INDICATOR ΓöÇΓöÇ :indicator_var ΓöÇΓöÿ

Usage Notes

The variable and indicator variables are preceded by a colon which is not part
of the REXX variable name.

Variables in the list are separated by commas. Indicator variables can be
concatenated to their associated variable, separated by a blank, or the keyword
INDICATOR.


ΓòÉΓòÉΓòÉ 5.2.2. CLOSE ΓòÉΓòÉΓòÉ

The CLOSE statement closes a cursor and releases the resources associated with
the cursor.

Syntax

ΓöÇΓöÇ CLOSE ΓöÇΓöÇ cursor_name ΓöÇΓöÇ

Usage Notes

Closing a cursor which is not open or which has not been declared generates an
error.

A cursor may be re-opened without closing the cursor. Once a cursor has been
closed, the associated statement must be prepared again before the cursor can
be re-opened.


ΓòÉΓòÉΓòÉ 5.2.3. COMMIT ΓòÉΓòÉΓòÉ

The COMMIT statement ends the current transaction and makes changes to the
database permanent.

Syntax

ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ COMMIT ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ
   Γöé                  Γöé          Γöé        Γöé Γöé           Γöé
   ΓööΓöÇ AT ΓöÇΓö¼ΓöÇ db_name ΓöÇΓöñ          ΓööΓöÇ WORK ΓöÇΓöÿ ΓööΓöÇ RELEASE ΓöÇΓöÿ
          Γöé           Γöé
          ΓööΓöÇ :db_var ΓöÇΓöÿ

Usage Notes

The RELEASE keyword is used to disconnect from Oracle after the transaction has
been committed.

The AT clause specifies the name of the database session for the COMMIT. If the
AT clause is not specified, the default database session is used.


ΓòÉΓòÉΓòÉ 5.2.4. CONNECT ΓòÉΓòÉΓòÉ

The CONNECT statement logs on to an Oracle database.

Syntax

ΓöÇΓöÇΓöÇ CONNECT ΓöÇΓö¼ΓöÇ :user_password ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ
               Γöé                                       Γöé
               ΓööΓöÇ :user ΓöÇΓöÇ IDENTIFIED BY ΓöÇΓöÇ :password ΓöÇΓöÿ

 ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ
   Γöé                  Γöé Γöé                            Γöé
   ΓööΓöÇ AT ΓöÇΓö¼ΓöÇ db_name ΓöÇΓöñ ΓööΓöÇ USING ΓöÇΓöÇ :connect_string ΓöÇΓöÿ
          Γöé           Γöé
          ΓööΓöÇ :db_var ΓöÇΓöÿ

Usage Notes

The user and password can be specified by a single REXX variable which contains
username/password or by a REXX variable containing the username and a separate
REXX variable containing the password.

The AT clause specifies the name of the database session and is used to create
multiple connections.  If the AT clause is not specified, the default database
session is used.

The USING clause specifies the Oracle connect string which identifies the
database to connect to. If not specified, the default database is used.

If the database session is already connected, the CONNECT statement will
disconnect the session before making the new connection.


ΓòÉΓòÉΓòÉ 5.2.5. DECLARE CURSOR ΓòÉΓòÉΓòÉ

The DECLARE CURSOR statement declares a cursor and associates it with a
specific query.

Syntax

ΓöÇΓöÇ DECLARE ΓöÇΓöÇ cursor_name ΓöÇΓöÇ CURSOR FOR ΓöÇΓöÇ stmt_name ΓöÇΓöÇ

Usage Notes

The cursor name and statement name are identifiers used by the Oracle-REXX
Interface and are not REXX variables.

The cursor name must be declared before it is used. The scope of the cursor
declaration is the REXX procedure in which the cursor is declared. Therefore,
the cursor name must be unique within the REXX procedure in which it is
declared and can not be referenced outside of the REXX procedure in which it is
declared.

The statement name must have been previously declared as a statement either
explicitly by a DECLARE STATEMENT statement or implicitly by a PREPARE
statement.


ΓòÉΓòÉΓòÉ 5.2.6. DECLARE DATABASE ΓòÉΓòÉΓòÉ

The DECLARE DATABASE statement declares the name of a non-default database
session for use in the AT clause.

Syntax

ΓöÇΓöÇ DECLARE ΓöÇΓöÇ db_name ΓöÇΓöÇ DATABASE ΓöÇΓöÇ

Usage Notes

The database name is an identifier used by the Oracle-REXX Interface and is not
a REXX variable.

The database name must be declared before it is used. The scope of the database
declaration is the REXX procedure in which the database is declared. Therefore,
the database name must be unique within the REXX procedure in which it is
declared.

Unlike statement names and cursor names, if the same database name is declared
in two different REXX procedures then the database name in both REXX procedures
will refer to the same database session. This allows doing a connection using a
database name in one REXX procedure and processing embedded SQL statement in
another REXX procedure using the same database name to refer to the same
session.


ΓòÉΓòÉΓòÉ 5.2.7. DECLARE STATEMENT ΓòÉΓòÉΓòÉ

The DECLARE STATEMENT statement declares the name of a dynamic SQL statement.

Syntax

ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ DECLARE ΓöÇΓöÇ stmt_name ΓöÇΓöÇ STATEMENT ΓöÇΓöÇ
   Γöé                  Γöé
   ΓööΓöÇ AT ΓöÇΓö¼ΓöÇ db_name ΓöÇΓöñ
          Γöé           Γöé
          ΓööΓöÇ :db_var ΓöÇΓöÿ

Usage Notes

The statement name is an identifier used by the Oracle-REXX Interface and is
not a REXX variable.

The statement name must be explicitly declared by a DECLARE STATEMENT statement
or implicitly declared by a PREPARE statement before it is used. The scope of
statement declaration is the REXX procedure in which the statement is declared.
Therefore, the statement name must be unique within the REXX procedure in which
it is declared and can not be referenced outside of the REXX procedure in which
it is declared.

The AT clause specifies the name of the database session to be used for the
statement. If the AT clause is not specified, the default database session is
used. If the statement is to be executed using a database session other than
the default session, the statement must be explicitly declared and the AT
clause must be specified on the DECLARE statement.


ΓòÉΓòÉΓòÉ 5.2.8. DESCRIBE BIND ΓòÉΓòÉΓòÉ

The DESCRIBE BIND statement returns information in the descriptor variable
about the variable placeholders in a prepared dynamic SQL statement.

Syntax

ΓöÇΓöÇ DESCRIBE BIND VARIABLES FOR ΓöÇΓöÇ stmt_name ΓöÇΓöÇ INTO ΓöÇΓöÇ descriptor ΓöÇΓöÇ

Usage Notes

The statement name is an identifier associated with a prepared SQL statement.
The statement name identifier is used by the Oracle-REXX Interface and is not a
REXX variable. The SQL statement must be prepared using the PREPARE statement
before it can be described with the DESCRIBE BIND statement.

The REXX variable descriptor is a compound variable in which information about
the variable placeholders in the SQL statement will be returned.


ΓòÉΓòÉΓòÉ 5.2.9. DESCRIBE SELECT ΓòÉΓòÉΓòÉ

The DESCRIBE SELECT statement returns information in the descriptor variable
about the columns in the select list in a prepared dynamic SQL statement.

Syntax

ΓöÇΓöÇ DESCRIBE ΓöÇΓö¼ΓöÇ SELECT LIST FOR ΓöÇΓö¼ΓöÇ stmt_name ΓöÇΓöÇ INTO ΓöÇΓöÇ descriptor ΓöÇΓöÇ
               Γöé                   Γöé
               ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ

Usage Notes

The statement name is an identifier associated with a prepared SQL statement.
The statement name identifier is used by the Oracle-REXX Interface and is not a
REXX variable. The SQL statement must be prepared using the PREPARE statement
before it can be described with the DESCRIBE SELECT statement.

The REXX variable descriptor is a compound variable in which information about
the columns in the select list of a SQL statement will be returned.


ΓòÉΓòÉΓòÉ 5.2.10. EXECUTE ΓòÉΓòÉΓòÉ

The EXECUTE statement executes a prepared dynamic SQL statement.

Syntax

ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ EXECUTE ΓöÇΓöÇ stmt_name ΓöÇΓöÇ
   Γöé                     Γöé
   ΓööΓöÇ FOR ΓöÇΓöÇ :count_var ΓöÇΓöÿ

 ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ
   Γöé                                        Γöé
   ΓööΓöÇ USING ΓöÇΓö¼ΓöÇ variable_list ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÿ
             Γöé                            Γöé
             ΓööΓöÇ DESCRIPTOR ΓöÇΓöÇ descriptor ΓöÇΓöÿ

Usage Notes

The statement name is an identifier associated with a prepared SQL statement.
The statement name identifier is used by the Oracle-REXX Interface and is not a
REXX variable. The SQL statement must be prepared using the PREPARE statement
before it can be executed with the EXECUTE statement.

The USING clause specifies either a list of REXX variables or the name of a
REXX descriptor variable. The variable list or descriptor variable contains the
values to be substituted for the variable placeholders in the SQL statement
when the SQL statement is executed. The number of values must match the number
of variable placeholders in the SQL statement.

The FOR clause is used for executing with arrays. When executing with arrays,
the variable list or descriptor variable contains arrays of values to be
substituted for the variable placeholders. The REXX variable count_var contains
the size of the arrays. The SQL statement is executed count_var times, each
time substituting the next row from the arrays for the variable placeholders.


ΓòÉΓòÉΓòÉ 5.2.11. EXECUTE IMMEDIATE ΓòÉΓòÉΓòÉ

The EXECUTE IMMEDIATE statement prepares and executes a dynamic SQL statement.

Syntax

ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ EXECUTE IMMEDIATE ΓöÇΓö¼ΓöÇ sql_stmt  ΓöÇΓö¼ΓöÇ
   Γöé                  Γöé                     Γöé             Γöé
   ΓööΓöÇ AT ΓöÇΓö¼ΓöÇ db_name ΓöÇΓöñ                     ΓööΓöÇ :stmt_var ΓöÇΓöÿ
          Γöé           Γöé
          ΓööΓöÇ :db_var ΓöÇΓöÿ

Usage Notes

The EXECUTE IMMEDIATE statement parses, prepares and executes a SQL statement.
If the SQL statement is to be execute several times then the PREPARE statement
should be used to prepare the statement after which it can be executed several
times without preparing the statement for each execution.

The AT clause specifies the name of the database session where the statement
will be executed. If the AT clause is not specified, the default database
session is used.

The SQL statement may be specified either by a literal string or by a REXX
variable which contains the SQL statement.


ΓòÉΓòÉΓòÉ 5.2.12. FETCH ΓòÉΓòÉΓòÉ

The FETCH statement advances the cursor to the next row of the query results
and retrieves the row.

Syntax

ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ FETCH ΓöÇΓöÇ csr_name ΓöÇΓöÇ
   Γöé                     Γöé
   ΓööΓöÇ FOR ΓöÇΓöÇ :count_var ΓöÇΓöÿ

 ΓöÇΓö¼ΓöÇ INTO ΓöÇΓöÇΓöÇ variable_list ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ
   Γöé                                  Γöé
   ΓööΓöÇ USING DESCRIPTOR ΓöÇΓöÇ descriptor ΓöÇΓöÿ

Usage Notes

The cursor name is an identifier previously declared as a cursor. The cursor
name identifier is used by the Oracle-REXX Interface and is not a REXX
variable.

The cursor must be opened using an OPEN statement before the results of a query
can be fetched. Each FETCH returns the next row of the query results.

The INTO clause specifies a list of REXX variable to receive the column values
from the query results. The USING DESCRIPTOR specifies the name of a REXX
descriptor variable to receive the column values from the query results.

The FOR clause is used for fetching with arrays. When fetching with arrays, the
variable list or descriptor variable will return arrays of values. The REXX
variable count_var contains the size of the arrays.


ΓòÉΓòÉΓòÉ 5.2.13. OPEN ΓòÉΓòÉΓòÉ

The OPEN statement executes the query associated with the cursor and positions
the cursor before the first row of the query results.

Syntax

ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ OPEN ΓöÇΓöÇ csr_name ΓöÇΓöÇ
   Γöé                     Γöé
   ΓööΓöÇ FOR ΓöÇΓöÇ :count_var ΓöÇΓöÿ

 ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ
   Γöé                                        Γöé
   ΓööΓöÇ USING ΓöÇΓö¼ΓöÇ variable_list ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÿ
             Γöé                            Γöé
             ΓööΓöÇ DESCRIPTOR ΓöÇΓöÇ descriptor ΓöÇΓöÿ

Usage Notes

The cursor name is an identifier previously declared as a cursor. The cursor
name identifier is used by the Oracle-REXX Interface and is not a REXX
variable.

The statement associated with the cursor must be prepared using a PREPARE
statement before the cursor can be opened. When the cursor is opened, the
statement is executed and the query results are generated. The cursor is
positioned before the first row of the query results.

The USING clause specifies either a list of REXX variables or the name of REXX
descriptor variable. The variable list or descriptor variable contains the
values to be substituted for the variable placeholders in the SQL statement
when the SQL statement is executed. The number of values must match the number
of variable placeholders in the SQL statement.

The FOR clause is used for executing with arrays. When executing with arrays,
the variable list or descriptor variable contains arrays of values to be
substituted for the variable placeholders. The REXX variable count_var contains
the size of the arrays. The SQL statement is executed count_var times, each
time substituting the next row from the arrays for the variable placeholders.


ΓòÉΓòÉΓòÉ 5.2.14. PREPARE ΓòÉΓòÉΓòÉ

The PREPARE statement parses and prepares a dynamic SQL statement.

Syntax

ΓöÇΓöÇ PREPARE ΓöÇΓöÇ stmt_name ΓöÇΓöÇ FROM ΓöÇΓö¼ΓöÇ sql_stmt  ΓöÇΓö¼ΓöÇ
                                   Γöé             Γöé
                                   ΓööΓöÇ :stmt_var ΓöÇΓöÿ

Usage Notes

The PREPARE statement parses and prepares a SQL statement and associates a
statement name with the SQL statement. The statement name is used to refer to
the SQL statement after it has been prepared. The statement name is an
identifier used by the Oracle-REXX Interface and is not a REXX variable.

The statement name may have been previously declared as a statement name by a
DECLARE STATEMENT statement. Otherwise the statement name must be an undeclared
identifier which will be implicitly declared as a statement name. If the
statement is to be executed using a database session other than the default
database session, the statement name must be explicitly declared using the
DECLARE STATEMENT statement and the AT clause must be specified.

The SQL statement may be specified either by a literal string or by a REXX
variable which contains the SQL statement.

After a SQL statement has been prepared, it may be executed using an EXECUTE
statement or an OPEN statement. The SQL statement may be executed several times
without re-preparing the statement for each execution.

SQL data definition statements, such as CREATE TABLE, are executed when they
are prepared. Generally data definition statements should be executed using
EXECUTE IMMEDIATE rather than PREPARE.


ΓòÉΓòÉΓòÉ 5.2.15. ROLLBACK ΓòÉΓòÉΓòÉ

The ROLLBACK statement ends the current transaction and undoes the changes to
the database.

Syntax

ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ ROLLBACK ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇ
   Γöé                  Γöé            Γöé        Γöé Γöé           Γöé
   ΓööΓöÇ AT ΓöÇΓö¼ΓöÇ db_name ΓöÇΓöñ            ΓööΓöÇ WORK ΓöÇΓöÿ ΓööΓöÇ RELEASE ΓöÇΓöÿ
          Γöé           Γöé
          ΓööΓöÇ :db_var ΓöÇΓöÿ

Usage Notes

The RELEASE keyword is used to disconnect from Oracle after the transaction has
been rolled back.

The AT clause specifies the name of the database session for the ROLLBACK. If
the AT clause is not specified, the default database session is used.


ΓòÉΓòÉΓòÉ 5.3. Return Codes ΓòÉΓòÉΓòÉ

SQLEXEC returns zero in the variable result if the embedded SQL statement was
valid and passed to the Oracle database for execution. If the Oracle-REXX
Interface detects an error a non-zero value will be returned in the variable
result.

Return codes

    o 100-199 Syntax errors

    o 200-299 Semantic errors

    o 900-999 Fatal errors


ΓòÉΓòÉΓòÉ 5.3.1. 100-199 Syntax Errors ΓòÉΓòÉΓòÉ

101  ERR_KEYWORD_BY
102  ERR_KEYWORD_FOR
103  ERR_KEYWORD_VARIABLES
104  ERR_KEYWORD_LIST
105  ERR_KEYWORD_INTO
106  ERR_KEYWORD_FROM
107  ERR_KEYWORD_DESCRIPTOR
108  ERR_KEYWORD_INTO_USING
109  ERR_DESCNAME
110  ERR_CURSOR_NAME
111  ERR_STMT_NAME
112  ERR_DECLARE_NAME
113  ERR_VARLIST
114  ERR_LITERAL
115  ERR_SQL_STMT
116  ERR_DECLARE_TYPE
117  ERR_DESCRIPTOR_VARLIST
118  ERR_IMMED_STMTNAME
119  ERR_ATCLAUSE_DBNAME
120  ERR_FORCLAUSE_VAR
121  ERR_CONNECT_USER
122  ERR_CONNECT_PWD
123  ERR_CONNECT_USING
124  ERR_REXX_NAME_TOO_LONG
125  ERR_MISSING_ENDING_QUOTE
126  ERR_STMT_KEYWORD
127  ERR_END_OF_STMT


ΓòÉΓòÉΓòÉ 5.3.2. 200-299 Semantic Errors ΓòÉΓòÉΓòÉ

201  ERR_AT_NOT_ALLOWED
202  ERR_FOR_NOT_ALLOWED
203  ERR_FOR_VALUE_INVALID
204  ERR_SQLD_INVALID
205  ERR_COMPOUND_NAME_TOO_LONG
206  ERR_NAME_NOT_DECLARED
207  ERR_DBNAME_NOT_DECLARED
208  ERR_STMT_NOT_DECLARED
209  ERR_NAME_ALREADY_DECLARED
210  ERR_NAME_NOT_CURSOR
211  ERR_NAME_NOT_STMT
212  ERR_NAME_NOT_DB
213  ERR_STMT_NOT_PREPARED
214  ERR_CURSOR_NOT_OPENED
215  ERR_DBNAME_INVALID
216  ERR_SELECT_NOT_ALLOWED
217  ERR_NOT_SELECT_STMT
218  ERR_BIND_VARS_NOT_ALLOWED
219  ERR_INTO_VARS_NOT_ALLOWED
220  ERR_FETCH_LIST_TOO_LONG
221  ERR_WRONG_NUM_BIND_VALUES
222  ERR_CURSOR_OPEN


ΓòÉΓòÉΓòÉ 5.3.3. 900-999 Fatal Errors ΓòÉΓòÉΓòÉ

901  ERR_NO_MEMORY
902  ERR_SESSION_NOT_FOUND
903  ERR_STMTBLK_NOT_FOUND
904  ERR_BAD_REXX_NAME
905  ERR_REXXVAR_ERROR