CHARTOROWID() Function in Oracle

by

In Oracle Database, the CHARTOROWID() function converts a string value to ROWID data type.

Syntax

The syntax goes like this:

CHARTOROWID(char)

Where char is one of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data types.

Example

Here’s an example to demonstrate:

SELECT 
    CHARTOROWID('AAATiDAAMAAALKzABa')
FROM DUAL;

Result:

AAATiDAAMAAALKzABa

Although this is a quick and easy example, it doesn’t show us that the end result is of type ROWID.

However, we can find out like this:

SELECT 
    DUMP('AAATiDAAMAAALKzABa', 17, 1, 5) AS "r1",
    DUMP(CHARTOROWID('AAATiDAAMAAALKzABa'), 17, 1, 5) AS "r2"
FROM DUAL;

Result:

                         r1                              r2 
___________________________ _______________________________ 
Typ=96 Len=18: A,A,A,T,i    Typ=69 Len=10: ^@,^A,8,83,^C

Here, I used the DUMP() function to get the internal representation of the first five characters.

But importantly, this function also returns the type ID. The first one is 96, which is the type ID for CHAR and NCHAR, and the second one is 69, which is the type ID for ROWID.

So this demonstrates that the CHARTOROWID() function does in fact convert a string to a ROWID.

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 ROWID = CHARTOROWID('AAATiDAAMAAALKzABa');

Result:

                ROWID    FIRST_NAME    LAST_NAME 
_____________________ _____________ ____________ 
AAATiDAAMAAALKzABa    Timothy       Gates

Invalid ROWID

Passing an invalid ROWID results in an error:

SELECT CHARTOROWID('oops')
FROM DUAL;

Result:

Error report -
ORA-01410: invalid ROWID

Null Argument

If the argument is null, the result is null:

SET NULL 'null';
SELECT CHARTOROWID(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 CHARTOROWID() without any arguments results in an error:

SELECT CHARTOROWID()
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 CHARTOROWID('AAATiDAAMAAALKzABa', 'AAATiDAAMAAALKzABa')
FROM DUAL;

Result:

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