Oracle ROWIDTOCHAR Function: A Simple Guide

The ROWIDTOCHAR function in Oracle SQL is a simple conversion function. Its one and only job is to convert a ROWID data type into a human-readable text string (VARCHAR2).

A ROWID is a special data type that represents the unique, physical, or logical address of a row in a table. It's not typically human-readable. ROWIDTOCHAR makes it possible to display this address, store it in a text column, or use it in a text-based comparison (like a LIKE operation).

This function is the direct opposite of CHARTOROWID.

What is the ROWIDTOCHAR Function in Oracle?

The ROWIDTOCHAR(rowid_value) function takes a ROWID and returns a VARCHAR2 string. The resulting string is always 18 characters long for standard rowids.

This is useful for:

• Debugging: Displaying the ROWID in a log file or on-screen to identify a specific row.
• Storing References: Saving a ROWID in a VARCHAR2 column in another table (though this is generally not recommended as ROWIDs can change).
• Text-Based Searching: Allowing you to use LIKE to find ROWIDs that match a certain pattern, which you cannot do on a raw ROWID type.

ROWIDTOCHAR Function Syntax

The syntax for ROWIDTOCHAR is very simple:

ROWIDTOCHAR(rowid_value)

Let's break that down:

• rowid_value: The ROWID value or column you want to convert (e.g., the ROWID pseudo-column).

Oracle ROWIDTOCHAR Function Examples

Here are two practical examples of how to use ROWIDTOCHAR.

Example 1: Selecting a ROWID as a String using ROWIDTOCHAR

This example shows the most basic use: retrieving the ROWID for an employee, not as a ROWID type, but as a standard VARCHAR2 string.

Query:

SELECT 
  last_name,
  ROWID, -- This is the ROWID data type
  ROWIDTOCHAR(ROWID) AS "Rowid_As_String" -- This is VARCHAR2
FROM employees
WHERE last_name = 'King';

Result: (The exact ROWID string will be different on every system)

LAST_NAME                 ROWID                Rowid_As_String
------------------------- ------------------ ------------------
King                      AAAFd1AAFAAAABSAAc   AAAFd1AAFAAAABSAAc

Example 2: Filtering on a Partial ROWID String using ROWIDTOCHAR

This example shows the main reason to use this function: it allows you to use text-based operators like LIKE to search for a ROWID. You cannot do this on the ROWID column directly.

Query:

-- This query finds all rows where the ROWID
-- (as a string) contains the characters 'AAFA'
SELECT 
  last_name, 
  ROWID
FROM employees 
WHERE ROWIDTOCHAR(ROWID) LIKE '%AAFA%';

Result: (This will return a list of employees whose ROWID matches the pattern. The exact result will vary.)

LAST_NAME                 ROWID
------------------------- ------------------
King                      AAAFd1AAFAAAABSAAc
Kochhar                   AAAFd1AAFAAAABSAAd
De Haan                   AAAFd1AAFAAAABSAAe
...