Oracle Database - Data type Conversion

Card Puncher Data Processing

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





Discover More
Card Puncher Data Processing
Datatype - Conversion between Java, Oracle, SQL Server and DB2

Datatype conversions between Java, Oracle, Microsoft SQL Server and DB2. Java Oracle Microsoft SQL Server DB2 java.lang.String varchar2(255) nvarchar(50) varchar(255) int number(20,0) long...
Card Puncher Data Processing
Oracle - DataType

Each value manipulated by Oracle Database has a datatype. Large Object: Data Type Description VARCHAR2(size [BYTE | CHAR]) Variable-length character string having maximum length size...
Card Puncher Data Processing
Oracle - PL/SQL - DML error logging

LOG ERRORS handles errors quickly and simplifies batch loading. When you need to load millions of rows of data into a table, the most efficient way is usually to use an INSERT, UPDATE, or MERGE statement...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - Gateway (Heterogeneous services)

Gateway (Or Heterogeneous Services before 11g) make a non-Oracle system appear as a remote Oracle Database server through program that are called agent. The Oracle ODBC agent acts as an ODBC client and...
Card Puncher Data Processing
Oracle Database - NLS_DATE_LANGUAGE

NLS_DATE_LANGUAGE is a parameter which control the language used in the date format datatype and that you can use to control the implicit datatype conversion
Card Puncher Data Processing
Oracle Database - Two-Task Common (TTC) protocol

A protocol that is used in a typical Oracle Net connection to provide character set and data type conversion between different character sets or formats on the client and server. Two-Task...



Share this page:
Follow us:
Task Runner