Oracle DUMP Function Explained with Examples

The Oracle DUMP function tells you the data type of an expression. Let's learn how to use it and see some examples in this article.

Purpose of the Oracle DUMP Function

The Oracle DUMP function will return a string that explains what the data type of an expression is. It also shows the length and the internal representation of the expression.

Syntax

The syntax of the DUMP function is:

1DUMP ( expression [, return_format] [, start_position] [, length] )

Parameters

The parameters of the Oracle DUMP function are:

• expression (mandatory): This is the expression that will be analysed and will have the information written out by the function. As with many functions, the expression can be a column or any other kind of expression.
• return_format (optional): This is the format of the data to be returned.
• start_position (optional): Denotes the starting position in the expression to return the internal representation for.
• length (optional): Denotes the length, from the start_position, in the expression to return the internal representation for.

Some notes about this function:

• The return type is VARCHAR2.
• If the expression is NULL, the function returns NULL.
• The returned result is always in the database character set.
• If return_format is omitted, the default value to use is 10.

Oracle DUMP Return Formats

You can use several different return formats for this function.

As you can see, adding a "10" before the return_format will include the character set name in the output.

Refer to the Examples section below for more information.

Examples of the DUMP Function

Here are some examples of the DUMP function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1 - Base Function

This example shows a simple DUMP function.

1SELECT DUMP('Database Star')
2FROM dual;

Result:

1Typ=96 Len=13: 68,97,116,97,98,97,115,101,32,83,116,97,114

The result shows the type number, the length of the expression, and a series of numbers that indicates the characters in the expression.

Example 2 - Format 8

This example shows the DUMP function with a return_format value of 8

1SELECT DUMP('Database Star', 8) 
2FROM dual;

Result:

1Typ=96 Len=13: 104,141,164,141,142,141,163,145,40,123,164,141,162

The result is similar to the first example but uses the octal notation for the expression.

Example 3 - Format 10

This example shows the DUMP function with a return_format value of 10.

1SELECT DUMP('Database Star', 10)
2FROM dual;

Result:

1Typ=96 Len=13: 68,97,116,97,98,97,115,101,32,83,116,97,114

The result is the same as the first example, as the default return_format value is 10, which is the decimal notation.

Example 4 - Format 16

This example shows the DUMP function with a return_format value of 16.

1SELECT DUMP('Database Star', 16)
2FROM dual;

Result:

1Typ=96 Len=13: 44,61,74,61,62,61,73,65,20,53,74,61,72

The result is similar to earlier examples but uses the hexadecimal notation.

Example 5 - Format 17

This example shows the DUMP function with a return_format value of 17.

1SELECT DUMP('Database Star', 17)
2FROM dual;

Result:

1Typ=96 Len=13: D,a,t,a,b,a,s,e, ,S,t,a,r

The result is similar to earlier examples, but the actual characters are shown

Example 6 - Format 1008

This example shows the DUMP function with a return_format value of 1008, which includes the character set name.

1SELECT DUMP('Database Star', 1008)
2FROM dual;

Result:

1Typ=96 Len=13 CharacterSet=AL32UTF8: 104,141,164,141,142,141,163,145,40,123,164,141,162

This result is similar to an earlier example but includes the CharacterSet inside the string.

Example 7 - Format 10 with Start Position

This example shows the DUMP function with a return_format value of 10, using a start_position.

1SELECT DUMP('Database Star', 10, 9)
2FROM dual;

Result:

1Typ=96 Len=13: 32,83,116,97,114

This example shows the expression in decimal format, but only shows the characters from the 9th position onwards.

Note that the start_position only impacts the representation of the characters returned, and not the Len part of the string.

Example 8 - Format 10 with Start Position and Length

This example shows the DUMP function with a return_format value of 10, using a start_position and a length.

1SELECT DUMP('Database Star', 10, 9, 5) 
2FROM dual;

Result:

1Typ=96 Len=13: 32,83,116,97,114

This example shows the expression from the 9th position onwards, and only 5 characters long.

Note that the start_position and length only impacts the representation of the characters returned, and not the Len part of the string.

Example 8 - Column

This example shows the DUMP function with a column.

1SELECT
2first_name,
3DUMP(first_name)
4FROM student;

Result:

This example shows the first_names from the student table, along with the DUMP information.

Similar Functions

Some functions which are similar to the DUMP function are:

• LENGTH - This function shows the length of the specified value.

You can find a full list of Oracle SQL functions here.