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.