Table of Contents

About

Data type Conversion.

In an Oracle Net connection with Java, this task is given to the Two-Task Common (TTC) protocol.

Explicit vs Implicit vs No Conversion

In SqlPlus: 

create table tmp_source 
(
x varchar2(20)
);
insert into tmp_source (x) 
WITH data(r) AS (
  SELECT 1.0 r FROM dual
  UNION ALL
  SELECT r+1 FROM data WHERE r < 10000000
  )
  SELECT to_char(r) FROM data;
commit;

create table tmp_target
(
x number
);

SET TIMING ON 

prompt Implicit Conversion
TIMING START 
insert into tmp_target (x) SELECT x FROM tmp_source;
TIMING STOP


prompt Explicit Conversion
TIMING START 
insert into tmp_target (x) SELECT to_number(x) FROM tmp_source;
TIMING STOP

prompt No Conversion
TIMING START 
insert into tmp_source (x) SELECT x FROM tmp_source;
TIMING STOP

SET TIMING OFF

drop table tmp_source;
drop table tmp_target;

Implicit Conversion
10,000,000 rows inserted.
Elapsed: 00:00:10.942

Explicit Conversion
10,000,000 rows inserted.
Elapsed: 00:00:09.326

No Conversion
10,000,000 rows inserted.
Elapsed: 00:00:07.033

Documentation / Reference