Programmer's Guide to the Pro*Ada Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Brief descriptions of the Oracle internal datatypes follow. For more information, see the Oracle7 Server SQL Language Reference Manual.
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.
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.
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.
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 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.
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.
Pseudocolumn | Corresponding Internal Datatype |
NEXTVAL | NUMBER |
CURRVAL | NUMBER |
ROWNUM | NUMBER |
LEVEL | NUMBER |
ROWID | ROWID |
ROWLABEL | MLSLABEL |
Function | Corresponding Internal Datatype |
USER | VARCHAR2 |
UID | NUMBER |
SYSDATE | DATE |
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.
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 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.
Oracle represents numbers with greater precision than floating point implementations because the internal format of Oracle numbers is decimal.
This external datatype is used mostly in Pro*C; it is seldom needed in a Pro*Ada program.
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.
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.
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 |
Normally, there is little reason to use this 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 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 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.
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.
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.
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.
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:
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" .
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.
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:
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.
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.
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" .
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.
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.
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;
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".
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.
-- 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;
A host variable must be
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.
-- 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" .
An indicator variable must be:
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);
NAME : string(1..15);
JOB : ORACLE.STRING_DEF;
...
NAME := "xxxxxxxxxxxxxxx";
JOB := new string'("xxxxxxxxxxxxxxx");
EXEC SQL SELECT ename, job
INTO :NAME, :JOB
FROM emp