Oracle DUMP

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.

1) 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)

2) 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:

In this tutorial, you have learned how to use the Oracle DUMP() function to get the data type, length, and internal representation of an expression.

Was this tutorial helpful?