今天修改数据库时,有较长字符串无法直接复制到某一个字段,开始以为是字符串太大了,但是长度距离VARCHAR2的上限还有很多,只有1700多个字符。检查这个字符按串和对应的错误。发现很怪异,是ORA-01480错误。
看看这个错误:
01480, 00000, "trailing null missing from STR bind value"// *Cause: A bind variable of type 5 (null-terminated string) does// not contain the terminating null in its buffer.// *Action: Terminate the string with a null character
原来字符串中包含&这个特殊字符,所以我们需要“处理”这个特殊的字符。可将CHR(38)可以将这个&特殊字符串转换。通过这种转换,含有多个这样特殊字符的字符串可以成功插入数据库.
那么,什么是CHR()呢?以前没有用过这个函数,故不解其意,现在遇着了就是缘分,好好学习学习,原来chr()函数的作用是将ascii码转换行字符(之所以函数名叫chr,我猜是因为当初起名字时感觉最合适的还是char,但是它是关键字呀,所以起个和它最相近的名字呗,那就去掉个“a”吧!),与些相反的有一个叫ascii()函数(这个函数很好记,a---s---k---码子嘛),ascii()的作用是将字符转换成ascii码。这两个函数正好起到一个相反的操作的。
chr()函数示例:SQL> select chr(65) from dual;
CHR(65)
-------
A
accii()函数示例:SQL> select ascii('A') from dual;
ASCII('A')
----------
而我今天碰到的chr(38)转换成的字符是“&”,又多学习了一对oracle函数,MARK一下,呵呵。
那“&”到底为什么在Oracle中成了特殊字符呢?经过查找,终于揭晓了答案:原来&这个字符在oracle的sql语句或存储过程中用来指定其后跟的是执行时要你输入的变量。如:select * from &AAA; 则执行此语句时,系统会提示你给赋值。或者比如:insert into test(col1,col2,col3,type) values(0,0,0,'&jfd') ,在PL/SQL Developer的SQL Window 中执行这条语句时,会弹出一个Variables提示框,要求你输入变量'jfd'值。而如果执行:insert into test(col1,col2,col3,type) values(0,0,0,chr(38)||'jfd') ,它就会乖乖的向表中插入这条数据。这下,所有疑虑就都云开雾散廖!~
学后有感:其实Oracle里的这个chr()函数,很想JavaScript或者Java高级程序语言中处理特殊字符的转意字符“/”,最起码,当初设计者们的用意和对他们的功能的要求是一样的,就是为了处理这些特殊的函数,因为机器是死的,对于像&var_name这样的字符串,它没有办法理性机灵的分辨出它的主人是想插入这么个字符串,还是想作为一个字符串的占位符。
其实,“&”之外,数据库中还有其他比较特殊的字符,比方说',%等,对于这些都可以用上述的chr()函数来处理,即chr(ascii)||(后便的字符),但实际上对于这些特殊字符的处理方法并非只有这一种,经查找,结果如下:
方法一:在要插入的SQL语句前加上Set define off;与原SQL语句一起批量执行
我们在SQL*PLUS下执行 SQL> show define;命令时,显示下列结果:
define "&" (hex 26)
这个是Oracle里面用来识别自定义变量的设置,现在我们在SQL*PLUS下将其关闭:
SQL> set define OFF;
然后再在SQL*PLUS执行导入脚本,OK!问题搞定。
执行完毕之后,重新设置set define ON。
·方法二:在SQL语句中将'&'替换成chr(38),因为chr(38)是‘&’的ASCII码
SQL> Select 'Tom' || chr(38) || 'Jerry' from dual;
·方法三:分拆原来的字符串
SQL> Select 'Tom' || '&' || 'Jerry' from dual;
我们可以看到,方法一最为简便,而且效率也最高。方法二因为有一个调用函数的过程,所以性能稍差。方法三需要两次连接字符串,效率最差!其中,方法二,三大同小异,都是对特殊字符单独进行处理(加'或者chr(ascii))后,再用||连接其他字符。
下面是常见字符与ascii对照表,以供参考:
第一部分由 00H 到 1FH 共 32 个,一般用来通讯或作为控制之用,有些字符可显示于屏幕,有些则无法显示在屏幕上,但能看到其效果(例如换行字符、归位字符)。
第二部分是由 20H 到 7FH 共 96 个,这 95 个字符是用来表示阿拉伯数字、英文字母大小写和底线、括号等符号,都可以显示在屏幕上。如下表:
ASCII 码 | 字符 | | ASCII 码 | 字符 | | ASCII 码 | 字符 | | ASCII 码 | 字符 |
十进位 | 十六进位 | | 十进位 | 十六进位 | | 十进位 | 十六进位 | | 十进位 | 十六进位 |
032 | 20 | | | 056 | 38 | 8 | | 080 | 50 | P | | 104 | 68 | h |
033 | 21 | ! | | 057 | 39 | 9 | | 081 | 51 | Q | | 105 | 69 | i |
034 | 22 | " | | 058 | 3A | : | | 082 | 52 | R | | 106 | 6A | j |
035 | 23 | # | | 059 | 3B | ; | | 083 | 53 | S | | 107 | 6B | k |
036 | 24 | $ | | 060 | 3C | < | | 084 | 54 | T | | 108 | 6C | l |
037 | 25 | % | | 061 | 3D | = | | 085 | 55 | U | | 109 | 6D | m |
038 | 26 | & | | 062 | 3E | > | | 086 | 56 | V | | 110 | 6E | n |
039 | 27 | ' | | 063 | 3F | ? | | 087 | 57 | W | | 111 | 6F | o |
040 | 28 | ( | | 064 | 40 | @ | | 088 | 58 | X | | 112 | 70 | p |
041 | 29 | ) | | 065 | 41 | A | | 089 | 59 | Y | | 113 | 71 | q |
042 | 2A | * | | 066 | 42 | B | | 090 | 5A | Z | | 114 | 72 | r |
043 | 2B | + | | 067 | 43 | C | | 091 | 5B | [ | | 115 | 73 | s |
044 | 2C | , | | 068 | 44 | D | | 092 | 5C | \ | | 116 | 74 | t |
045 | 2D | - | | 069 | 45 | E | | 093 | 5D | ] | | 117 | 75 | u |
046 | 2E | . | | 070 | 46 | F | | 094 | 5E | ^ | | 118 | 76 | v |
047 | 2F | / | | 071 | 47 | G | | 095 | 5F | _ | | 119 | 77 | w |
048 | 30 | 0 | | 072 | 48 | H | | 096 | 60 | ` | | 120 | 78 | x |
049 | 31 | 1 | | 073 | 49 | I | | 097 | 61 | a | | 121 | 79 | y |
050 | 32 | 2 | | 074 | 4A | J | | 098 | 62 | b | | 122 | 7A | z |
051 | 33 | 3 | | 075 | 4B | K | | 099 | 63 | c | | 123 | 7B | { |
052 | 34 | 4 | | 076 | 4C | L | | 100 | 64 | d | | 124 | 7C | | |
053 | 35 | 5 | | 077 | 4D | M | | 101 | 65 | e | | 125 | 7D | } |
054 | 36 | 6 | | 078 | 4E | N | | 102 | 66 | f | | 126 | 7E | ~ |
055 | 37 | 7 | | 079 | 4F | O | | 103 | 67 | g | | 127 | 7F | |
第三部分由 80H 到 0FFH 共 128 个字符,一般称为『扩充字符』,这 128 个扩充字符是由 IBM 制定的,并非标准的 ASCII 码。这些字符是用来表示框线、音标和其它欧洲非英语系的字母。
ESC键 | VK_ESCAPE (27) | 回车键 | VK_RETURN (13) | TAB键 | VK_TAB (9) |
Caps Lock键 | VK_CAPITAL (20) | Shift键 | VK_SHIFT () | Ctrl键 | VK_CONTROL (17) |
Alt键 | VK_MENU (18) | 空格键 | VK_SPACE (/32) | 退格键 | VK_BACK (8) |
左徽标键 | VK_LWIN (91) | 右徽标键 | VK_LWIN (92) | 鼠标右键快捷键 | VK_APPS (93) |
Insert键 | VK_INSERT (45) | Home键 | VK_HOME (36) | Page Up | VK_PRIOR (33) |
PageDown | VK_NEXT (34) | End键 | VK_END (35) | Delete键 | VK_DELETE (46) |
方向键(←) | VK_LEFT (37) | 方向键(↑) | VK_UP (38) | 方向键(→) | VK_RIGHT (39) |
方向键(↓) | VK_DOWN (40) | F1键 | VK_F1 (112) | F2键 | VK_F2 (113) |
F3键 | VK_F3 (114) | F4键 | VK_F4 (115) | F5键 | VK_F5 (116) |
F6键 | VK_F6 (117) | F7键 | VK_F7 (118) | F8键 | VK_F8 (119) |
F9键 | VK_F9 (120) | F10键 | VK_F10 (121) | F11键 | VK_F11 (122) |
F12键 | VK_F12 (123) | Num Lock键 | VK_NUMLOCK (144) | 小键盘0 | VK_NUMPAD0 (96) |
小键盘1 | VK_NUMPAD0 (97) | 小键盘2 | VK_NUMPAD0 (98) | 小键盘3 | VK_NUMPAD0 (99) |
小键盘4 | VK_NUMPAD0 (100) | 小键盘5 | VK_NUMPAD0 (101) | 小键盘6 | VK_NUMPAD0 (102) |
小键盘7 | VK_NUMPAD0 (103) | 小键盘8 | VK_NUMPAD0 (104) | 小键盘9 | VK_NUMPAD0 (105) |
小键盘. | VK_DECIMAL (110) | 小键盘* | VK_MULTIPLY (106) | 小键盘+ | VK_MULTIPLY (107) |
小键盘- | VK_SUBTRACT (109) | 小键盘/ | VK_DIVIDE (111) | Pause Break键 | VK_PAUSE (19) |
Scroll Lock键 | VK_SCROLL (145) | | | |