error ORA-06512 while running query in script


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting error ORA-06512 while running query in script
# 1  
error ORA-06512 while running query in script

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?
# 2  
give a EXIT before the EOF line
# 3  
i tried with EXIT but still facing the same proble.
# 4  
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');
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #584
Difficulty: Easy
Binary files generally requires less space and are more efficient to process.
True or False?

10 More Discussions You Might Find Interesting

1. Programming

Oracle simple SQL query result in: ORA-08103: object no longer exists

Dear community, please help with a query on Oracle. I'm using SQLPlus (but with SQLDeveloper is the same) to accamplish a sinple query like: select count(*) from ARCHIT_D_TB where (TYP_ID=22 OR TYP_ID=23) and SUB_TM like '%SEP%' and CONS=1234This is a very simple query that works perfect until... (5 Replies)
Discussion started by: Lord Spectre
5 Replies

2. Shell Programming and Scripting

Regarding a query on making changes to a running script

Hello All, Greetings !! I have a query here to all is as follows: Question: Let's say we are running a script in a UNIX box and we have opened an another session and then made changes in script of some statements NOT to be print some values(just an example) so when I am monitoring the... (5 Replies)
Discussion started by: RavinderSingh13
5 Replies

3. UNIX for Dummies Questions & Answers

Extract only the data from ksh script running netezza query

Hi I searched this forum before posting the question, but couldnt find it, the issue i'm facing is, i'm trying to select a column from a netezza table from a korn shell script, but the query runs var=$(nzodbcsql -q "select MAX(millcount) from table1";) echo $var it returns the value like... (10 Replies)
Discussion started by: maximus_jack
10 Replies

4. Shell Programming and Scripting

Error in running DB query by script

Hi I was trying to fetch data from database. But the number of rows exported were huge so i got the error. Experts please advice. Thanks a lot for your supprt. #: ./script.sh ./script.sh: xmalloc: subst.c:3076: cannot allocate 1401346369 bytes (0 bytes allocated) (2 Replies)
Discussion started by: brij123
2 Replies

5. Shell Programming and Scripting

Running sed from a script query

Hello! I'm trying to run this code to print the body of an html document (all text in between <body> and </body>) from a script but am unsure how to call it from the command line interface. /<body>/,/<\/body>/ 1s/.*<body>// $s/<\/body>.*//p I have tried to call it using this: sed... (6 Replies)
Discussion started by: bgnersoon2be#1
6 Replies

6. HP-UX

ORA-06512 Error in Oracle 10g

HI All , I am using oracle 10g and HP AIX . I am getting the below error while running the package. ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 478 ORA-06512: at "DW_APP.PKG_ASS_FVC", line 595 ORA-06512: at line 2 ... (4 Replies)
Discussion started by: Perlbaby
4 Replies

7. UNIX for Advanced & Expert Users

grep all ORA errors except one ORA error

Hi - I am trying to grep all "ORA" errors in a log files.I have to grep all ORA errors except one error for example ORA-01653.How can exclude that error in "grep" command? In following "grep" command I want to exclude "ORA-01653" error grep -i ORA alert.log >>/tmp/ora_errors.txt ... (7 Replies)
Discussion started by: Mansoor8810
7 Replies

8. Solaris

maxuprc and maxusers - ORA-27300, ORA-27301, ORA-27302

Hi all, Am intermittently getting the following errors on one of my databases. Errors in file /oracle/HRD/saptrace/background/hrd_psp0_13943.trc: ORA-27300: OS system dependent operation:fork failed with status: 12 ORA-27301: OS failure message: Not enough space ORA-27302:... (1 Reply)
Discussion started by: newbie_01
1 Replies

9. AIX

Query on running script with nohup

Hi, I'm trying to run database restore script with the nohup command as it will run for long hours since if I run it normally, the putty session will become inactive and the restore gets terminated. The command I use is nohup db2 -tvf FBR_NODE0000.scr -z FBR_NODE0000.log & But the problem is... (2 Replies)
Discussion started by: vkcool.17
2 Replies

10. UNIX for Dummies Questions & Answers

Shell Script --- Delete ORA error from data file

Hi All, I have to make the following changes in shell script: When the data file is being created, an “ORA” error occurs in it. I have to remove this ORA error from data file and put it in log file. I am “ –n grip command” to find the ORA error and get the line number, How do I delete it... (1 Reply)
Discussion started by: sk005
1 Replies

Featured Tech Videos