In Oracle Database, the need to handle different data types arises frequently. When this happens, it is crucial to perform data type conversion correctly. This article provides a detailed explanation of explicit data type conversion, focusing on the following points:
- Why you should use explicit conversion
- How to use basic conversion functions
- Practical examples from real-world scenarios
- Troubleshooting and performance optimization
This article uses examples based on the SCOTT sample schema. You can import and use the SCOTT sample schema by executing the following:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
Target Audience
- Individuals who are struggling with errors due to data type differences.
- Those who want to learn how to write efficient SQL.
- Beginners who handle Oracle in a professional setting.
Why is Explicit Data Type Conversion Necessary?
The Background of Data Type Conversion
In Oracle Database, when handling different data types, the following two types of conversion occur:
- Implicit Conversion: Performed automatically by Oracle.
- Explicit Conversion: Specified by the developer using functions.
The Risks of Implicit Conversion
Implicit conversion is convenient, but it comes with the following problems:
- Performance Degradation: Implicit conversion can sometimes invalidate indexes.
- Risk of Errors: Potential for unintended conversions to occur.
Example: Error due to implicit conversion
SELECT * FROM EMP WHERE HIREDATE = '2025-01-01';
HIREDATE is a DATE type, but an implicit conversion occurs when comparing it to a string. This may not produce the intended result.
SQL> SELECT HIREDATE FROM EMP;
HIREDATE
---------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82
14 rows selected.
SQL> SELECT * FROM EMP WHERE HIREDATE = '2025-01-01';
SELECT * FROM EMP WHERE HIREDATE = '2025-01-01'
*
ERROR at line 1:
ORA-01861: literal does not match format string
Basics of Explicit Data Type Conversion
You can avoid the problems above by performing explicit conversion. The conversion functions commonly used in Oracle are as follows.
1. TO_CHAR Function
Converts numbers or dates to strings. Its key feature is the ability to specify a format.
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') AS DATE_STRING
FROM dual;
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE_STRING
2 FROM dual;
DATE_STRING
------------------------------
2025-11-17
Practical Use:
- Cleaning up the appearance of reports.
- Displaying numbers in comma-separated or currency formats.
SELECT TO_CHAR(SAL, '999,999.99') AS FORMATTED_SALARY
FROM EMP;
SQL> SELECT TO_CHAR(SAL, '999,999.99') AS FORMATTED_SALARY
2 FROM EMP;
FORMATTED_S
-----------
800.00
1,600.00
1,250.00
2,975.00
1,250.00
2,850.00
2,450.00
3,000.00
5,000.00
1,500.00
1,100.00
950.00
3,000.00
1,300.00
14 rows selected.
2. TO_NUMBER Function
Converts a string to a number. It is mainly used for calculation processing.
SELECT ENAME, SAL + TO_NUMBER('1000') AS NEW_SALARY
FROM EMP;
SQL> SELECT ENAME, SAL + TO_NUMBER('1000') AS NEW_SALARY
2 FROM EMP;
ENAME NEW_SALARY
---------- ----------
SMITH 1800
ALLEN 2600
WARD 2250
JONES 3975
MARTIN 2250
BLAKE 3850
CLARK 3450
SCOTT 4000
KING 6000
TURNER 2500
ADAMS 2100
JAMES 1950
FORD 4000
MILLER 2300
14 rows selected.
Point to Note:
- An error will occur if the input string cannot be interpreted as a number.
3. TO_DATE Function
Converts a string to a DATE type. A specified format is mandatory.
SELECT TO_DATE('01/01/2025', 'MM/DD/YYYY') AS DATE_VALUE
FROM dual;
SQL> SELECT TO_DATE('2025-01-01', 'YYYY-MM-DD') AS DATE_VALUE
2 FROM dual;
DATE_VAL
--------
25-01-01
Practical Use:
- Extracting data using a date range.
SELECT ENAME,HIREDATE
FROM EMP
WHERE HIREDATE BETWEEN TO_DATE('01/01/1981', 'MM/DD/YYYY')
AND TO_DATE('12/31/1981', 'MM/DD/YYYY');
SQL> SELECT ENAME,HIREDATE
2 FROM EMP
3 WHERE HIREDATE BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD')
4 AND TO_DATE('1981-12-31', 'YYYY-MM-DD');
ENAME HIREDATE
---------- ---------
ALLEN 20-FEB-81
WARD 22-FEB-81
JONES 02-APR-81
MARTIN 28-SEP-81
BLAKE 01-MAY-81
CLARK 09-JUN-81
KING 17-NOV-81
TURNER 08-SEP-81
JAMES 03-DEC-81
FORD 03-DEC-81
10 rows selected.
Specific Examples Using Explicit Conversion
Case Study 1: Display hire date with formatting
SELECT ENAME, TO_CHAR(HIREDATE, 'MM/DD/YYYY HH24:MI:SS') AS FORMATTED_DATE
FROM EMP
WHERE DEPTNO = 10;
SQL> SELECT ENAME, TO_CHAR(HIREDATE, 'YYYY-MM-DD HH24:MI:SS') AS FORMATTED_DATE
2 FROM EMP
3 WHERE DEPTNO = 10;
ENAME FORMATTED_DATE
---------- -------------------
CLARK 1981-06-09 00:00:00
KING 1981-11-17 00:00:00
MILLER 1982-01-23 00:00:00
Case Study 2: Convert string salary to a number and calculate
SELECT ENAME, SAL, TO_NUMBER('1000') AS BONUS, SAL + TO_NUMBER('1000') AS TOTAL_SALARY
FROM EMP;
SQL> SELECT ENAME, SAL, TO_NUMBER('1000') AS BONUS, SAL + TO_NUMBER('1000') AS TOTAL_SALARY
2 FROM EMP;
ENAME SAL BONUS TOTAL_SALARY
---------- ---------- ---------- ------------
SMITH 800 1000 1800
ALLEN 1600 1000 2600
WARD 1250 1000 2250
JONES 2975 1000 3975
MARTIN 1250 1000 2250
BLAKE 2850 1000 3850
CLARK 2450 1000 3450
SCOTT 3000 1000 4000
KING 5000 1000 6000
TURNER 1500 1000 2500
ADAMS 1100 1000 2100
JAMES 950 1000 1950
FORD 3000 1000 4000
MILLER 1300 1000 2300
14 rows selected.
Case Study 3: Format a number as currency
SELECT ENAME, TO_CHAR(SAL, '$999,999.00') AS FORMATTED_SALARY
FROM EMP;
SQL> SELECT ENAME, TO_CHAR(SAL, '$999,999.00') AS FORMATTED_SALARY
2 FROM EMP;
ENAME FORMATTED_SA
---------- ------------
SMITH $800.00
ALLEN $1,600.00
WARD $1,250.00
JONES $2,975.00
MARTIN $1,250.00
BLAKE $2,850.00
CLARK $2,450.00
SCOTT $3,000.00
KING $5,000.00
TURNER $1,500.00
ADAMS $1,100.00
JAMES $950.00
FORD $3,000.00
MILLER $1,300.00
14 rows selected.
Troubleshooting and Points to Note
Common Errors
Error 1: Invalid number
SELECT TO_NUMBER('ABC') FROM dual; -- Error
SQL> SELECT TO_NUMBER('ABC') FROM dual;
SELECT TO_NUMBER('ABC') FROM dual
*
ERROR at line 1:
ORA-01722: invalid number
- Cause: ‘ABC’ cannot be converted to a number.
- Solution: Handle it beforehand using a
CASEstatement orNVL.
SELECT CASE
WHEN REGEXP_LIKE('ABC', '^\d+$') THEN TO_NUMBER('ABC')
ELSE NULL
END AS CONVERTED_VALUE
FROM dual;
Impact on Performance
If explicit conversion is not used appropriately, indexes can be invalidated.
Bad Example:
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE, 'MM/YYYY') = '02/1981';
Even if an index is set on HIREDATE, it will be invalidated by the conversion.
Good Example:
SELECT * FROM EMP
WHERE HIREDATE BETWEEN TO_DATE('01/01/1981', 'MM/DD/YYYY')
AND TO_DATE('12/31/1981', 'MM/DD/YYYY');
Visual Aid: Learning with a Flowchart
Flowchart Example: The Flow of Data Type Conversion
- Check the input data type.
- Select the necessary conversion function.
- Specify the format model.
- Check the result and avoid errors.
Practical Application Points
- Explicitly state the format model Eliminate ambiguity in conversion by specifying the appropriate format model.
- Avoid conversion on large data If frequent conversion is necessary on large-scale data, consider optimizing the table structure itself.
- Improve SQL maintainability When using conversions, add comments to clarify your intent.
Summary
Explicit data type conversion is an essential skill, indispensable for preventing errors, improving performance, and clarifying queries. Take the first step toward writing more efficient SQL by utilizing the basics and applications introduced in this article.
[reference]
Oracle Database SQL Language Reference, 19c
コメント