一个简单的循环脚本问题

chunchun2012

测试环境为Redhat 4 U4,oracle 10.2.0.1
在/home/oracle下写了一个简单的脚本check.sh，内容如下
select to_char(sysdate,'HH24:MI:SS') now from dual;
select CPHBT from X$KCCCP where CPHBT>0;
!md5sum /u0/oracle/oradata/TESTwis/control01.ctl;
在sqlplus下运行正常
SQL> @check.sh

NOW
--------
16:19:42


     CPHBT
----------
809090683

ac99dc73241f11ad92cb415c61a9bb59  /u0/oracle/oradata/TESTwis/control01.ctl

为了多次运行这一脚本，想将其修改为循环结构，于是修改check.sh内容如下
declare
i number;
begin
for i in 1..20 loop
select to_char(sysdate,'HH24:MI:SS') now from dual;
select CPHBT from X$KCCCP where CPHBT>0;
!md5sum /u0/oracle/oradata/TESTwis/control01.ctl;
end loop;
end;
/

运行该脚本后出错，内容如下
SQL> @check.sh
!md5sum /u0/oracle/oradata/TESTwis/control01.ctl;
*
ERROR at line 7:
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "!" when expecting one of the following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
The symbol "case was inserted before "!" to continue.
ORA-06550: line 7, column 49:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( * @ % & = - + < / > at in is mod remainder not rem when
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member
貌似循环结构下识别不了操作系统的命令？
那要想多次自动运行上述三个命令，请教一下，该如何操作？

Minsic

“！”是sqlplus的命令，不是PL/SQL的命令，所以报错，用shell来实现循环就好了

Stone

貌似循环结构下识别不了操作系统的命令？
那要想多次自动运行上述三个命令，请教一下，该如何操作？

应该是在PL/SQL里面无法识别，所以报错。
用shell脚本应该是比较好的啦，简单写了下，可以参考下 :)

1. 
   
2. [oracle@stonedb stone]$ cat chk.sh
   
3. #!/bin/bash
   
4. 
   
5. for (( i=1; i<=10; i++ ))
   
6. do
   
7. echo "=== 第 $i 次检查 ==="
   
8. sqlplus -s '/ as sysdba' << EOL
   
9. select to_char(sysdate,'HH24:MI:SS') now from dual;
   
10. select CPHBT from X\$KCCCP where CPHBT>0;
    
11. host md5sum /u01/app/oracle/oradata/STONE11G/control01.ctl
    
12. EOL
    
13. done
    
14. 
    

复制代码

输出测试如下，环境Redhat Linux 5.4：

1. [oracle@stonedb stone]$ ./chk.sh
   
2. === 第 1 次检查 ===
   
3. 
   
4. NOW
   
5. --------
   
6. 23:05:13
   
7. 
   
8. 
   
9.      CPHBT
   
10. ----------
    
11. 800963123
    
12. 
    
13. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl
    
14. 
    
15. === 第 2 次检查 ===
    
16. 
    
17. NOW
    
18. --------
    
19. 23:05:13
    
20. 
    
21. 
    
22.      CPHBT
    
23. ----------
    
24. 800963123
    
25. 
    
26. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl
    
27. 
    
28. === 第 3 次检查 ===
    
29. 
    
30. NOW
    
31. --------
    
32. 23:05:13
    
33. 
    
34. 
    
35.      CPHBT
    
36. ----------
    
37. 800963123
    
38. 
    
39. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl
    
40. 
    
41. === 第 4 次检查 ===
    
42. 
    
43. NOW
    
44. --------
    
45. 23:05:13
    
46. 
    
47. 
    
48.      CPHBT
    
49. ----------
    
50. 800963123
    
51. 
    
52. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl
    
53. 
    
54. === 第 5 次检查 ===
    
55. 
    
56. NOW
    
57. --------
    
58. 23:05:14
    
59. 
    
60. 
    
61.      CPHBT
    
62. ----------
    
63. 800963123
    
64. 
    
65. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl
    
66. 
    
67. === 第 6 次检查 ===
    
68. 
    
69. NOW
    
70. --------
    
71. 23:05:14
    
72. 
    
73. 
    
74.      CPHBT
    
75. ----------
    
76. 800963123
    
77. 
    
78. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl
    
79. 
    
80. === 第 7 次检查 ===
    
81. 
    
82. NOW
    
83. --------
    
84. 23:05:14
    
85. 
    
86. 
    
87.      CPHBT
    
88. ----------
    
89. 800963123
    
90. 
    
91. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl
    
92. 
    
93. === 第 8 次检查 ===
    
94. 
    
95. NOW
    
96. --------
    
97. 23:05:14
    
98. 
    
99. 
    
100.      CPHBT
     
101. ----------
     
102. 800963123
     
103. 
     
104. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl
     
105. 
     
106. === 第 9 次检查 ===
     
107. 
     
108. NOW
     
109. --------
     
110. 23:05:14
     
111. 
     
112. 
     
113.      CPHBT
     
114. ----------
     
115. 800963123
     
116. 
     
117. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl
     
118. 
     
119. === 第 10 次检查 ===
     
120. 
     
121. NOW
     
122. --------
     
123. 23:05:15
     
124. 
     
125. 
     
126.      CPHBT
     
127. ----------
     
128. 800963123
     
129. 
     
130. a7463eccdf1dcb50af37968f6734003e  /u01/app/oracle/oradata/STONE11G/control01.ctl

复制代码

chunchun2012

非常感谢！学习了！