function

TO_NCLOB Function in Oracle SQL – PLSQL

November 27, 2012 by techhoneyadmin

The TO_NCLOB function in Oracle SQL / PLSQL is used to convert a LOB value to NCLOB value.

Syntax for the TO_NCLOB function in Oracle SQL / PLSQL is:

SELECT TO_NCLOB(expression_1)
FROM table_name;

expression_1 can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB

Examples:

SELECT TO_NCLOB(employee_name)
FROM employee;

November 27, 2012 by techhoneyadmin

The TO_LOB function in Oracle SQL / PLSQL is used to convert a LONG or LONG RAW value to LOB value.

Syntax for the TO_LOB function in Oracle SQL / PLSQL is:

SELECT TO_LOB(column_name)
FROM table_name;

column_name can be LONG or LONG RAW.

Examples:

SELECT TO_LOB(long_column_name)
FROM employee;

November 27, 2012 by techhoneyadmin

The TO_DSInterval function in Oracle SQL / PLSQL is used to convert a string value to Interval Day to Second type.

Syntax for the TO_DSInterval function in Oracle SQL / PLSQL is:

SELECT TO_DSInterval(character_1 [nls_parameter])
FROM table_name;

character_1 can be CHAR, VARCHAR2, NCHAR or NVARCHAR2 data type, it is the value that will be converted to Interval Day to Second.
nls_paramter is a decimal character and group separator value.

Examples:

SELECT TO_DSINTERVAL('50 10:30:00')
FROM dual;

Would return ‘+50 10:30:00.000000’

November 27, 2012 by techhoneyadmin

The TO_CLOB function in Oracle SQL / PLSQL is used to convert a LOB value from national character set to database character set.

Syntax for the TO_CLOB function in Oracle SQL / PLSQL is:

SELECT TO_CLOB(expression_1)
FROM table_name;

expression_1 can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB or LOB_COLUMN

Examples:

SELECT TO_CLOB(employee_name)
FROM employee;

November 27, 2012 by techhoneyadmin

The RAWTOHEX function in Oracle SQL / PLSQL is used to convert raw value to hexadecimal values.

Syntax for the RAWTOHEX function in Oracle SQL / PLSQL is:

SELECT RAWTOHEX (raw_value)
FROM table_name;

raw_value is the raw value that is to be converted to hexadecimal value.

Examples:
Using RAWTOHEX in Oracle SQL / PLSQL SELECT Statement

SELECT RAWTOHEX('A')
FROM DUAL;

Will return ‘41’ as hexadecimal value.

SELECT RAWTOHEX('AB')
FROM DUAL;

Will return ‘4142’ as hexadecimal value.

SELECT RAWTOHEX('Tech Honey')
FROM DUAL;

Will return ‘5465636820486F6E6579’ as hexadecimal value.