Using Oracle Dump Function

Oracle dump() function is a useful tool for looking into the "real value", not just the displayed. For example, we may be puzzled to see the results of the query below. Message "data analytics" in Record 3 spans two lines.

SQL> select * from tbl_test;

        ID MESSAGE
---------- --------------------------------
         1 Hello
         2 World
         3 data
            analytics

To find out what is going on, we use dump() function to examine the content of message. On record 3, there is a ASCII character 10 which represents a line new.

SQL> select a.*, dump(id), dump(message) from tbl_test a;

        ID MESSAGE
---------- --------------------------------
DUMP(ID)
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
DUMP(MESSAGE)
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
         1 Hello
Typ=2 Len=2: 193,2
Typ=1 Len=5: 72,101,108,108,111

         2 World
Typ=2 Len=2: 193,3
Typ=1 Len=5: 87,111,114,108,100

         3 data
            analytics
Typ=2 Len=2: 193,4
Typ=1 Len=16: 100,97,116,97,32,10,32,97,110,97,108,121,116,105,99,115

To remove the new line, we use replace() function as shown below. Now the display looks much better.

SQL> select id, replace(message, chr(10), '') from tbl_test a;

        ID REPLACE(MESSAGE,CHR(10),'')
---------- ----------------------------------------------------------------
         1 Hello
         2 World
         3 data  analytics

In addition to dump(), we can also use function utl_raw.CAST_TO_RAW() as described in post Watch out invisible characters.