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

Developing a Pro*Ada Program


Passing data between Oracle and your Pro*Ada application requires host variables, event handling, and access to Oracle. This chapter introduces the specific packages provided with Pro*Ada that you use to meet these requirements. The following topics are in this chapter:


Datatypes

Oracle recognizes internal and external datatypes:

At precompile time, each host variable in the Declare Section is associated with an external datatype code. At run time, the datatype code of every host variable used in a SQL statement is passed to Oracle. Oracle uses the codes to convert between internal and external datatypes.

Internal Datatypes

For database columns and pseudocolumns, Oracle uses the following internal datatypes:

Code Name Description
1 VARCHAR2 <= 2000-byte, variable-length string
2 NUMBER fixed or floating point number
8 LONG <= 217483647-byte, variable-length string
11 ROWID fixed-length binary number
12 DATE 7-byte, fixed-length date/time value
23 RAW <= 255-byte, variable-length binary data
24 LONG RAW <= 217483647-byte, variable-length binary data
96 CHAR <= 255-byte, fixed-length string
105 MLSLABEL variable-length binary label, 2-5 bytes
Table 3 - 1. Internal Datatypes

These internal datatypes can be different from Ada datatypes. For example, the NUMBER datatype was designed for portability, precision (no rounding error), and correct collating. Ada has no equivalent datatype.

Brief descriptions of the Oracle internal datatypes follow. For more information, see the Oracle7 Server SQL Language Reference Manual.

VARCHAR2

Use the VARCHAR2 datatype to store variable-length character strings. The maximum width of a VARCHAR2 database column is 2000 bytes. To define a VARCHAR2 column, use the syntax

column_name  VARCHAR2(maximum_width) 

where maximum_width is an integer literal in the range 1 .. 2000.

Specify the maximum width of a VARCHAR2(n) column in bytes, not characters. If a VARCHAR2(n) column stores multi-byte characters, its maximum width is less than n characters.

NUMBER

Use the NUMBER datatype to store fixed or floating point numbers of virtually any size. You can specify precision, which is the total number of digits, and scale, which determines where rounding occurs.

The maximum precision of a NUMBER value is 38. The magnitude range is 1.0E-129 to 9.99E125. Scale can range from -84 to 127. For example, a scale of -3 means the number is rounded to the nearest thousand (3456 becomes 3000). A scale of 2 means the value is rounded to the nearest hundredth (3.456 becomes 3.46).

When you specify precision and scale, Oracle does extra integrity checks before storing the data. If you do not specify precision and scale, they default to 38 and 10, respectively.

LONG

Use the LONG datatype to store variable-length character strings. LONG columns can store text, arrays of characters, or even documents. The LONG datatype is like the VARCHAR2 datatype, except that the maximum width of a LONG column is 2147483647 bytes or two gigabytes.

Note: You can use LONG columns in UPDATE, INSERT, and (most) SELECT statements, but not in expressions, function calls, or certain SQL clauses such as WHERE, GROUP BY, and CONNECT BY. Only one LONG column is currently allowed per database table, and that column cannot be indexed.

ROWID

Internally, every table in an Oracle database has a pseudocolumn named ROWID that stores values called rowids. ROWIDs uniquely identify rows and provide the fastest way to access particular rows.

DATE

Use the DATE datatype to store dates and times in 7-byte, fixed-length fields. The date portion defaults to the first day of the current month. The time portion defaults to midnight.

RAW

Use the RAW datatype to store binary data or byte strings (a sequence of graphics characters, for example). RAW data is not interpreted by Oracle. The maximum width of a RAW column is 255 bytes.

RAW data is like VARCHAR2 data, except that Oracle assumes nothing about the meaning of RAW data and does no character set conversions (ASCII to EBCDIC for example) when you transmit RAW data from one system to another.

LONG RAW

Use the LONG RAW datatype to store binary data or byte strings. The maximum width of a LONG RAW column is 2147483647 bytes or two gigabytes.

LONG RAW data is like LONG data, except that Oracle assumes nothing about the meaning of LONG RAW data and does no character set conversions when you transmit LONG RAW data from one system to another.

Note: The restrictions that apply to LONG data also apply to LONG RAW data.

CHAR

Use the CHAR datatype to store fixed-length character strings. The maximum width of a CHAR column is 255 bytes.

MLSLABEL

With Trusted Oracle7, use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle7 uses labels to control access to data. The maximum length of a MLSLABEL column is 5 bytes. For more information, see the Trusted Oracle7 Server Administrator's Guide.

You can use the MLSLABEL datatype to define a column. However, with standard Oracle, such columns can store nulls only. With Trusted Oracle7, you can insert any valid operating system label into a column of type MLSLABEL. If the label is in text format, Trusted Oracle7 converts it to a binary value to reduce the length.

SQL Pseudocolumns and Functions

SQL recognizes the following pseudocolumns and parameter-less functions that return specific data items:

Pseudocolumn Corresponding Internal Datatype
NEXTVAL NUMBER
CURRVAL NUMBER
ROWNUM NUMBER
LEVEL NUMBER
ROWID ROWID
ROWLABEL MLSLABEL
Table 3 - 2. SQL Pseudocolumns

Pseudocolumns are not actual columns in a table. However, pseudocolumns are treated like columns, so their values must be SELECTed from a table. Sometimes it is convenient to SELECT pseudocolumn values from a dummy table.

Function Corresponding Internal Datatype
USER VARCHAR2
UID NUMBER
SYSDATE DATE
Table 3 - 3. SQL Functions

You can reference SQL pseudocolumns and functions in SELECT, INSERT, UPDATE, and DELETE statements. The following example uses the pseudocolumn SYSDATE to compute the number of months since an employee was hired. The date of hire is found in the actual database column HIREDATE.

EXEC SQL SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE) 
    INTO :MONTHS_OF_SERVICE 
    FROM emp 
    WHERE EMPNO = :EMP_NUMBER; 

Brief descriptions of the SQL pseudocolumns and functions follow. For more detailed information, see the Oracle7 Server SQL Language Reference Manual.

NEXTVAL returns the next number in a specified sequence. After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. In the following example, you use the sequence named partno to assign part numbers:

EXEC SQL INSERT INTO PARTS 
    VALUES (partno.NEXTVAL, :DESCRIPTION, :QUANTITY, :PRICE); 

If a transaction generates a sequence number, the sequence is incremented immediately. A reference to NEXTVAL stores the current sequence number in CURRVAL.

CURRVAL returns the current number in a specified sequence. Before you reference CURRVAL, you must use NEXTVAL to generate a sequence number.

ROWNUM returns a number indicating the sequence in which a row was selected from a table. The first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the selected rows before the sort is done.

You can use ROWNUM to limit the number of rows returned by a SELECT statement. Also, you can use ROWNUM in an UPDATE statement to assign unique values to each row in a table. Using ROWNUM in the WHERE clause does not stop the processing of a SELECT statement. It just limits the number of rows retrieved. The only meaningful use of ROWNUM in a WHERE clause is

... WHERE ROWNUM < constant; 

because the value of ROWNUM increases only when a row is retrieved. The following search condition can never be met because the first four rows are not retrieved:

... WHERE ROWNUM = 5; 

LEVEL returns the level number of a node in a tree structure. The root is level 1, children of the root are level 2, grandchildren are level 3, and so on.

LEVEL is used in the SELECT CONNECT BY statement to incorporate some or all the rows of a table into a tree structure. In an ORDER BY or GROUP BY clause, LEVEL segregates the data at each level in the tree.

Specify the direction in which the query walks the tree (down from the root or up from the branches) with the CONNECT BY operator. In the START WITH clause, specify a condition that identifies the root of the tree. See the Oracle7 Server SQL Reference manual for examples of the LEVEL and ORDER BY clauses.

ROWID returns a row address in hexadecimal.

ROWLABEL with standard Oracle returns a null. With Trusted Oracle7, ROWLABEL returns the operating system label for a row in the format specified by the Oracle initialization parameter MLS_LABEL_FORMAT.

A common use of ROWLABEL is to filter query results. For example, the following statement counts only those rows with a security level higher than unclassified:

EXEC SQL SELECT COUNT(*) INTO :HEAD_COUNT FROM emp 
    WHERE ROWLABEL > 'UNCLASSIFIED'; 

For more information, see the Trusted Oracle7 Server Administrator's Guide.

USER returns the username of the current Oracle user.

UID returns the unique ID number assigned to an Oracle user.

SYSDATE returns the current date and time.

External Datatypes

As Table 3 - 4 shows, the external datatypes include all the internal datatypes plus several datatypes found in host languages other than Ada. For example, the STRING datatype refers to a C null-terminated string, and the DECIMAL datatype refers to a COBOL packed decimal.

Code Name Description
1 VARCHAR2 <= 2000-byte, variable-length string
2 NUMBER fixed or floating point number
3 INTEGER 2-byte or 4-byte signed integer
4 FLOAT 4-byte or 8-byte floating point number
5 STRING null-terminated character string
6 VARNUM variable-length binary number
7 DECIMAL COBOL or PL/I packed decimal
8 LONG <= 2147483647-byte, variable-length string
9 VARCHAR <=65533-byte, variable-length binary data
11 ROWID fixed-length binary number
12 DATE 7-byte, fixed-length date/time value
15 VARRAW <= 65533-byte, variable-length binary data
23 RAW <= 255-byte, variable-length binary data
24 LONG RAW <= 217483647-byte, variable-length binary data
68 UNSIGNED 2-byte or 4-byte unsigned integer
94 LONG VARCHAR <= 217483647-byte, variable-length string
95 LONG VARRAW <= 217483647-byte, variable-length string
96 CHAR <= 255-byte, fixed-length character string
106 MLSLABEL variable-length binary label, 2-5 bytes
Table 3 - 4. External Datatypes

Brief descriptions of the external datatypes follow.

VARCHAR2

By default, unless MODE=ANSI, Oracle assigns the VARCHAR2 datatype to all character host variables. Use the VARCHAR2 datatype to store variable-length character strings. The maximum length of a VARCHAR2 value is 2000 bytes.

On Input Oracle reads the number of bytes specified for the input host variable, strips any trailing blanks, then stores the input value in the target database column. Be careful. An uninitialized host variable can contain null characters. So, always blank-pad a character-input host variable to its declared length.

If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value is all-blank, Oracle treats it like a null.

Oracle can convert a character value to a NUMBER column value if the character value represents a valid number. Otherwise, Oracle generates an error.

On Output Oracle returns the number of bytes specified for the output host variable, blank-padding if necessary. Then Oracle assigns the output value to the target host variable. If a null value is returned, Oracle fills the host variable with blanks.

If the output value is longer than the declared length of the host variable, Oracle truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle sets it to the original length of the output value.

Oracle can convert NUMBER column values to character values. The length of the character host variable determines precision. If the host variable is too short for the number, scientific notation is used. For example, if you SELECT the column value 123456789 into a string(1..6), Oracle returns the value "1.2E08."

NUMBER

Use the NUMBER datatype to store fixed or floating point Oracle numbers. You can specify precision and scale. The maximum precision of a NUMBER value is 38; the magnitude range is 1.0E-129 to 9.99E125. Scale can range from -84 to 127.

NUMBER values are stored in variable-length format, starting with an exponent byte and followed by up to 20 mantissa bytes. The high-order bit of the exponent byte is a sign bit that is set for positive numbers. The low-order 7 bits represent the exponent, a base-100 digit with an offset of 65.

Each mantissa byte is a base-100 digit in the range 1 .. 100. For positive numbers, 1 is added to the digit. For negative numbers, the digit is subtracted from 101, and, unless there are 20 mantissa bytes, a byte containing 102 is appended to the data bytes. Each mantissa byte can represent two decimal digits. The mantissa is normalized, and leading zeros are not stored. You can use up to 20 data bytes for the mantissa, but only 19 are guaranteed to be accurate. The 19 bytes, each representing a base-100 digit, allow a maximum precision of 38 digits for a value of the NUMBER datatype.

On output, the host variable contains the number as represented internally by Oracle. To accommodate the largest possible number, the output host variable must be 21 bytes long. Only the bytes used to represent the number are returned. Oracle does not blank-pad or null-terminate the output value for the NUMBER datatype. If you need to know the length of the returned value, use the VARNUM datatype instead.

Note: Normally, there is little reason to use this datatype.

INTEGER

Use the INTEGER datatype to store numbers that have no fractional part. An integer is a signed, 2-byte or 4-byte binary number. The order of the bytes in a word is system-dependent. You must specify a length for input and output host variables. On output, if the column value is a floating point number, Oracle truncates the fractional part.

FLOAT

Use the FLOAT datatype to store numbers that have a fractional part or that exceed the capacity of the INTEGER datatype. The number is represented using the floating-point format of your system and typically requires 4 or 8 bytes of storage. You must specify a length for input and output host variables.

Oracle represents numbers with greater precision than floating point implementations because the internal format of Oracle numbers is decimal.

STRING

The STRING datatype is like the VARCHAR2 datatype, except that a STRING value is always null-terminated.

This external datatype is used mostly in Pro*C; it is seldom needed in a Pro*Ada program.

VARNUM

The VARNUM datatype is like the NUMBER datatype, except that the first byte of a VARNUM variable stores the length of the value.

On input, you must set the first byte of the host variable to the length of the value. On output, the host variable contains the length followed by the number as represented internally by Oracle. To accommodate the largest possible number, the host variable must be 22 bytes long. After SELECTing a column value into a VARNUM host variable, you can check the first byte to get the length of the value.

Normally, there is little reason to use this datatype.

DECIMAL

This datatype is not used in Pro*Ada. See the Programmer's Guide to the Oracle Precompilers for a description of the DECIMAL datatype.

LONG

Use the LONG datatype to store variable-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 2147483647 bytes or two gigabytes.

VARCHAR

Use the VARCHAR datatype to store variable-length character strings. VARCHAR variables have a 2-byte length field followed by a <= 65533-byte string field. However, for VARCHAR array elements, the maximum length of the string field is 65530 bytes. When you specify the length of a VARCHAR variable, be sure to include 2 bytes for the length field. For longer strings, use the LONG VARCHAR datatype.

ROWID

Use the ROWID datatype to store binary rowids in fixed-length fields. The field size is port-specific.

Additional Information: Consult your platform-specific Oracle documentation for more information on the field size of the ROWID.

You can use VARCHAR2-compatible string host variables to store rowids in a readable format. When you SELECT or FETCH a rowid into a string host variable, Oracle converts the binary value to an 18-byte character string and returns it in the format

BBBBBBBB.RRRR.FFFF 

where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file. These numbers are hexadecimal. For example, the rowid

0000000E.000A.0007 

points to the 11th row in the 15th block in the 7th database file.

Typically, you FETCH a rowid into an Ada host variable, then compare the host variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement. That way, you can identify the latest row fetched by a cursor. For an example, see "Mimicking CURRENT OF" [*].

Note: If you need full portability or your application communicates with a non-Oracle database via SQL*Connect, specify a maximum length of 2000 (not 18) bytes when declaring the host variable. If your application communicates with a non-Oracle database via Oracle Open Gateway, specify a maximum length of 256 bytes. Although you can assume nothing about its contents, the host variable will behave normally in SQL statements.

DATE

Use the DATE datatype to store dates and times in 7-byte, fixed-length fields. As Table 3 - 5 shows, the century, year, month, day, hour (in 24-hour format), minute, and second are stored in that order from left to right.

Byte 1 2 3 4 5 6 7
Meaning Century Year Month Day Hour Minute Second
Example 06-DEC-1990 119 190 12 6 1 1 1
Table 3 - 5. Date Example

The century and year bytes are in excess-100 notation. The hour, minute, and second are in excess-1 notation. Dates before the Common Era (B.C.E.) are less than 100. The epoch begins on January 1, 4712 B.C. For this date, the century byte is 53 and the year byte is 88. The hour byte ranges from 1 to 24. The minute and second bytes range from 1 to 60. The time defaults to midnight (1, 1, 1).

Normally, there is little reason to use this datatype.

VARRAW

You use the VARRAW datatype to store binary data or byte strings. The VARRAW datatype is like the RAW datatype, except that VARRAW variables have a 2-byte length field followed by a <= 65533-byte data field. For longer strings, use the LONG VARRAW datatype.

When you specify the length of a VARRAW variable, be sure to include 2 bytes for the length field. The first two bytes of the variable must be interpretable as an integer. To get the length of a VARRAW variable, simply refer to its length field.

RAW

Use the RAW datatype to store binary data or byte strings. The maximum length of a RAW value is 255 bytes.

RAW data is like CHAR data, except that Oracle assumes nothing about the meaning of RAW data and does no character set conversions when you transmit RAW data from one system to another.

LONG RAW

Use the LONG RAW datatype to store binary data or byte strings. The maximum length of a LONG RAW value is 2147483647 bytes or two gigabytes.

LONG RAW data is like LONG data, except that Oracle assumes nothing about the meaning of LONG RAW data and does no character set conversions when you transmit LONG RAW data from one system to another.

UNSIGNED

Use the UNSIGNED datatype to store unsigned integers. An unsigned integer is a binary number of 2 or 4 bytes. The order of the bytes in a word is system-dependent. You must specify a length for input and output host variables. On output, if the column value is a floating point number, Oracle truncates the fractional part.

LONG VARCHAR

Use the LONG VARCHAR datatype to store variable-length character strings. LONG VARCHAR variables have a 4-byte length field followed by a string field. The maximum length of the string field is 2147483643 bytes. When you specify the length of a LONG VARCHAR variable, be sure to include 4 bytes for the length field.

LONG VARRAW

Use the LONG VARRAW datatype to store variable-length binary data or byte strings. LONG VARRAW variables have a 4-byte length field followed by a data field. The maximum length of the data field is 2147483643 bytes. When you specify the length of a LONG VARRAW variable, be sure to include 4 bytes for the length field.

CHAR

By default, when MODE=ANSI, Oracle assigns the CHAR datatype to all character host variables. Use the CHAR datatype to store fixed-length character strings. The maximum length of a CHAR value is 255 bytes.

On Input Oracle reads the number of bytes specified for the input host variable, does not strip trailing blanks, then stores the input value in the target database column.

If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value is all-blank, Oracle treats it like a character value.

On Output Oracle returns the number of bytes specified for the output host variable, blank-padding if necessary, then assigns the output value to the target host variable. If a null is returned, Oracle fills the host variable with blanks.

If the output value is longer than the declared length of the host variable, Oracle truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle sets it to the original length of the output value.

MLSLABEL

Use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle7 uses labels to control access to data. For more information, see the Trusted Oracle7 Server Administrator's Guide.

You can use the MLSLABEL datatype to define a column. However, with standard Oracle, such columns can store nulls only. With Trusted Oracle, you can insert any valid operating system label into a column of type MLSLABEL.

On Input Trusted Oracle7 translates the input value into a binary label, which must be a valid operating system label. If it is not, Trusted Oracle7 issues an error message. If the label is valid, Trusted Oracle7 stores it in the target database column.

On Output Trusted Oracle7 converts the binary label to a character string, which can be of type CHAR, CHARZ, STRING, VARCHAR, or VARCHAR2.

VARCHAR2 Versus CHAR

The VARCHAR2 and CHAR datatypes differ in subtle but significant ways. CHAR semantics have changed slightly to comply with the current ANSI/ISO SQL standard. The changes come into play when you compare, INSERT, UPDATE, SELECT, or FETCH character values.

On Input When MODE=ANSI, if both values being compared in a SQL statement belong to type CHAR, blank-padding semantics are used. That is, before comparing character values of unequal length, Oracle blank-pads the shorter value to the length of the longer value. For example, if ENAME is a CHAR database column and EMP_NAME is a CHAR host variable (by default or via datatype equivalencing), the following search condition is TRUE when the column value "BELL" and the host value "BELL " are compared:

... WHERE ENAME = :EMP_NAME; 

When MODE=ORACLE, if either or both values in a comparison belong to type VARCHAR2, non-blank-padding semantics are used. That is, when comparing character values of unequal length, Oracle makes no adjustments and uses the exact lengths. For example, if JOB is a CHAR column and JOB_TITLE is a VARCHAR2 host variable, the following search condition is FALSE when the column value "CLERK" and the host value "CLERK " are compared:

... WHERE JOB = :JOB_TITLE; 

When you INSERT a character value into a CHAR database column, if the value is shorter than the defined width of the column, Oracle blank-pads the value to the defined width. As a result, information about trailing blanks is lost. If the character value is longer than the defined width of the CHAR column, Oracle generates an error. Oracle neither truncates the value nor tries to trim trailing blanks.

When you INSERT a character value into a VARCHAR2 database column, if the value is shorter than the defined width of the column, Oracle does not blank-pad the value. Nor does Oracle strip trailing blanks. Character values are stored intact, so no information is lost. If the character value is longer than the defined width of the VARCHAR2 column, Oracle generates an error. Oracle neither truncates the value nor tries to trim trailing blanks. The same rules apply when UPDATEing.

On Output When you SELECT a column value into a CHAR host variable, if the value is shorter than the declared length of the variable, Oracle blank-pads the value to the declared length. For example, if EMP_NAME is a CHAR(15) host variable (by default or via datatype equivalencing), and you SELECT a 10-byte column value into it, Oracle adds 5 blanks. If the column value is longer than the declared length of the CHAR host variable, Oracle truncates the value, stores it, and generates a warning.

When you SELECT a column value into a VARCHAR2 host variable, if the value is shorter than the declared length of the variable, Oracle does not blank-pad the value. Nor does Oracle strip trailing blanks. If the column value is longer than the declared length of the VARCHAR2 host variable, Oracle truncates the value, stores it, and generates a warning. The same rules apply when FETCHing.


Declaring and Referencing Host Variables

Every host variable used in a SQL statement must be declared as an Ada program variable. You declare a host variable in the same way as you declare any Ada variable. Host variable names can be any length permitted by your Ada compiler, but only the first 31 characters are significant to the precompiler. The normal scoping rules for Ada variables apply to host variables.

If you precompile your program with the option MODE=ORACLE, you can declare and use host variables of the types described in this section. If you precompile your program with MODE=ANSI, you must use the datatypes defined in the supplied SQL_STANDARD package. For more information, see "The SQL_STANDARD Package" [*]. For more information about the command line options such as MODE, see Chapter 11, "Running the Pro*Ada Precompiler."

A host variable can be any valid usage of a simple variable, a function call, an array slice, a selected record component, or a qualified reference to a variable in another package. The datatype of the host variable must be:

Both VARCHAR and ROWID are Oracle datatypes as well as types predefined by Pro*Ada for use with host variables.

Pro*Ada does not do checking on constrained subtypes or derived types. For example, in the following code fragment the constraint error is caught at runtime, not at precompile time or compile time:

type STYPE is integer range 1..2; 
MY_SHORT    : STYPE; 
... 
EXEC SQL SELECT 3 INTO :MY_SHORT FROM dual; 
... 

Table 3 - 6 lists the Ada base types, followed by the Pro*Ada-defined datatypes that can be used as host variables. If a function call is used as a host variable, it must return one of these types. Only these types can normally be used as host variables. However, for an example that shows how to use derived types, see "Derived Types as Host Variables" [*].

Ada or Oracle Datatype Meaning
integer Ada integer type, size is system dependent
float Ada floating point number
long_float Ada double precision floating point
short_integer a smaller integer, size is system dependent
short_short_integer the smallest integer, size is system dependent
string Ada string type
INDICATOR Oracle-defined type for indicator variables
RAW Oracle-defined binary type
ROWID Oracle-defined row identifier type
STRING_DEF Oracle-defined string access type
VARCHAR Oracle-defined record, with body and length components
VARRAW Oracle-defined record
Table 3 - 6. The Pro*Ada Predefined Datatypes

Arrays of scalar types and of strings can also serve as host variables, using the Oracle array interface. For more information, see Chapter 7, "Using Host Arrays."

A host variable and its source or target database column do not need to have similar types. But they must be compatible. Oracle automatically converts between most Oracle and Ada datatypes. For example, if a database column of type NUMBER is SELECTed into a string variable, Oracle does the conversion from the internal binary NUMBER format to the ASCII characters in the Ada string.

Figure 3 - 1 shows how host variable datatypes (in the left-hand column) are compatible with Oracle datatypes (on the top row). A blank box indicates that the datatypes are not compatible. For example, you cannot convert an Ada numeric type to an Oracle date. If you try to convert between incompatible types, an "incompatible datatypes" error occurs at runtime.

Figure 3 - 1. Possible Datatype Conversion

Data conversion follows normal programming practices. For example, you cannot convert an Oracle NUMBER value that is greater than 2147483647 to an Ada integer (on most systems). If you try this, Oracle will produce a "converting column overflows integer datatype" runtime error. Also, you cannot convert a string host variable that contains characters other than `0'-`9', `E', `e', `.', `,', `+', or `-' to an Oracle NUMBER column.

The SQL Declare Section

You can declare program variables to be used in SQL statements (that is, host variables) in a special SQL Declare Section. Use of the SQL Declare Section is optional in Pro*Ada programs.

A SQL Declare Section begins with the statement

EXEC SQL BEGIN DECLARE SECTION; 

and ends with the statement

EXEC SQL END DECLARE SECTION; 

Between these two statements only the following are allowed:

Because the EXEC SQL {BEGIN | END} DECLARE SECTION statements are simply ignored by the precompiler, variables declared in a SQL Declare Section are treated just like any other Ada declared variables, and the same scoping rules apply to them.

Type Checking

The precompiler does not do type or mode checking of host variables. All type and mode checking is done at Ada compile time, and all rules of the Ada language apply.

Pro*Ada defines tightly constrained bindings between the converted Oracle types and host variable types. These bindings cannot be manipulated by the Pro*Ada programmer. Consequently, all of the integrity built into the type checking mechanisms of the Ada language is maintained by the interface between Pro*Ada and Oracle.

Ada declarations of host variables must be consistent with the implied modes of parameters in the SQL statement where they appear. For example, Ada constants cannot be referenced as host output variables.

Most violations in host variable declaration or use are detected and reported at compile time, usually because the Ada compiler cannot resolve an argument in a procedure call generated by the precompiler. These calls are typically to the procedures SQL_BIND, SQL_INTO, and SQL_STORE. These procedures are used explicitly in dynamic SQL Pro*Ada programs. For more information about these procedures, see Chapter 9, "Implementing Dynamic SQL Method 4."

The usual cause of such an error is the use of an unsupported Ada type for a host variable. For more information about Ada types, see "Derived Types as Host Variables" [*]. Certain violations, such as an attempt to perform an array operation using a non-array host variable, are detected only at runtime.

The RAW Datatype

The Pro*Ada predefined RAW datatype holds binary data that is not intended to be interpreted as character data. You can declare a RAW type variable with a length of 255 as follows:

BIN_DATA   :  ORACLE.RAW(1..255); 

Note: A RAW value greater than 255 bytes can only be inserted into a LONG RAW column. The maximum useable length of a RAW is 65535 bytes.

Pro*Ada predefines neither operators nor input/output subprograms for the RAW datatype.

The ROWID Datatype

If you SELECT a ROWID pseudocolumn into a host variable that is a string or a Pro*Ada STRING_DEF, the ROWID is returned as a 18-byte ASCII string, with values coded in hexadecimal. For example, the following code fragment

MY_ROWID   :  string(1..18); 
... 
EXEC SQL SELECT rowid INTO :MY_ROWID 
    FROM emp WHERE ename = 'ALLEN'; 
PUT_LINE("ROWID is " & MY_ROWID); 

would print something like

ROWID is 000017D8.0001.0002 

which in this case means the 6104th block, 2nd row, in the 2nd database file. The actual value printed would differ from case to case.

However, if you use the Pro*Ada-defined ROWID datatype in a query, you receive the ROWID in a system-specific binary format, as shown in the following example:

MY_ROWID   :  ORACLE.ROWID; 
... 
EXEC SQL SELECT rowid INTO :MY_ROWID 
    FROM emp 
    WHERE ename = 'ALLEN'; 
 
PUT("Size of binary ROWID is "); 
PUT(ORACLE.ROWID'size); 
PUT_LINE(" bits."); 

This example shows that the size of the binary ROWID type is not usually 18 bytes. The size varies from system-to-system. With VAX/VMS Pro*Ada, it is 13 bytes.

Note: Pro*Ada does not predefine any operators or special input/output subprograms for the ROWID datatype.

For an example that uses the binary ROWID datatype, see "Mimicking CURRENT OF" [*].

The STRING_DEF Datatype

STRING_DEF is a Pro*Ada predefined access type for host variables of type string. It can be used just like the string type. For example, the following code fragment declares a host variable of type STRING_DEF, and then allocates the storage space and uses it in a SELECT statement:

EMP_NAME_ACC   :  ORACLE.STRING_DEF; 
... 
begin 
    EMP_NAME_ACC := new string(1..15); 
    ...
    EXEC SQL SELECT ename INTO :EMP_NAME_ACC 
            FROM emp 
            WHERE empno = 7499; 
    PUT_LINE( 
      "The employee who has empno 7499 is " & EMP_NAME_ACC.all); 

Note: The .all access indicator is not used with STRING_DEF host variables when they are used in SQL statements.

For input to and output from Oracle, host variables of type STRING_DEF obey the same rules as host variables of type string. For more information, see "Handling Character Data" [*].

Note: STRING_DEF variables cannot be used to form host arrays.

The VARCHAR Datatype

VARCHAR can be used instead of the string type as a variable-length character array. The VARCHAR type is a record with two components. It is defined as follows:

type VARCHAR (n      : integer) is record 
              length : unsigned_short := 0; 
              buffer : string(1..n); 
end record; 

One advantage of VARCHAR is that the length of the string is set by Oracle on output. In the following example, you use a VARCHAR string as a host variable in a query:

with text_io, 
     integer_text_io, 
     VARCHAR_TEXT_IO; 
... 
EMP_NAME     : ORACLE.VARCHAR(20);  -- max length is 20 
... 
EXEC SQL SELECT INITCAP(ename) INTO :EMP_NAME 
    FROM emp 
    WHERE ename = 'ALLEN'; 
 
text_io.PUT("The length of "); 
VARCHAR_TEXT_IO.PUT(EMP_NAME); 
text_io.PUT("'s name is "); 
integer_text_io.PUT(integer(EMP_NAME.LENGTH), 2); 
text_io.NEW_LINE;

and the program prints "The length of Allen's name is 5."

When you use a VARCHAR variable for input to Oracle, you must set the length component before performing the INSERT or UPDATE statement.

Assignment to a VARCHAR

You cannot directly assign a string literal to a VARCHAR. For example, the following code results in an Ada compile time error:

NAME   : ORACLE.VARCHAR(5); 
... 
NAME := "SMITH"; 

When you assign character data to the buffer component of a VARCHAR, you must also set the length component. For example:

NAME   : ORACLE.VARCHAR(10); 
... 
NAME.BUFFER(1..5) := "SMITH"; 
NAME.LENGTH := 5; 

It is possible to copy between VARCHARs of identical maximum length, using the assignment operator. For example, the following code fragment would print "Y is ABCDE":

X  : ORACLE.VARCHAR(5); 
Y  : ORACLE.VARCHAR(5); 
... 
X.buffer := "ABCDE"; 
X.length := 5;    -- length MUST be set
Y := X; 
PUT("Y is "); 
VARCHAR_TEXT_IO.PUT(Y); 
NEW_LINE; 

Other Operations

The equality ("="), inequality ("/="), greater than (">"), less than ("<"), and catenation ("&") operators are not defined by Oracle for VARCHARs.

VARCHAR_TEXT_IO

The Pro*Ada predefined package VARCHAR_TEXT_IO contains input/output procedures, parallel to those of the TEXT_IO package. These procedures are:

PUT( V : VARCHAR)
PUT_LINE ( V : VARCHAR)
GET ( V : IN OUT VARCHAR) 
GET_LINE ( V : IN OUT VARCHAR) 
GET_LINE ( V : IN OUT VARCHAR, LAST : NATURAL) 

When the VARCHAR_TEXT_IO package is used with GET, the length component is set by the procedure. If, for the code fragment

with text_io, 
     integer_text_io, 
     VARCHAR_TEXT_IO; 
... 
MY_V_STRING    : ORACLE.VARCHAR(80); 
... 
text_io.PUT("Enter a string: "); 
VARCHAR_TEXT_IO.GET_LINE(MY_V_STRING); 
text_io.PUT("The length is "); 
integer_text_io.PUT(integer(MY_V_STRING.LENGTH),WIDTH => 2); 
NEW_LINE; 

you enter the string "This is my string", the program will print the line: "The length is 17".

Returning Nulls to a VARCHAR

Oracle automatically sets the length component of a VARCHAR output host variable. If you SELECT or FETCH a null into a VARCHAR, Oracle sets the length component to zero. The previous value of the buffer component is unchanged.

The VARRAW Datatype

The predefined host variable datatype VARRAW provides a variable-length RAW binary capability. VARRAW is defined as a record:

type VARRAW (n      : integer) is record 
             length : unsigned_short := 0; 
             buffer : raw(1..n); 
end record; 

VARRAW behaves like VARCHAR with respect to the setting of the buffer and length components. There are no operators or input/output packages predefined for VARRAW, because VARRAW is intended for raw binary data, not character data. You can, of course, create your own input/output packages for VARRAW data.

Host Variable Example

The following example, declares 3 host variables, then uses a SELECT statement to search the database for an employee number matching the value of host variable EMP_NUMBER. When a matching row is found, Oracle sets output host variables DEPT_NUMBER and EMP_NAME to the values of columns DEPTNO and ENAME in that row.

-- declare host variables 
EMP_NUMBER    : integer; 
EMP_NAME      : string(1..10); 
DEPT_NUMBER   : integer; 
... 
PUT("Employee number: "); 
INTEGER_TEXT_IO.GET(EMP_NUMBER); 
... 
EXEC SQL SELECT deptno, ename INTO :DEPT_NUMBER, :EMP_NAME 
    FROM emp 
    WHERE empno = :EMP_NUMBER; 

Guidelines

The following guidelines apply to declaring and referencing host variables.

A host variable must be

A host variable must not be

A host variable can be


Declaring and Referencing Indicator Variables

You can associate each host variable with an optional indicator variable. You use indicator variables to assign null values to input host variables, and detect null or truncated values in output host variables.

You can use ORACLE.INDICATOR or integer as the type for indicator variables. When you precompile with MODE=ANSI, you must use the types defined in the SQL_STANDARD package. The types for indicator variables in that package are INDICATOR_TYPE or SMALLINT.

An Example

In this example, you declare 3 host variables and 1 indicator variable, then use a SELECT statement to search the database for an employee number matching the value of host variable EMP_NUMBER. When a matching row is found, Oracle sets output host variables SALARY and COMMISSION to the values of columns SAL and COMM in that row, and stores a return code in indicator variable IND_COMM. The next statement uses IND_COMM to select a course of action.

-- declare host and indicator variables 
EMP_NUMBER   : integer; 
SALARY       : float; 
COMMISSION   : float; 
IND_COMM     : indicator;  -- indicator variable 
PAY          : float; 
... 
PUT("Employee number: "); 
GET(EMP_NUMBER); 
... 
EXEC SQL SELECT sal, comm 
    INTO :SALARY, :COMMISSION :IND_COMM 
    FROM emp 
    WHERE empno = :EMP_NUMBER; 
... 
if IND_COMM = -1 then    -- commission is null 
    PAY := SALARY; 
else 
    PAY := SALARY + COMMISSION; 
end if; 
... 

To improve readability, you can precede any indicator variable with the optional keyword INDICATOR. You must still prefix the indicator variable with a colon. The correct syntax is

:HOST_VARIABLE INDICATOR :INDICATOR_VARIABLE 

which is equivalent to

:HOST_VARIABLE :INDICATOR_VARIABLE 

You can use both forms of expression in your host program. However, the keyword INDICATOR is not allowed in Ada statements.

Note: When you precompile with MODE=ANSI, you must use the INDICATOR keyword.

For more information about using indicator variables in your program, see "Using Indicator Variables" [*].

Guidelines

The following guidelines apply to declaring and referencing indicator variables.

An indicator variable must be:

An indicator variable must not be:


Handling Character Data

This section explains how the Pro*Ada Precompiler handles character host variables. Host variables for character data can be of the types:

On Input

When inputting data from string and STRING_DEF variables, the program interface strips any trailing blanks up to the first non-blank character. After stripping the blanks, the value is sent to the database.

When inputting data from a VARCHAR variable, only the number of characters specified in the length component is transmitted to the database. For example, in the following code fragment, only the value "BAKER" is transmitted:

NAME : ORACLE.VARCHAR(15); 
... 
NAME.buffer := "BAKERxxxxxxxxxx"; 
NAME.length := 5; 
 
EXEC SQL INSERT INTO emp (ename, empno, deptno) 
    VALUES (:NAME, 9999, 10); 

On Output

For variables of type string and STRING_DEF, output is blank-padded to the declared length of the variable. For example, the following code fragment:

NAME : string(1..15); 
JOB  : ORACLE.STRING_DEF; 
... 
NAME := "xxxxxxxxxxxxxxx"; 
JOB := new string'("xxxxxxxxxxxxxxx"); 
EXEC SQL SELECT ename, job 
    INTO :NAME, :JOB 
    FROM emp