1 Writing a Pro*PL/1 Program

Dedicated exclusively to the Pro*PL/1 precompiler, this manual complements the language-independent Oracle Database Programmer's Guide to Oracle Precompilers. Concepts, terminology, methods, requirements, and options detailed in the Oracle Database Programmer's Guide to Oracle Precompilers.

Using the INCLUDE Statement

The precompiler first searches the current directory, then the directory specified by INCLUDE, and finally a directory for standard INCLUDE files. However, you must use INCLUDE to specify a directory path for non-default files unless they are stored in the current directory.

Event and Error Handling

Host Variables

Declaring Host Variables

Structures

An Example

Du kan bruge attribut factoring i en SQL Declare Section, som i EXEC SQL BEGIN DECLARE SECTION;.

Special Requirements

Referencing Host Variables

Although it may be confusing, you can give a host variable the same name as an Oracle table or column, as the previous example (SAL) showed.

Restrictions

Indicator Variables

Declaring Indicator Variables

LONG

Referencing Indicator Variables

Host Arrays

Declaring Host Arrays

Referencing Host Arrays

Pointers as Host Variables

EMP_STRUCT BASED, 2 EMP_NAME CHAR(20),

CHARACTER VARYING

VARYING Returning Nulls to a CHARACTER (N)

Handling Character Data

Effects of the MODE Option

When the mode is Oracle, the program interface removes the following spaces up to the first non-empty character. If you precompile this example in ANSI mode, the trailing blanks added by PL/1 are not stripped and the values 'MILLER' (four trailing blanks) and 'SALES' (three trailing blanks) are sent to the database.

The Oracle Datatypes

Internal Datatypes

SQL Pseudocolumns and Functions

External Datatypes

Datatype Conversion

Datatype Equivalencing

Host Variable Equivalencing

Embedding PL/SQL

Using Host Variables with PL/SQL

Using Indicator Variables with PL/SQL

Handling Nulls

Handling Truncated Values

SQLCHECK

Connecting to Oracle

Automatic Logins

For example, if your operating system username is RHILL and OPS$RHILL is a valid Oracle username, linking with '/' automatically logs you into Oracle as the OPS$RHILL user.

Concurrent Logins

You will learn how to deal with errors and state changes using the SQLSTATE, SQLCA, SQLCODE, and WHENEVER statements.

SQLSTATE, the SQLCA, and SQLCODE

Declaring SQLSTATE

SQLSTATE Values

0S invalid transformation group name specification 0T target table does not match cursor specification 0U attempt to map to non-updateable column 0V attempt to map to order column. Error Handling and Diagnosis 2-3 Table 2–2 shows how SQLSTATE status codes and conditions are mapped to Oracle errors.

Using SQLSTATE

Declaring SQLCODE

Using the SQLCA

The status code indicates whether the SQL statement executed successfully or threw an exception (error or warning). To promote interoperability (the ability of systems to easily exchange information), the SQL standard predefines all common SQL exceptions.

Declaring the SQLCA

SQLCA,

After executing an SQL statement, the Oracle Server returns a status code to the SQLSTATE variable that is currently in scope.

What's in the SQLCA?

Key Components of Error Reporting

Status Codes

Warning Flags

Rows-processed Count

Parse Error Offset

Error Message Text

Getting the Full Text of Error Messages

Using the WHENEVER Statement

For more information about the WHENEVER conditions and actions, see Chapter 7 of the Oracle Database Programmer's Guide to the Oracle Precompilers.

Scope of WHENEVER

Helpful Hint

Caution

Using the ORACA

Declaring the ORACA

ORACA

Enabling the ORACA

What's in the ORACA?

Precompiler Command

Precompiler Options

See Chapter 11 of the Oracle Database Programmer's Guide to Oracle Precompilers for a list of precompiler options. To view the online view, enter the precompiler command without arguments at your operating system prompt. The value of an option is a string literal, which can represent text or numeric values.

The option value is always separated from the option name by an equal sign, with no white space around the equal sign.

Default Values

Some options have Boolean values and you can display them with yes or no strings.

Determining Current Values

Case Sensitivity

Configuration Files

Scope of Options

For example, if you specify MAXLITERAL for file A but not for file B, SQL statements in file A are executed with the specified MAXLITERAL value, but SQL statements in file B are executed with the default value. There is one exception to this rule: the MAXOPENCURSORS value that is in effect when a connection to a database is made remains in effect for the lifetime of that connection. An option setting remains in effect until the end of the file unless you specify the option again.

For more information about other options, see the Oracle Database Programmer's Guide to the Oracle Precompilers.

DBMS

When DBMS=V6, the DESCRIBE operation of a fixed-length string (in method 4 of dynamic SQL) returns a data type code of 1. If you precompile with the DBMS=V6 option and connect to an Oracle version 7 database, then the data definition language statement, such as

MODE

If Oracle assigns a truncated column value to an output host variable, no error message is issued.

Entering Options

Special PL/1 Options

Performing Conditional Precompilations

Performing Separate Precompilations

Compiling and Linking

Sample Programs

Sample Program 1: Login and Query

Sample Program 2: Using a Cursor

Sample Program 3: Fetching in Batches

Sample Program 4: Datatype Equivalencing

After logging in, it creates a new table in the SCOTT account, IMAGE, and simulates placing bitmap images of employees in it. Later, when an employee number is entered, his/her bitmap is selected back from the IMAGE table and pseudo-displayed on the terminal screen. EXEC SQL SELECT EMP.EMPNO, ENAME, SAL, NVL(COMM,0), BITMAP INTO :EMP_NUMBER, :EMP_NAME, :SALARY, :COMMISSION, :BUFFER FROM EMP, IMAGE.

BIN FIXED(31);

Sample Program 5: A SQL*Forms User Exit

Note: The sample code provided is for a SQL*Forms user exit and is not intended to be compiled in the same way as the other sample programs listed in this chapter.

Sample Program 6: Dynamic SQL Method 1

This usage is "dynamic" because the SQL statement is a string variable whose contents the program can determine at runtime. Print diagnostic text containing the error message, current SQL statement, line number, and file name of the error.

Sample Program 7: Dynamic SQL Method 2

Note that the statement contains two host variable placeholders, V1 and V2, that require actual input host variables to be specified at the EXECUTE (next code). List the SQL statement and the values to use for the input host variables. The statement name is an SQL identifier, not a host variable, and therefore does not appear in the DECLARE SECTION.

The EXECUTE statement executes a prepared SQL statement using the specified input host variables, which are positionally substituted for placeholders in the PREPAREd statement.

Sample Program 8: Dynamic SQL Method 3

Note that the statement contains a host variable placeholder, V1, for which an actual input host variable must be provided in OPEN (see below). The OPEN statement evaluates the active set of the PREPARE query using the specified input host variables, which are positionally substituted for the placeholders in the PREPAREd query. For each occurrence of a placeholder in the statement there must be a variable in the USING clause.

If a wildcard occurs more than once in a statement, the corresponding variable must appear more than once in the USING statement.

Sample Program 9: Calling a Stored procedure

CREATE OR REPLACE PACKAGE BODY calldemo AS PAIN get_emp (dept_number IN number) IS SELECT ename, job, sal FROM emp. The procedure opens the cursor if it is not already open, retrieves a batch of rows, and - returns the number of rows actually retrieved. IF NOT get_emp%ISOPEN THEN -- open cursor if OPEN get_emp(dept_number); -- not already open END IF;.

When all rows have been retrieved, close the cursor and exit the loop, returning only the last set of rows found.

Meeting the Special Requirements of Method 4

What Makes Method 4 Special?

What Information Does Oracle Need?

Where Is the Information Stored?

How is the Information Obtained?

The SQLDA

Introducing the PL/1 SQLDA

Declaring a SQLDA

Multiple SQLDAs

The SQLDA Variables

SELDSC | BNDDSC}

SQLDNUM

SQLDFND

SQLDSC(N)

SQLDV

SQLDFMT

SQLDVLN

SQLDVTYP

See the section "Data types in SQLDA" later in this chapter for more information about data type codes.

SQLDI

SQLDH_VNAME

SQLDH_MAX_VNAMEL

SQLDH_CUR_VNAMEL

SQLDI_VNAME

SQLDI_MAX_VNAMEL

SQLDI_CUR_VNAMEL

SQLDFCLP

SQLDFCRCP

Datatypes in the SQLDA

Internal and External Datatypes

Implementing Dynamic SQL Method 4 5-9 SYSDATE, but these entities are always returned as one of the basic internal types. When you e.g. DESCRIBING a select list that contains a LONG RAW element, the length of the value is not returned in the SQLDVLN element. You can coerce the internal LONG RAW type to an external VARRAW type by placing an external data type code for VARRAW in the SQLDVTYP element after executing DESCRIBE but before executing FETCH.

The data returned on the FETCH will then include the length of the LONG RAW item in the first two bytes of the output buffer.

Coercing Datatypes After DESCRIBE

Extracting Precision and Scale

Note that the first parameter in the SQLPRC procedure call refers to the SQLDVLN element in the Jth minor structure of the SQLDSC array, and that the precision and scale parameters must be 4 bytes in size. The SQLPRC procedure returns zero as the precision and scale values for certain SQL data types. The SQLPR2 procedure is similar to SQLPRC, has the same syntax, and returns the same binary values, except for the data types listed in the following table:.

Datatype Codes

Implementing Dynamic SQL Method 4 5-11 Oracle Precompilers Guide for details on the format of. The data type codes listed in the preceding table are the ones you must set in the SQLDVTYP element of the SQLDA for data conversion.

Handling NULL/NOT NULL Datatypes

The Basic Steps

A Closer Look at Each Step

Declare a Host String

Set the Size of the Descriptors

Declare the SQLDAs

Declare the Data Buffers

Initialize the Descriptors

The data buffers are empty after initialization (except for SEL_DI and BND_DI, which were set to zero in the previous example code). As our example progresses and the DESCRIBE or FETCH statements begin to populate the data buffers, the values will appear in later figures. Unused or reserved fields in the descriptors (SQLDFMT, SQLDFMTL, SQLDFCLP, and SQLDFCRCP) are not shown in these figures.

Note: To save space, the names of the SQLDA elements in the left columns of Figures 5-3 through 5-9 are abbreviated.

Get the Query Text into the Host String

PREPARE the Query from the Host String

DECLARE a Cursor

When you declare a cursor for dynamic queries, the statement name given by PREPARE to the dynamic query is replaced by the static query.

DESCRIBE the Bind Variables

VariablesReset Maximum Number of Bind

Get Values for Bind Variables

Assuming the user entered a value of 625 for BONUS, Figure 5–6 shows the resulting link descriptor.

OPEN the Cursor

DESCRIBE the SelectList

Adjust the Select Descriptor Values

Implementing the Dynamic SQL Method 4 5-21 OPEN executes the query, identifies its active group, and places the cursor on the first row. Note that the lengths for the buffers that will hold the EMPNO and COMM fields are set to 6 and 9. These values were set in the preceding DO circuit by the EMP table column lengths of 4 and 7 by the statement that adds 2 to PRECISION (for the sign possible minus the decimal point).

FETCH a Row from the Active Set

Process the Select-List Items

CLOSE the Cursor

Using Host Arrays

Dynamic SQL Method 4 Program

This section presents a complete program that illustrates the steps required to use Dynamic SQL Method 4. These steps are outlined in the "Basic Steps" section earlier in this chapter, and are discussed in more detail in the following sections. In this demo program, each step outlined in "The Basic Steps" section earlier in this chapter is commented out in the source code. Note that due to the block structure of PL/1, the steps do not follow in order.).

1 TO BNDDSC.SQLDNUM;

1 TO SELDSC.SQLDNUM;

Topics

Each feature change is followed by one or more references to the relevant section(s) in this manual. Follow-up rules on page 1-1 PL/1 versions on page 1-3 Preprocessor on page 1-3 Explanation labels on page 1-4.

Index