CHARTOROWID Function in Oracle SQL – PLSQL

The CHARTOROWID function in Oracle SQL / PLSQL is used to convert the CHAR, VARCHAR2, NCHAR or NVARCHAR2 data-type into ROWID type.

Syntax for using the CHARTOROWID function Oracle SQL / PLSQL is:

SELECT CHAROROWID (value)
FROM table_name;

    • value is the value to be converted to ROWID
    • The format of ROWID is BBBBBBB.RRRR.FFFFF

Here,
BBBBBBB is the block in the database file.
RRRR is the row in the block.
FFFFF is the database file.

Example:

Using CHARTOROWID function

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

If we write our query as:

SELECT *
FROM employee
WHERE ROWID = CHARTOROWID ('AAAPD2AABAAAS2aAAA');

Will return a unique row from employee table.


Tagged , , , , , , , , , , , . Bookmark the permalink.