Oracle7 Server Application Developer's Guide
Data Conversion
In some cases, Oracle allows data of one datatype where it expects data of a different datatype. Generally, an expression cannot contain values with different datatypes. However, Oracle can use the following functions to automatically convert data to the expected datatype:
Implicit datatype conversions work according to the rules explained in the following two sections.
Note: In this discussion, CHAR is used to refer to the class of all character string datatypes.
If using Trusted Oracle, see ![[*]](jump.gif)
for additional information involving data conversions and the MLSLABEL and RAW MLSLABEL datatypes.
Rule 1: Assignments
For assignments, Oracle can perform the following conversions automatically
The assignment succeeds if Oracle can convert the datatype of the value used in the assignment to that of the assignment's target. The four types of assignments are explained below.
For the examples in the following list, assume a package with a public variable declared as
var1 CHAR(5);
and a table created with the following statement:
CREATE TABLE table1 (col1 NUMBER);
The datatype of expression must either be the same as, or convertible to, the datatype of variable. For example, Oracle automatically converts the data provided in the following assignment within the body of a stored procedure:
var1 := 0
- INSERT INTO table VALUES (expression1, expression2, ...)
The datatypes of expression1, expression2, etc., must either be the same as, or convertible to, the datatypes of corresponding columns in table. For example, Oracle automatically converts the data provided in the following INSERT statement for TABLE1 (see table definition above):
INSERT INTO table1 VALUES ('19');
- UPDATE table SET column = expression
The datatype of expression must either be the same as, or convertible to, the datatype of column. For example, Oracle automatically converts the data provided in the following UPDATE statement issued against TABLE1:
UPDATE table1 SET col1 = '30';
- SELECT column INTO variable FROM table
The datatype of column must either be the same as, or convertible to, the datatype of variable. For example, Oracle automatically converts data selected from the table before assigning it to the variable in the following statement:
SELECT col1 INTO var1 FROM table1 WHERE col1 = 30;
Rule 2: Expression Evaluation
For expression evaluation, Oracle can automatically perform the same conversions as for assignments. An expression is converted to a type based on its context. For example, operands to arithmetic operators are converted to NUMBER and operands to string functions are converted to VARCHAR2.
Some common types of expressions follow:
- Simple expressions, such as
comm + '500'
- Boolean expressions, such as
bonus > sal / '10'
- Function and procedure calls, such as
MOD (counter, '2')
- WHERE clause conditions, such as
WHERE hiredate = TO_DATE('1993-01-01','yyyy-mm-dd')
- WHERE clause conditions, such as
WHERE rowid = '00000DC5.000C.0001'
In general, Oracle uses the rule for expression evaluation when a datatype conversion is needed in places not covered by the rule for assignment conversions.
In assignments of the form
variable := expression
Oracle first evaluates expression using the conversions covered by Rule 2; expression can be as simple or complex as desired. If it succeeds, the evaluation of expression results in a single value and datatype. Then, Oracle tries to assign this value to the assignment's target using Rule 1.
CHAR to NUMBER conversions only succeed if the character string represents a valid number. CHAR to DATE conversions only succeed if the character string satisfies the session default format, NLS_DATE_FORMAT.