FB

Oracle DUMP FunctionThe 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:

DUMP ( 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.

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

Oracle DUMP Return Formats

You can use several different return formats for this function.

FormatExplanation
8octal notation
10decimal notation
16hexadecimal notation
17single characters
1008octal notation with character set name
1010decimal notation with character set name
1016hexadecimal notation with character set name
1017single characters with character set name

 

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.

SELECT DUMP('Complete IT Professional') as DUMP_TEST FROM dual;

Result:

DUMP_TEST
Typ=96 Len=24: 67,111,109,112,108,101,116,101,32,73,84,32,80,114,111,102,101,115,115,105,111,110,97,108

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

SELECT DUMP('Complete IT Professional', 8) as DUMP_TEST FROM dual;

Result:

DUMP_TEST
Typ=96 Len=24: 103,157,155,160,154,145,164,145,40,111,124,40,120,162,157,146,145,163,163,151,157,156,141,154

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.

SELECT DUMP('Complete IT Professional', 10) as DUMP_TEST FROM dual;

Result:

DUMP_TEST
Typ=96 Len=24: 67,111,109,112,108,101,116,101,32,73,84,32,80,114,111,102,101,115,115,105,111,110,97,108

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.

SELECT DUMP('Complete IT Professional', 16) as DUMP_TEST FROM dual;

Result:

DUMP_TEST
Typ=96 Len=24: 43,6f,6d,70,6c,65,74,65,20,49,54,20,50,72,6f,66,65,73,73,69,6f,6e,61,6c

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.

SELECT DUMP('Complete IT Professional', 17) as DUMP_TEST FROM dual;

Result:

DUMP_TEST
Typ=96 Len=24: C,o,m,p,l,e,t,e, ,I,T, ,P,r,o,f,e,s,s,i,o,n,a,l

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.

SELECT DUMP('Complete IT Professional', 1008) as DUMP_TEST FROM dual;

Result:

DUMP_TEST
Typ=96 Len=24 CharacterSet=AL32UTF8: 103,157,155,160,154,145,164,145,40,111,124,40,120,162,157,146,145,163,163,151,157,156,141,154

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.

SELECT DUMP('Complete IT Professional', 10, 9) as DUMP_TEST FROM dual;

Result:

DUMP_TEST
Typ=96 Len=24: 32,73,84,32,80,114,111,102,101,115,115,105,111,110,97,108

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.

SELECT DUMP('Complete IT Professional', 10, 9, 5) as DUMP_TEST FROM dual;

Result:

DUMP_TEST
Typ=96 Len=24: 32,73,84,32,80

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.

SELECT first_name, DUMP(first_name) as DUMP_TEST FROM student;

Result:

FIRST_NAMEDUMP_TEST
JohnTyp=1 Len=4: 74,111,104,110
SusanTyp=1 Len=5: 83,117,115,97,110
TomTyp=1 Len=3: 84,111,109
MarkTyp=1 Len=4: 77,97,114,107
StevenTyp=1 Len=6: 83,116,101,118,101,110
JulieTyp=1 Len=5: 74,117,108,105,101
MichelleTyp=1 Len=8: 77,105,99,104,101,108,108,101
AndrewTyp=1 Len=6: 65,110,100,114,101,119
RobertTyp=1 Len=6: 82,111,98,101,114,116
TanyaTyp=1 Len=5: 84,97,110,121,97

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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your SQL Cheat Sheets Now: