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 Free PDF: 9 Ways to Improve your Database Skills

Oracle DUMP Return Formats

You can use several different return formats for this function.

Format Explanation
8 octal notation
10 decimal notation
16 hexadecimal notation
17 single characters
1008 octal notation with character set name
1010 decimal notation with character set name
1016 hexadecimal notation with character set name
1017 single 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_NAME DUMP_TEST
John Typ=1 Len=4: 74,111,104,110
Susan Typ=1 Len=5: 83,117,115,97,110
Tom Typ=1 Len=3: 84,111,109
Mark Typ=1 Len=4: 77,97,114,107
Steven Typ=1 Len=6: 83,116,101,118,101,110
Julie Typ=1 Len=5: 74,117,108,105,101
Michelle Typ=1 Len=8: 77,105,99,104,101,108,108,101
Andrew Typ=1 Len=6: 65,110,100,114,101,119
Robert Typ=1 Len=6: 82,111,98,101,114,116
Tanya Typ=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 Free PDF: 9 Ways to Improve your Database Skills

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your FREE PDF: 9 Ways to Improve your Database Skills