ROWIDTONCHAR() Function in Oracle

by

In Oracle Database, the ROWIDTONCHAR() function converts a ROWID value to NVARCHAR2 data type

It’s similar to the ROWIDTOCHAR() function, except that ROWIDTOCHAR() converts a ROWID value to VARCHAR2 data type.

Syntax

The syntax goes like this:

ROWIDTONCHAR(rowid)

Example

Here’s an example:

SELECT ROWIDTONCHAR('AAATiBAAMAAAAIDAAE')
FROM DUAL;

Result:

AAATiBAAMAAAAIDAAE

The result of the conversion is always in the national character set and is 18 characters long.

And here’s a dump of the result when compared to the CHARTOROWID() function (which returns a ROWID value from character data):

SELECT 
    DUMP(CHARTOROWID('AAATiBAAMAAAAIDAAE'), 17, 1, 4) AS "CHARTOROWID",
    DUMP(ROWIDTONCHAR('AAATiBAAMAAAAIDAAE'), 17, 1, 4) AS "ROWIDTONCHAR"
FROM DUAL;

Result:

                 CHARTOROWID               ROWIDTONCHAR 
____________________________ __________________________ 
Typ=69 Len=10: ^@,^A,8,81    Typ=1 Len=36: ^@,A,^@,A

A type ID of 69 means that it’s of ROWID data type, and a type ID of 1 means that it’s either VARCHAR2 or NVARCHARCHAR2.

A Database Example

Here’s an example that returns a row in a database table, based on a given ROWID:

SELECT 
    ROWID,
    FIRST_NAME,
    LAST_NAME
FROM EMPLOYEES
WHERE ROWIDTONCHAR(ROWID) LIKE '%KzABa';

Result:

                ROWID    FIRST_NAME    LAST_NAME 
_____________________ _____________ ____________ 
AAATiDAAMAAALKzABa    Timothy       Gates

Null Argument

If the argument is null, the result is null:

SET NULL 'null';
SELECT ROWIDTONCHAR(null)
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Invalid Argument Count

Calling ROWIDTONCHAR() without any arguments results in an error:

SELECT ROWIDTONCHAR()
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"

And passing too many arguments also results in an error:

SELECT ROWIDTONCHAR('a', 'b')
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"