Hi,
can anyone please help me on this.
I dont understand as to why am getting this error..

Please, please help on this!

fortesting purpose I have commented certain lines..

"Pre_s2a_leadtime.sh" 55 lines, 1233 characters
#!/bin/csh

# Check if program is already running and echo a time stamp for the log file
#log_start_chk $0 $$
#if ( $status == 0 ) then
#exit
#endif

#source run_env

set L2S_RUN_LOG_FILE="Pre_s2a_leadtime.log"

echo -n "$0 started" >> ${L2S_RUN_LOG_FILE}
date '+%m%d%y %H:%M:%S' >>  ${L2S_RUN_LOG_FILE}


#setenv DB_NAME viewer/viewer@ltstprod


FLAG=`sqlplus -silent viewer/viewer@ltstprod << SQLEND >> ${L2S_RUN_LOG_FILE}
set pagesize 0 feedback off verify off heading off echo off
select UNCONSTRAINED_PIECE_PART from ICMPINPUTCONTROL;
exit;
SQLEND`

if [ $FLAG ='Y']
then

sqlplus viewer/viewer@ltstprod << sqlend >> ${L2S_RUN_LOG_FILE}
# Running the following queries before the actual L2S program

drop table cycle_time_bkp;

create table cycle_time_bkp as select * from cycle_time;

update cycle_time a set cycle_time_days = 0
where exists(select 1 from route b where a.id = b.id and a.route = b.route and b.flow_id = 7);

quit
"Pre_s2a_leadtime.sh" 55 lines, 1256 characters

Line 21 of your original post is missing a `:

FLAG=`sqlplus -silent viewer/viewer@ltstprod << SQLEND >> ${L2S_RUN_LOG_FILE}

Hi sknake,
thanks a lot for hte quick response.

I already have ` symbol at he end of second SQLEND
at line 25 of my initial code tag

for your ref..

FLAG=`sqlplus -silent viewer/viewer@ltstprod << SQLEND >> ${L2S_RUN_LOG_FILE}set pagesize 0 feedback off verify off heading off echo offselect UNCONSTRAINED_PIECE_PART from ICMPINPUTCONTROL;exit;SQLEND`

I missed that. You need to instruct CSH that the command is split over multiple lines:

FLAG=`sqlplus -silent viewer/viewer@ltstprod << SQLEND >> ${L2S_RUN_LOG_FILE} \
set pagesize 0 feedback off verify off heading off echo off \
select UNCONSTRAINED_PIECE_PART from ICMPINPUTCONTROL; \
exit; \
SQLEND`

Thank you very much Scott, it worked! (but without spaces before \ symbol).

Thanks once again.

now Am stuck with another error in my code. Though the values in table column in Y, I get this error. The if condition is supposed to be true.
Error is:
[Y=Y]: No match

"Pre_s2a_leadtime.sh" 54 lines, 1243 characters
#!/bin/csh

# Check if program is already running and echo a time stamp for the log file
#log_start_chk $0 $$
#if ( $status == 0 ) then
#exit
#endif

#source run_env

set L2S_RUN_LOG_FILE="Pre_s2a_leadtime.log"

echo -n "$0 started" >> ${L2S_RUN_LOG_FILE}
date '+%m%d%y %H:%M:%S' >>  ${L2S_RUN_LOG_FILE}


#setenv DB_NAME viewer/viewer@ltstprod


set FLAG=`sqlplus -silent viewer/viewer@ltstprod << SQLEND\
set pagesize 0 feedback off verify off heading off echo off\
select unconstrained_piece_part from icmpinputcontrol;\
exit;\
SQLEND`

if [$FLAG='Y'] then

sqlplus viewer/viewer@ltstprod << sqlend >> ${L2S_RUN_LOG_FILE}
# Running the following queries before the actual L2S program

drop table cycle_time_bkp;

create table cycle_time_bkp as select * from cycle_time;

update cycle_time a set cycle_time_days = 0
where exists(select 1 from route b where a.id = b.id and a.route = b.route and b.flow_id = 7);
commit;
quit
sqlend
"Pre_s2a_leadtime.sh" 54 lines, 1243 characters

Change the format of your if statement:

sk:/tmp> set FLAG="Y"
sk:/tmp> echo $FLAG
Y
sk:/tmp> if ( ${FLAG} == Y ) echo "hi"
hi
sk:/tmp> if ( ${FLAG} == N ) echo "hi"
sk:/tmp>

Edited 7 Years Ago by sknake: n/a

Comments
my two bugs got resolved, gr8 job!

Thanks a lot Scott!
it worked..

one more thing, am I connecting to sql more than once. but i use sqlend eachtime. am getting a message saying script FAILED at the end, though all sql stmts are executing completely.

Can you advise why is this?

My log file output is as below

Pre_s2a_leadtime.sh started100909 02:51:37

SQL*Plus: Release 9.2.0.3.0 - Production on Fri Oct 9 02:51:37 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> SP2-0734: unknown command beginning "Running th..." - rest of line ignored.
SQL> SQL>
Table dropped.

SQL> SQL>
Table created.

SQL> SQL>   2
649 rows updated.

SQL>
Commit complete.

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Produc                                          tion
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Pre_s2a_leadtime.sh has FAILED

I don't know. What is throwing the error -- the oracle client or the script? I'm not that familiar with csh or oracle. Sorry I couldn't be of any help on this one. You do have a comment in the middle of your SQL Statement though -- Maybe that is causing it to report the failure:

"Pre_s2a_leadtime.sh" 54 lines, 1243 characters
#!/bin/csh

# Check if program is already running and echo a time stamp for the log file
#log_start_chk $0 $$
#if ( $status == 0 ) then
#exit
#endif

#source run_env

set L2S_RUN_LOG_FILE="Pre_s2a_leadtime.log"

echo -n "$0 started" >> ${L2S_RUN_LOG_FILE}
date '+%m%d%y %H:%M:%S' >>  ${L2S_RUN_LOG_FILE}


#setenv DB_NAME viewer/viewer@ltstprod


set FLAG=`sqlplus -silent viewer/viewer@ltstprod << SQLEND\
set pagesize 0 feedback off verify off heading off echo off\
select unconstrained_piece_part from icmpinputcontrol;\
exit;\
SQLEND`

if [$FLAG='Y'] then

sqlplus viewer/viewer@ltstprod << sqlend >> ${L2S_RUN_LOG_FILE}
# Running the following queries before the actual L2S program
drop table cycle_time_bkp;

create table cycle_time_bkp as select * from cycle_time;

update cycle_time a set cycle_time_days = 0
where exists(select 1 from route b where a.id = b.id and a.route = b.route and b.flow_id = 7);
commit;
quit
sqlend
"Pre_s2a_leadtime.sh" 54 lines, 1243 characters

At that point it isn't interpreting that line as shellscript but rather piping the contents to a file which is evident in:

SQL> SP2-0734: unknown command beginning "Running th..." - rest of line ignored.
SQL> SQL>
Table dropped.

Move that comment above the >>file and see if that cures your issue

Edited 7 Years Ago by sknake: n/a

This question has already been answered. Start a new discussion instead.