Oracle CHARTOROWID Function: A Simple Guide

The CHARTOROWID function in Oracle SQL is a specialized conversion function. Its one and only job is to convert a string of text (like CHAR or VARCHAR2) that is formatted like a ROWID into an actual ROWID data type.

A ROWID is a special address that uniquely identifies a row in a table. This function is most often used when ROWIDs have been stored as text in another table or an application, and you need to convert them back into a usable ROWID to find the original row.

What is the CHARTOROWID Function in Oracle?

The CHARTOROWID(char) function takes a character string and interprets it as a ROWID.

This is useful for:

• Querying a table using a ROWID that you have stored as text.
• Migrating data where ROWIDs were saved in a VARCHAR2 column.

This is a technical function. You generally don't use it unless you are specifically working with ROWIDs that have been converted to text.

CHARTOROWID Function Syntax

The syntax for CHARTOROWID is very simple:

CHARTOROWID(char)

Let's break that down:

• char: The text string you want to convert to a ROWID. This string must be in the valid ROWID format (e.g., 'AAAFd1AAFAAAABSAA/').

Oracle CHARTOROWID Function Examples

Here are two practical examples of how to use CHARTOROWID.

Example 1: Basic Conversion using CHARTOROWID

This example simply takes a text string that looks like a ROWID and converts it into the ROWID data type.

(Note: The actual ROWID string used here is just an example and will not work on your database. Every ROWID is unique to its own database.)

Query:

SELECT 
  CHARTOROWID('AAAFd1AAFAAAABSAA/') AS "Converted_ROWID"
FROM DUAL;

Result: (The output will be an actual ROWID data type, which may look the same as the string.)

Converted_ROWID
------------------
AAAFd1AAFAAAABSAA/

Example 2: Finding a Row by its Text ROWID using CHARTOROWID

This is the most common use case. Imagine you have a ROWID (perhaps from an error log) stored as text, and you want to find the corresponding employee.

(Note: You must first find a valid ROWID from your own employees table to run this example. You can get one by running SELECT ROWID, last_name FROM employees WHERE ROWNUM = 1; and then pasting that ROWID into the query.)

Query:

-- First, get a valid ROWID:
-- SELECT ROWID FROM employees WHERE last_name = 'Greene';
-- (Let's assume the result is 'AAAFd1AAFAAAABSAA/')

-- Now, use that text string with CHARTOROWID in the WHERE clause:
SELECT 
  last_name
FROM 
  employees
WHERE 
  ROWID = CHARTOROWID('AAAFd1AAFAAAABSAA/');

Result: (If the ROWID is correct, Oracle will find that specific row.)

LAST_NAME
-------------------------
Greene