DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > Controlled Data Type Conversion With the Cast Function

Controlled Data Type Conversion With the Cast Function

This feature reduces the complexity of the code we write for data type conversions within SQL and PL/SQL code blocks and increases application performance.

Emrah Mete user avatar by
Emrah Mete
CORE ·
May. 15, 17 · Database Zone · Tutorial
Like (5)
Save
Tweet
3.35K Views

Join the DZone community and get the full member experience.

Join For Free

In this article, I will talk about Oracle 12c Release 2 with a new feature on the SQL side.

Cast function is an SQL function that transforms between data types, as is known by a majority. This function can convert from built-in or collection-typed data types to other built-in or collection-typed data types. Briefly, let's remember how it works.

-- EXAMPLE
SELECT CAST ('123456' AS NUMBER) + 5 FROM DUAL;

CAST('123456'ASNUMBER)+5
------------------------
                  123461

-- EXAMPLE
SELECT CAST ('10.10.2017' AS DATE) + 5 FROM DUAL;

CAST('10.10.2017'ASDATE)+5
--------------------------
15/10/2017   

-- EXAMPLE
SELECT CAST (SYSDATE AS TIMESTAMP) FROM DUAL;

CAST(SYSDATEASTIMESTAMP)                          
--------------------------------------------------
14/2/2017 08:21:59,000000  

We remembered how the function worked, but when the data types that could not be converted to CAST function before Oracle 12c R2 were sent, the function was getting an error and the program was terminated.

-- EXAMPLE
SELECT CAST ('sdfsj343' AS NUMBER) FROM DUAL; 
--> ERROR : ORA-01722: invalid number

-- EXAMPLE
SELECT CAST ('7878987' AS DATE) castdate FROM DUAL;
--> ERROR : ORA-01847: day of month must be between 1 and last day of month

These kinds of mistakes are the types of errors that we encounter in our codes according to the amount of data we process. Especially in systems where Data Quality problems exist, we often encounter such problems. When using this function with Oracle 12c R2, a mechanism is added to handle the problems that will arise as a result of possible data type mismatches. Along with this mechanism, a new option has been introduced to reduce or completely eliminate errors in type conversions. Let's see how it works now:

-- EXAMPLE
SELECT CAST ('fsweereg' AS DATE default sysdate on conversion error)
castdate1 from dual;

-- EXAMPLE
SELECT CAST ('fsweereg' AS DATE default '10-10-2017' on 
conversion error, 'DD-MM-YYYY') castdate2 from dual;

-- EXAMPLE
SELECT CAST ('fsdfs34' AS NUMBER default -99 on conversion error) 
castnumber from dual;

resultset

As can be seen from the examples, we can define the default values in a wrong conversion, so that the function continues in a controlled way. The CONVERSION ERROR clause that comes with Oracle 12c R2 can also be used in other casting functions such as to_number and to_date.

-- EXAMPLE
select to_number('32423fsfs'  default -99 on conversion error)
castdate from dual;

-- EXAMPLE
select to_date('32423fsfs'  default sysdate on conversion error)
castdate from dual;

-- EXAMPLE
select to_date('32423fsfs'  default '10-10-2017' on conversion error
, 'DD-MM-YYYY') castdate from dual;

I believe that this feature will reduce the complexity of the code we write for data type conversions within SQL and PL/SQL code blocks and increase our application performance at this point. Native code support for controlling data type conversion is a very important development in this context.

Data Types Data (computing) Type conversion

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Modern Application Security Requires Defense in Depth
  • SSH Tutorial: Nice and Easy [Video]
  • Five Tips to Fasten Your Skewed Joins in Apache Spark
  • Testing Your Infrastructure as Code Using Terratest

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo