Oracle DUMP Function

Summary: in this tutorial, you will learn how to use the Oracle DUMP() function to find the data type, length, and internal representation of an expression.

Introduction to Oracle DUMP() function #

The Oracle DUMP() function allows you to find the data type, length, and internal representation of a value.

The following illustrates the syntax of the DUMP() function:

DUMP ( expression [, return_format] [, start_position] [, length] )Code language: SQL (Structured Query Language) (sql)

Arguments #

The DUMP() function takes four arguments

expression #

Specifies an expression to be evaluated. It can be a column or an expression.

return_format #

Determines the format of the returned value. The return_format accepts one of the following values:

If you don’t specify the return_format, the DUMP() function will return the internal representation of the expression in decimal format (or 10).

start_position #

Specifies the starting position in the expression for which to return the internal representation.

length #

Specifies the length, of the start_position, in the expression for which to return the internal representation.

Return value #

The DUMP() function returns a value of VARCHAR2 type. If the expression is NULL, the DUMP() function will return NULL.

Examples #

Let’s take some examples of using the DUMP() function.

Basic Oracle DUMP() function examples #

The following example uses the DUMP() function to display the type, length, and internal representation of the string 'Oracle DUMP':

SELECT 
    DUMP('Oracle DUMP') AS result
FROM 
    DUAL; 
Code language: SQL (Structured Query Language) (sql)

Here is the result:

RESULT                                           
--------------------------------------------------
Typ=96 Len=11: 79,114,97,99,108,101,32,68,85,77,80
Code language: SQL (Structured Query Language) (sql)

In this result:

The Typ=96: 96 denotes the CHAR data type. (See the table for the mapping of the internal numbers and their corresponding data types)
Len=11: the length of the string is 11.
79,114,97,99,108,101,32,68,85,77,80 is the decimal (or ASCII) representation of the string 'Oracle DUMP' e.g., ASCII of O is 79, r is 114, and so on.

To display the actual characters of the string, you use the format 17:

SELECT 
    DUMP('Oracle DUMP',17) AS result
FROM 
    DUAL; Code language: SQL (Structured Query Language) (sql)

Here is the output:

RESULT                             
------------------------------------
Typ=96 Len=11: O,r,a,c,l,e, ,D,U,M,P
Code language: SQL (Structured Query Language) (sql)

Using Oracle DUMP() function with table data example #

This example uses the DUMP() function to show the data type, length, and internal representation of the customer names from the customers table:

SELECT 
    name, 
    DUMP(name) result
FROM 
    customers
ORDER BY 
    name;Code language: SQL (Structured Query Language) (sql)

This picture illustrates the partial result set:

Summary #

Use the Oracle DUMP() function to get the data type, length, and internal representation of an expression.

Was this tutorial helpful?