Oracle PL/SQL provides several conversion functions that can be used to convert one data type to another. These functions are useful when you need to store data in a specific format or when you need to convert data for use in another application.
There are several different Oracle PL/SQL conversion functions, including CAST, TO_CHAR, TO_DATE, TO_NUMBER, TO_CLOB, and TO_TIMESTAMP. Here are some examples of PL/SQL conversion functions:
CAST function is used to convert a value from one data type to another. For example, you could use the CAST function to convert a date or number to a string.
SELECT CAST('21-JAN-2022' AS VARCHAR2(30)) FROM dual;
SELECT CAST(123 AS VARCHAR2(10)) FROM dual;
TO_CHAR function is used to convert a value to a string. This function is often used to convert dates and numbers to strings for use in SQL queries.
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM dual; SELECT TO_CHAR(123) FROM dual;
TO_DATE function is used to convert a string to a date. This function is often used when data is being imported from another application into Oracle.
SELECT TO_DATE('2022-01-01','YYYY-MM-DD') FROM dual;
TO_NUMBER function is used to convert a string to a number.
SELECT TO_NUMBER('123.45', '999.99') FROM dual;
SELECT TO_NUMBER('123') FROM dual;
TO_CLOB function is used to convert a value to a CLOB (Character Large Object). A CLOB is a data type that can store large amounts of character data.
TO_TIMESTAMP function is used to convert a value to a timestamp. A timestamp is a data type that stores both date and time information.
It’s important to note that these functions can raise exceptions if the conversion is not possible.