经常执行脚本的朋友可能遇到这样的问题,有个脚本如下:
INSERT INTO b
SELECT * FROM dba_objects WHERE ROWNUM<10;
注意2行之间有行空行,这样的sql放在sql窗口执行是没问题的,但是放在命令窗口执行就会有问题
SQL> @C:\Users\Administrator\Desktop\1.sql
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
SYS ICOL$ 20 2 TABLE 2005/6/30 1 2005/6/30 19: 2005-06-30:19:10:16 VALID N N N
SYS I_USER1 44 44 INDEX 2005/6/30 1 2005/6/30 19: 2005-06-30:19:10:16 VALID N N N
SYS CON$ 28 28 TABLE 2005/6/30 1 2005/6/30 19: 2005-06-30:19:10:16 VALID N N N
SYS UNDO$ 15 15 TABLE 2005/6/30 1 2005/6/30 19: 2005-06-30:19:10:16 VALID N N N
SYS C_COBJ# 29 29 CLUSTER 2005/6/30 1 2005/6/30 19: 2005-06-30:19:10:16 VALID N N N
SYS I_OBJ# 3 3 INDEX 2005/6/30 1 2005/6/30 19: 2005-06-30:19:10:16 VALID N N N
SYS PROXY_ROLE_DATA$ 25 25 TABLE 2005/6/30 1 2005/6/30 19: 2005-06-30:19:10:16 VALID N N N
SYS I_IND1 39 39 INDEX 2005/6/30 1 2005/6/30 19: 2005-06-30:19:10:16 VALID N N N
SYS I_CDEF2 51 51 INDEX 2005/6/30 1 2005/6/30 19: 2005-06-30:19:10:16 VALID N N N
9 rows selected
结果很搞笑,本来一个insert语句,结果被搞成select语句,就是因为中间有行空行,由此可以知道用命令行执行sql时,遇到空行时
就默认认为上面的sql执行完毕了。没办法只能将中间的空行的去掉,这个例子还好,一共才2行,如果有成千上万行的话,那手动删岂不郁闷死,还好有办法:
SQL> set sqlblanklines on;
SQL> @C:\Users\Administrator\Desktop\1.sql
9 rows inserted