Document Type | Technical Information
Category | Administration
Applicable Product Version | 7FS02PS
Document Number | TADTI095
Overview
This document provides precautions when values of different types are assigned to conditional clause columns in bind queries or literal queries, explained through examples of implicit type conversion.
Method
Implicit Type Conversion
Implicit type conversion can occur in the following cases.
1. When inserting or updating data of a different type into a column, or assigning data of a different type to a variable
2. When the two values compared in a conditional statement have different types
<Example>
SQL> CREATE TABLE emp (id NUMBER, current_credits NUMBER(3));
SQL> INSERT INTO emp VALUES (1004, 2);
...
SQL> DECLARE
cur_cred VARCHAR2(5);
BEGIN
SELECT current_credits INTO cur_cred
FROM emp WHERE id = 1004;
END;
The above example is a SELECT statement that retrieves the current credit number of the employee with ID 1004 from the emp table.
The data type of the column current_credits is NUMBER(3), while cur_cred is VARCHAR2(5).
tbPSM automatically converts the NUMBER type data to VARCHAR2 type and stores it in cur_cred assigned in the INTO clause.
Precautions
When assigning or comparing data of different types, the following situations may occur. It is recommended to match the types appropriately for the target.
Precautions When Using Implicit Type Conversion
When a VARCHAR2 type is converted to a DATE type, an exception may occur if the VARCHAR2 variable is not actually in DATE format.
When a LONG type variable is converted to NUMBER type, an exception may occur if the LONG variable is not actually in NUMBER format.
<Example>
SQL> DECLARE
a VARCHAR2(20) := '2025/04/30';
b VARCHAR2(20) := 'Tibero';
c VARCHAR2(20) := '1';
d DATE;
e NUMBER;
BEGIN
d := a; -- Conversion successful
d := b; -- Exception occurs
e := b; -- Exception occurs
e := c; -- Conversion successful
END;
For d, which is typed as DATE,
matching with a succeeds because a's data is in DATE format, allowing implicit conversion,
but matching with b fails because b's data is not in DATE format, so implicit conversion does not occur.
For e, which is typed as NUMBER,
matching with b fails because b's data is not in NUMBER format, so implicit conversion does not occur,
but matching with c succeeds because c's data is in NUMBER format, allowing implicit conversion.
<Example>
SQL> CREATE TABLE emp (id NUMBER);
SQL> INSERT INTO emp VALUES (1);
SQL> commit;
SQL> select * from emp where id = 1;
ID
----------
1
1 row selected.
SQL> select * from emp where id = 'a';
TBR-5074: Given string does not represent a number in proper format.
An error occurs when assigning a string ('a') instead of a number (1) to a NUMBER type column.
Precautions for Same Plan with Bind Variables
Depending on the type assigned to the bind variable, the result may be the same due to implicit type conversion, but the internal plan and the number of blocks read may differ.
Precautions When Plan/Block Count Changes
<Example - When Plan/Block Count Changes>
SQL> drop table t;
SQL> create table t(c1 varchar2(10));
SQL> insert into t
select '2025'||(lpad(trunc(dbms_random.value(1,12)), 2,'0'))||(lpad(trunc(dbms_random.value(1,30)), 2,'0')) c1
from dual
connect by level < 10000;
SQL> commit;
create index idx1_t on t(c1);
A. When VARCHAR2 is specified, and using bind variable query
SQL> select /*+ varchar2 - bind */ *
from t
where c1 < :c_v1;
1888 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:10, %%CPU:0, Rows:3164)
2 INDEX (RANGE SCAN): IDX1_T (Cost:10, %%CPU:0, Rows:3164)
Predicate Information
--------------------------------------------------------------------------------------------------------------------------------------------
2 - access: ("T"."C1" < :C_V1) (0.249)
B. When VARCHAR2 is specified, and using literal query
SQL> select /*+ varchar2 - literal */ *
from t
where c1 < '20250301';
1888 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:10, %%CPU:0, Rows:3164)
2 INDEX (RANGE SCAN): IDX1_T (Cost:10, %%CPU:0, Rows:3164)
Predicate Information
--------------------------------------------------------------------------------------------------------------------------------------------
2 - access: ("T"."C1" < '20250301') (0.249)
C. When NUMBER is specified, and using bind variable query
SQL> select /*+ number - bind */ *
from t
where c1 < :n_v1;
1888 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:4, %%CPU:0, Rows:1270)
2 INDEX (RANGE SCAN): IDX1_T (Cost:4, %%CPU:0, Rows:1270)
Predicate Information
--------------------------------------------------------------------------------------------------------------------------------------------
2 - access: ("T"."C1" < :N_V1) (0.100)
D. When NUMBER is specified, and using literal query
SQL> select /*+ number - literal */ *
from t
where c1 < 20250301;
1888 rows selected.
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------
1 TABLE ACCESS (FULL): T (Cost:13, %%CPU:0, Rows:1270)
Predicate Information
--------------------------------------------------------------------------------------------------------------------------------------------
1 - filter: ("T"."C1" < 20250301) (0.100)
Precautions When Results Differ
Depending on the type assigned to the bind variable, implicit type conversion may cause the results to differ.
<Example - When Results Differ>
SQL> drop table t2;
SQL> create table t2(c1 varchar(10));
SQL> insert into t2 values ('9');
SQL> insert into t2 values ('99');
SQL> insert into t2 values ('100');
SQL> insert into t2 values ('101');
SQL> commit;
SQL> select * from t2 where c1 > 9;
C1
----------
99
100
101
3 rows selected.
SQL> select * from t2 where c1 > '9';
C1
----------
99
1 row selected.
Results may vary depending on the type assigned in the conditional clause.
Note
You can check the table below for types allowed for implicit conversion, distinguished by source and target.
[Summary Table of Allowed Implicit Conversions]
- Columns: Source Type / Rows: Target Type
BIN_INT | PLS_INT | NUMBER | CHAR | VARCHAR2 | LONG | DATE | RAW | CLOB | BLOB | ROWID | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| BIN_INT | O | O | O | O | O | O | |||||
| PLS_INT | O | O | O | O | O | O | |||||
| NUMBER | O | O | O | O | O | O | |||||
| CHAR | O | O | O | O | O | O | O | O | O | O | |
| VARCHAR2 | O | O | O | O | O | O | O | O | O | O | |
| LONG | O | O | O | O | O | O | O | O | O | O | |
| DATE | O | O | O | ||||||||
| RAW | O | O | O | O | |||||||
| CLOB | O | O | O | ||||||||
| BLOB | O | ||||||||||
| ROWID | O | O | O |