Oracle Database - Data type Conversion

> Database > Oracle Database

1 - About

Data type Conversion.

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

Advertising

3 - 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

4 - Documentation / Reference

db/oracle/datatype_conversion.txt · Last modified: 2017/10/18 17:18 by gerardnico