Oracle7 Server Concepts
Data Conversion
In some cases, Oracle supplies data of one datatype where it expects data of a different datatype. This is allowed when Oracle can automatically convert the data to the expected datatype using one of the following functions:
Implicit datatype conversions work according to the rules explained in the following two sections.
Note: If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for additional information involving data conversions and the MLSLABEL and RAW MLSLABEL datatypes.
Rule 1: Assignments
For assignments, Oracle can automatically convert the following:
- VARCHAR2 or CHAR to NUMBER
- VARCHAR2 or CHAR to ROWID
- VARCHAR2 or CHAR to LABEL
The assignment succeeds if Oracle can convert the datatype of the value used in the assignment to that of the assignment's target.
Note: For the examples in the following list, assume a package with a public variable and a table declared as in the following statements:
var1 CHAR(5);
CREATE TABLE table1 (col1 NUMBER);
The datatype of expression must be 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, and so on, must either be the same as or convertible to the datatypes of the 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 convert the following:
- VARCHAR2 or CHAR to NUMBER
Some common types of expressions follow:
- Simple expressions, such as the following:
- Boolean expressions, such as the following:
- Function and procedure calls, such as the following:
- WHERE clause conditions, such as the following:
WHERE hiredate = '01-JAN-91'
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 simple or complex. If it succeeds, 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 has the default format 'DD-MON-YY'.