Oracle ROWIDTONCHAR Function: A Simple Guide

The ROWIDTONCHAR function in Oracle SQL is a character set-specific version of ROWIDTOCHAR. Its one and only job is to convert a ROWID data type into a human-readable text string, specifically in the national character set (as an NVARCHAR2 data type).

This is a technical function used when you need to store or display a ROWID as text in a Unicode or other national character set environment.

What is the ROWIDTONCHAR Function in Oracle?

The ROWIDTONCHAR(rowid_value) function takes a ROWID and returns an NVARCHAR2 string. The resulting string is always 18 characters long.

This is useful for:

• Displaying ROWIDs in an application that uses the national character set.
• Storing ROWID references in an NVARCHAR2 column.

ROWIDTONCHAR Function Syntax

The syntax for ROWIDTONCHAR is very simple:

ROWIDTONCHAR(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 ROWIDTONCHAR Function Examples

Here are two practical examples of how to use ROWIDTONCHAR.

Example 1: Basic Conversion using ROWIDTONCHAR

This example retrieves the ROWID for an employee, not as a ROWID type, but as an NVARCHAR2 string.

Query:

SELECT
  last_name,
  ROWIDTONCHAR(ROWID) AS "Rowid_As_String"
FROM employees
WHERE last_name = 'King';

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

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

Example 2: Checking the Data Type with DUMP using ROWIDTONCHAR

This example uses the DUMP function to prove that ROWIDTONCHAR returns a national character set string (Typ=1 for NVARCHAR2) while ROWIDTOCHAR returns a standard VARCHAR2 (Typ=96).

Query:

SELECT
  DUMP(ROWIDTONCHAR(ROWID)) AS "NVARCHAR2_Dump"
FROM employees
WHERE ROWNUM = 1;

SELECT
  DUMP(ROWIDTOCHAR(ROWID)) AS "VARCHAR2_Dump"
FROM employees
WHERE ROWNUM = 1;

Result: (Your results will vary, but note the Typ=1 for ROWIDTONCHAR)

NVARCHAR2_Dump
-----------------------------------------------------------------
Typ=1 Len=36: 0,65,0,65,0,65,0,70,0,100,0,49,0,65,0,65,0...

VARCHAR2_Dump
-----------------------------------------------------------------
Typ=96 Len=18: 65,65,65,70,100,49,65,65,70,65,65,65,66,83,65,65,99

The Len=36 (36 bytes) for the NVARCHAR2 string vs. Len=18 (18 bytes) for the VARCHAR2 string shows the difference in character set encoding.