set sqlblanklines on

admin

有时候从网页粘过来得sql代码直接在sqlplus里执行时报错：

1. 
   
2. SQL> set sqlblanklines off   
   
3. SQL> select thread#,to_char (first_time, 'YYYY-MM-DD') day,
   
4.   2  
   
5. SQL>          sum (decode (to_char (first_time, 'HH24'), '00', 1, 0)) H00,
   
6. SP2-0734: unknown command beginning "sum (decod..." - rest of line ignored.
   
7. SQL>
   
8. SQL>          sum (decode (to_char (first_time, 'HH24'), '01', 1, 0)) H01,
   
9. SP2-0734: unknown command beginning "sum (decod..." - rest of line ignored.
   
10. SQL>
    
11. SQL>          sum (decode (to_char (first_time, 'HH24'), '02', 1, 0)) H02,
    
12. SP2-0734: unknown command beginning "sum (decod..." - rest of line ignored.
    
13. SQL>
    
14. SQL>          sum (decode (to_char (first_time, 'HH24'), '03', 1, 0)) H03,
    
15. SP2-0734: unknown command beginning "sum (decod..." - rest of line ignored.
    
16. SP2-0044: For a list of known commands enter HELP
    
17. and to leave enter EXIT.

复制代码

这是因为有空行，所以要做如下操作

1. set sqlblanklines

复制代码

1. SQL>  set sqlblanklines on
   
2. SQL> select thread#,to_char (first_time, 'YYYY-MM-DD') day,
   
3.   2  
   
4.   3           sum (decode (to_char (first_time, 'HH24'), '00', 1, 0)) H00,
   
5.   4  
   
6.   5           sum (decode (to_char (first_time, 'HH24'), '01', 1, 0)) H01,
   
7.   6  
   
8.   7           sum (decode (to_char (first_time, 'HH24'), '02', 1, 0)) H02,
   
9.   8  
   
10.   9           sum (decode (to_char (first_time, 'HH24'), '03', 1, 0)) H03,
    
11. 10  
    
12. 11           sum (decode (to_char (first_time, 'HH24'), '04', 1, 0)) H04,
    
13. 12  
    
14. 13           sum (decode (to_char (first_time, 'HH24'), '05', 1, 0)) H05,
    
15. 14  
    
16. 15           sum (decode (to_char (first_time, 'HH24'), '06', 1, 0)) H06,
    
17. 16  
    
18. 17           sum (decode (to_char (first_time, 'HH24'), '07', 1, 0)) H07,
    
19. 18  
    
20. 19           sum (decode (to_char (first_time, 'HH24'), '08', 1, 0)) H08,
    
21. 20  
    
22. 21           sum (decode (to_char (first_time, 'HH24'), '09', 1, 0)) H09,
    
23. 22  
    
24. 23           sum (decode (to_char (first_time, 'HH24'), '10', 1, 0)) H10,
    
25. 24  
    
26. 25           sum (decode (to_char (first_time, 'HH24'), '11', 1, 0)) H11,
    
27. 26  
    
28. 27           sum (decode (to_char (first_time, 'HH24'), '12', 1, 0)) H12,
    
29. 28  
    
30. 29           sum (decode (to_char (first_time, 'HH24'), '13', 1, 0)) H13,
    
31. 30  
    
32. 31           sum (decode (to_char (first_time, 'HH24'), '14', 1, 0)) H14,
    
33. 32  
    
34. 33           sum (decode (to_char (first_time, 'HH24'), '15', 1, 0)) H15,
    
35. 34  
    
36. 35           sum (decode (to_char (first_time, 'HH24'), '16', 1, 0)) H16,
    
37. 36  
    
38. 37           sum (decode (to_char (first_time, 'HH24'), '17', 1, 0)) H17,
    
39. 38  
    
40. 39           sum (decode (to_char (first_time, 'HH24'), '18', 1, 0)) H18,
    
41. 40  
    
42. 41           sum (decode (to_char (first_time, 'HH24'), '19', 1, 0)) H19,
    
43. 42  
    
44. 43           sum (decode (to_char (first_time, 'HH24'), '20', 1, 0)) H20,
    
45. 44  
    
46. 45           sum (decode (to_char (first_time, 'HH24'), '21', 1, 0)) H21,
    
47. 46  
    
48. 47           sum (decode (to_char (first_time, 'HH24'), '22', 1, 0)) H22,
    
49. 48  
    
50. 49           sum (decode (to_char (first_time, 'HH24'), '23', 1, 0)) H23,
    
51. 50  
    
52. 51           count (*) total
    
53. 52  
    
54. 53      from v$log_history a
    
55. 54  
    
56. 55     where first_time >= sysdate - 30
    
57. 56  
    
58. 57  group by thread#,to_char (first_time, 'YYYY-MM-DD')
    
59. 58  
    
60. 59  order by thread#,to_char (first_time, 'YYYY-MM-DD');
    
61. 
    
62.    THREAD# DAY               H00        H01        H02        H03        H04
    
63. ---------- ---------- ---------- ---------- ---------- ---------- ----------
    
64.        H05        H06        H07        H08        H09        H10        H11
    
65. ---------- ---------- ---------- ---------- ---------- ---------- ----------
    
66.        H12        H13        H14        H15        H16        H17        H18
    
67. ---------- ---------- ---------- ---------- ---------- ---------- ----------
    
68.        H19        H20        H21        H22        H23      TOTAL
    
69. ---------- ---------- ---------- ---------- ---------- ----------
    
70.          1 2020-10-30          0          0          0          0          0
    
71.          0          0          0          0          0          0          0
    
72.          0          0          0          0          5          7          0
    
73.          0          0          0          0          0         12

复制代码