24 September, 2017

Casting Data Type in Oracle Database

CAST function 
The CAST function converts a value from one data type to another data type.

Syntax
CAST ( [ Expression | NULL | ? ] AS Datatype)

Note : CAST conversions among SQL-92 data types.
The flowing are SQL-92 data types
  1. BOOLEAN
  2. SMALLINT
  3. INTEGER
  4. BIGINT
  5. DECIMAL
  6. REAL
  7. DOUBLE
  8. FLOAT
  9. CHAR
  10. VARCHAR
  11. LONG VARCHAR
  12. CHAR
  13. VARCHAR
  14. LONG VARCHAR
  15. CLOB
  16. BLOB
  17. DATE
  18. TIME
  19. TIMESTAMP
  20. XML
Examples
SELECT CAST (SYSDATE AS VARCHAR2 (12)) DATE_TO_VARCHAR FROM DUAL;
SELECT CAST ('12' AS INTEGER)  STRING_TO_NUMBERIC FROM DUAL;
SELECT CAST (NULL AS VARCHAR2 (2)) NULL_TO_STRING FROM DUAL;

As CAST only convert among SQL-92 data types, we can not use for example RAW data type.
But there are some packages casts to RAW like utl_raw.cast_to_raw

SELECT UTL_RAW.CAST_TO_RAW('Mahmoud') FROM DUAL;
output is : "4D61686D6F7564"

OAF :Formatting DateTime Fields

Use the following code to format DateTime feilds in OAF OAWebBean departureDateBean = webBean.findChildRecursive("DepartureDate"...