Shell Programming and Scripting

1 #!/bin/ksh
2 ################################################################
3 # Written by Johnson 12/03/2008
4 # Version 1.0
5 # This script executes some SQL to provide Spike Check Report to TNS team.
6 #################################################################
7
8 . $HOME/conf/systemProperties/EnvSetup.properties
9
10 # SQL Block Start to create temp table from Registers Reads table.
11
12
13 sqlplus -s pipe/da!!a5p@ednamip.world << EOF
14 whenever sqlerror exit 9; --Arbitrary Return Code
15
16 set serveroutput on;
17
18 declare
19 TABLE_CNT1 NUMBER;
20 TABLE_CNT2 NUMBER;
21 TABLE_CNT3 NUMBER;
22 begin
23
24 SELECT COUNT(TABLE_NAME) INTO TABLE_CNT1 FROM user_tables WHERE table_name = 'SPIKECHECK_R1';
25 IF TABLE_CNT1 = 1 THEN
26 EXECUTE IMMEDIATE ('drop table spikecheck_r1');
27 ELSE
28 NULL;
29 END IF;
30
31 SELECT COUNT(TABLE_NAME) INTO TABLE_CNT2 FROM user_tables WHERE table_name = 'SPIKECHECK_R2';
32 IF TABLE_CNT2 = 1 THEN
33 EXECUTE IMMEDIATE ('drop table spikecheck_r2');
34 ELSE
35 NULL;
36 END IF;
37
38 SELECT COUNT(TABLE_NAME) INTO TABLE_CNT3 FROM user_tables WHERE table_name = 'SPIKECHECK_READS';
39 IF TABLE_CNT3 = 1 THEN
40 EXECUTE IMMEDIATE ('drop table spikecheck_reads');
41 ELSE
42 NULL;
43 END IF;
44
45 DBMS_OUTPUT.PUT_LINE(' Table Dropped ');
46
47 EXECUTE IMMEDIATE ('create table SPIKECHECK_R1 as
48 select r1.channel_id,r1.local_read_time,r1.source,r1.source_detail,r1.cum_read from
49 register_reads@EDNMUDP.WORLD r1 where
50 r1.source_detail = '||''''||'AMR'||''''||' and
51 r1.source = '||''''||'TNS'||''''||' and
52 (r1.utc_read_time > trunc(sysdate-2) and r1.utc_read_time < trunc(sysdate-1))');
53
54 EXECUTE IMMEDIATE ('create table SPIKECHECK_R2 as
55 select r2.channel_id,r2.local_read_time,r2.source,r2.source_detail,r2.cum_read from
56 register_reads@EDNMUDP.WORLD r2 where
57 r2.source_detail = '||''''||'AMR'||''''||' and
58 r2.source = '||''''||'TNS'||''''||' and
59 (r2.utc_read_time > trunc(sysdate-1) and r2.utc_read_time < trunc(sysdate))');
60
61
62
63 EXECUTE IMMEDIATE ('create table SPIKECHECK_READS as
64 select
65 r2.source ReadSrc,
66 r2.channel_id ChRef,
67 r1.cum_read Read1_CumRead,
68 r1.local_read_time Read1_ReadTime,
69 r2.cum_read Read2_CumRead,
70 r2.local_read_time Read2_ReadTime,
71 r2.cum_read - r1.cum_read Read2_Read1
72 from
73 SPIKECHECK_R1 r1,
74 SPIKECHECK_R2 r2
75 where
76 r1.channel_id = r2.channel_id and
77 (r2.cum_read - r1.cum_read) > 1000 and
78 r1.cum_read > '||''''||'1000'||''''||' and
79 r2.cum_read >'||''''||'0'||''''||')');
80
81 DBMS_OUTPUT.PUT_LINE(' Table Created Successfully ');
82
83
84 END;
85 /
86 EOF
87
88 echo "Done"
89
90 # SQL Block End to create temp table from Registers Reads table.





when i run this script i am getting below error,

Table Dropped
declare
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 46

Usage: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |
<variable> | :<bindvariable> ] [ COMMIT | ROLLBACK ]
Done


please let me know what is this error?

give a EXIT before the EOF line

i tried with EXIT but still facing the same proble.

Rewrite below lines
where
76 r1.channel_id = r2.channel_id and
77 (r2.cum_read - r1.cum_read) > 1000 and
78 r1.cum_read > '||''''||'1000'||''''||' and
79 r2.cum_read >'||''''||'0'||''''||'

as

where
76 r1.channel_id = r2.channel_id and
77 (r2.cum_read - r1.cum_read) > 1000 and
78 r1.cum_read > 1000 and
79 r2.cum_read > 0');