Oracle CHARTOROWID() Function

Oracle CHARTOROWID() is a built-in function that converts a given string value to the ROWID data type.

Oracle `CHARTOROWID()` Syntax

Here is the syntax of the Oracle CHARTOROWID() function:

CHARTOROWID(rowid_str)

Parameters

rowid_str: Required. The string representation of the ROWID value. It can be a value of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type.

Return Value

The Oracle CHARTOROWID() function converts a given string value to the ROWID data type and returns the converted value.

If you pass an invalid rowid value, Oracle reports an error.

If any parameter is NULL, CHARTOROWID() returns NULL.

Oracle `CHARTOROWID()` Examples

Here are some examples that demonstrate the usage of the Oracle CHARTOROWID() function.

Basic Usage

The following statement converts a character-type rowid string representation to a value of the ROWID type:

SELECT
    CHARTOROWID('AAAFD1AAFAAAABSAA/')
FROM dual;

输出：

CHARTOROWID('AAAFD1AAFAAAABSAA/')
____________________________________
AAAFd1AAFAAAABSAA/

Here is another example:

SELECT
    CHARTOROWID('AAATiDAAMAAALKzABa')
FROM dual;

输出：

CHARTOROWID('AAATIDAAMAAALKZABA')
____________________________________
AAATiDAAMAAALKzABa

The output looks similar to a regular string. To understand the type of the output result of the CHARTOROWID() function, you can use the DUMP() function to view its internal result:

SELECT
    DUMP('A'),
    DUMP(CHARTOROWID('AAATiDAAMAAALKzABa'))
FROM dual;

输出：

DUMP('A')           DUMP(CHARTOROWID('AAATIDAAMAAALKZABA'))
___________________ _____________________________________________
Typ=96 Len=1: 65    Typ=69 Len=10: 0,1,56,131,3,0,178,179,0,90

You can see that the type ID of a regular string is 96, while the type ID of the return value of the CHARTOROWID() function is 69.

Invalid Rowid

If you pass an invalid rowid value, Oracle reports an error.

SELECT
    CHARTOROWID('abc')
FROM dual;

输出：

SQL Error: ORA-01410: invalid ROWID
01410. 00000 -  "invalid ROWID"

NULL Parameter

If any parameter is NULL, CHARTOROWID() returns NULL.

SET NULL 'NULL';
SELECT
    CHARTOROWID(NULL)
FROM dual;

输出：

CHARTOROWID(NULL)
____________________
NULL

In this example, we use the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion