使用DS開發的時候,有的時候會遇到一個問題:數據庫層面定義的空格與DS自已定義的空格概念不一致,導致生成的數據會有一定的問題.

　　舉例來說:

　　在數據庫里面定義CHAR(20),如果插入的字符不足20的時候,會自動補空格(ascii為0x20);在DS里面的話,源數據如果不滿足20的話,那么DS也會自動補全,此時補的值為ascii 0x00。那么，這條記錄在數據庫里面的話，表面上是看不出異常的，但是加條件查詢的時候，是查不出來結果集的。此時，就要用到dump來判斷是否有此類情況出現。

????? oracle的dump函數就不介紹了，需要用到的時候，請google即可。

The argument return_fmt specifies the format of the return value and can have any of the following values:

• 8 returns result in octal notation.

• 10 returns result in decimal notation.

• 16 returns result in hexadecimal notation.

• 17 returns each byte printed as a character if and only if it can be interpreted as a printable character in the character set of the compiler—typically ASCII or EBCDIC. Some ASCII control characters may be printed in the form ^X as well. Otherwise the character is printed in hexidecimal notation. All NLS parameters are ignored. Do not depend on any particular output format for DUMP with return_fmt 17.

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, a return_fmt of 1008 returns the result in octal and provides the character set name of expr .

The arguments start_position and length combine to determine which portion of the internal representation to return. The default is to return the entire internal representation in decimal notation.

If expr is null, then this function returns NULL .

select dump(test_col) from test_tb;

Typ=96 Len=15: 54,52,54,53,51,49,57,32,32,32,32,32,32,0,0

typ表示當前的expr值的類型。如：2表示NUMBER，96表示CHAR。 然后長度就是15位，最后二位就是ascii 0.

那么，這個時候查詢這條記錄是沒辦法查詢出來的。

select * from test_tb where trim(replace(test_col,chr(0),''))='6465319'? --按此查詢即可得出結果。

?

PS：一般遇到這種情況，最好的辦法是在DS的定義里面把char改成varchar,讓DB來做填充空格的操作。

?

oracle dump的使用心得