Oracle/PLSQL: Dump Function

Oracle/PLSQL: Dump Function

---

In Oracle/PLSQL, the dump function returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression.

The syntax for the dump function is:

dump( expression, [return_format], [start_position], [length] )

expression is the expression to analyze.

return_format is optional. It determines the format of the return value. This parameter can be any of the following values:

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

start_position and length are optional parameters. They determines which portion of the internal representation to display. If these parameters are omitted, the dump function will display the entire internal representation in decimal notation.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */

     substrb(dump(min("PROMOTIONDISCOUNTCODE"),16,0,32),1,120),

     min("PROMOTIONDISCOUNTCODE"),

     dump(min("PROMOTIONDISCOUNTCODE")),

     dump(min("PROMOTIONDISCOUNTCODE"),16,0,32),

     dump(min("PROMOTIONDISCOUNTCODE"),0,0,32),

     dump(min("PROMOTIONDISCOUNTCODE"),1017,0,32)

from "EJMIS"."PASSENGERJOURNEYSEGMENT" t
 

For example:

dump('Tech')	would return 'Typ=96 Len=4: 84,101,99,104'
dump('Tech', 10)	would return 'Typ=96 Len=4: 84,101,99,104'
dump('Tech', 16)	would return 'Typ=96 Len=4: 54,65,63,68'
dump('Tech', 1016)	would return 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'
dump('Tech', 1017)	would return 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'