The DUMP function has the following syntax,
DUMP(expr[,return_fmt[,start_position[,length]]])
Where,
1)expr contains any expresion like column name or any strings.
2)return_fmt may be any of 8,10,16,17 characters.If 8 is given then character code point returned as octal representation.
- If 10 is given then character code point returned as decimal representation. It is by default option.
- If 16 is given then character code point returned as hexadecimal representation.
- If 17 is given then character code point returned as single characters.
4)length indicates the length of output to be shown.
- The return value of DUMP function is VARCHAR2 datatype.
- By default, the return value contains no character set information. To retrieve the character set name of expr, add 1000 to any of the preceding format values.
- For example, to get characterset information along with code point in hexadecimal representation pass 1016 as parameter in the expr of the DUMP function.
- To get characterset information along with code point in octal representation pass 1008 as parameter in the expr of the DUMP function.
In the output of DUMP function,
- Typ: Indicates the datatype code. For example , for VARCHAR2 datatype the corresponding code is 1, for NUMBER datatype the corresponding code is 2 etc. In the below example Typ 96 is means CHAR datatype. Details of corresponding code along with oracle datatype is discussed on oracle datatype internal code
- Len: Indicates that the total size of the data in the row in bytes. Len=5 means total 5 bytes data in the row.
- After Len the internal representation of the data is shown of the corresponding row. In fact it is called database code point and this returned result is always in the database character set.
Below is an example:
SELECT DUMP(col1, 10)
FROM tab1
WHERE cond1 = val1;
DUMP(COL1)
-------------------------------------
Typ=96 Len=4: 65,66,67,32
For this example, type=96 is indicating a CHAR column. The last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.
A bit detail example is below.
1)To get the dump information of 'Momin Arju' word and code point in decimal representation without any characterset information issue,
SQL> select dump('Momin Arju') from dual;
DUMP('MOMINARJU')
---------------------------------------------------
Typ=96 Len=10: 77,111,109,105,110,32,65,114,106,117
2)To get information such as 1. Here we add extra one parameter to select explicitly the code point as decimal representation.
SQL> select dump('Momin Arju',10) from dual;
DUMP('MOMINARJU',10)
---------------------------------------------------
Typ=96 Len=10: 77,111,109,105,110,32,65,114,106,117
3)To get information in decimal starting from byte 4 and then to 5 bytes next issue,
SQL> select dump('Momin Arju',10,4,5) from dual;
DUMP('MOMINARJU',10,4,5)
--------------------------------
Typ=96 Len=10: 105,110,32,65,114
4)To get information along with characterset and code point in decimal issue,
SQL> select dump('Momin Arju',1010,4,5) from dual;
DUMP('MOMINARJU',1010,4,5)
------------------------------------------------------
Typ=96 Len=10 CharacterSet=AL32UTF8: 105,110,32,65,114
5)To get information code point in hexadecimal along with character set
information issue,
SQL> select dump('Momin Arju',1016,4,5) from dual;
DUMP('MOMINARJU',1016,4,5)
---------------------------------------------------
Typ=96 Len=10 CharacterSet=AL32UTF8: 69,6e,20,41,72
6) To represent code point as octal issue,
SQL> select dump('Momin Arju',1008,4,5) from dual;
DUMP('MOMINARJU',1008,4,5)
-------------------------------------------------------
Typ=96 Len=10 CharacterSet=AL32UTF8: 151,156,40,101,162
7) To get information as single characters without any characterset information
issue,
SQL> select dump('Momin Arju',17,4,5) from dual;
DUMP('MOMINARJU',17,4,5)
------------------------
Typ=96 Len=10: i,n, ,A,rRelated DocumentsHow does one escape special characters when writing SQL queries?
No comments:
Post a Comment