Shell script to update row using SQL Plus

Question

Okay, so I have a script that I'm trying to run that creates a file with an update statement and pipes that to sqlplus to execute. I know that the update statement is valid and it creates the file to send to sqlplus is correct because I have ran just that code in SQL Plus. It just doesn't work when I execute the script. My team and I have looked at this and have tried multiple things, but just can't figure out why it doesn't seem to be executing. Any help would be great. Thanks!

 # Update Cycle DUE_DATE_TIME field to target run date
   UPDATE_SQL="UPDATE CYCLE SET DUE_DATE_TIME = to_date('${TARGET_RUN_DATE}', 'YYYYMMDD') - 1"
   echo $UPDATE_SQL >> ${WORK_AREA}/update_cycle_table_${PROCESS_ID}.sql
    echo "${USER_NAME}@${DATABASE}
    set line 200
    start ${WORK_AREA}/update_cycle_table_${PROCESS_ID}.sql
    commit
    exit"|sqlplus -s > /dev/null
   if [[ $? -eq 0 ]]
   then
      print "Updated Cycle successfully\n\n"
   fi

Okay, so I resolved the issue by making it a bit simpler and instead of just writing it to a file and running that in SQLPlus, I just did the update within the pipe to SQLPlus. I tried to do that before and it wasn't working, but I guess whatever I did this time worked. Thanks everyone for your input!

remove the > /dev/null so you can see if there are any error messages. Also does $? get set to 0? Good luck. — shellter
– shellter, Commented Oct 9, 2013 at 19:33
I had removed /dev/null previously and there were no errors. The "Updated Cycle successfully" is printed out so $? is set to zero @shellter — Ashley White
– Ashley White, Commented Oct 9, 2013 at 19:39
very minor thing, but it might make a difference, echo "$UPDATE_SQL" >> ${WORK_AREA}/....? Hey, why >> should just need > . Good luck. — shellter
– shellter, Commented Oct 9, 2013 at 20:18

Nicolas · Accepted Answer · 2013-10-09 20:32:34Z

1

You're missing a ; at the end of the update and after the commit. You should probably add something like :

spool ${WORK_AREA}/update_cycle_table_${PROCESS_ID}.log

after the set line to get a log of what's not working.

answered Oct 9, 2013 at 20:32

Nicolas

9337 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Robert Bender · Accepted Answer · 2013-10-10 03:10:50Z

1

I'd suggest this. (Note I typed this without access to ksh, so you might have to play with the code a bit to get it to work). I've never been a big fan of piping stuff into sqlplus. And the return from sqlplus is not very useful. The return only indicates if sqlplus succesfully ran, not any error condition with the underlying sql.

sqlplus -s /nolog <<EOD >> ${sql_local_output_file}
  connect ${connect_string}
  @${WORK_AREA}/update_cycle_table_${PROCESS_ID}.sql
  commit
EOD

if (!grep -q -e error ${sql_local_output_file}); then 
      print "Updated Cycle successfully\n\n"
else
      cat ${sql_local_output_file}
fi

answered Oct 10, 2013 at 3:10

Robert Bender

2,6691 gold badge15 silver badges9 bronze badges

Comments

Ashley White · Accepted Answer · 2013-10-10 13:40:27Z

0

Resolved by this code instead of the previous:

#Update cycle table with user input TARGET RUN DATE
  echo "${USER_NAME}@${DATABASE}
       set feedback off
       UPDATE CYCLE SET DUE_DATE_TIME = to_date('${TARGET_RUN_DATE}', 'YYYYMMDD') - 1;
       exit"|sqlplus -s >/dev/null
   if [[ $? -eq 0 ]]
   then
      print "Updated Cycle successfully\n\n"
   fi

It wasn't even reaching the code that was piped into SQLPlus for some reason, so I made it simpler and just did the above. Weird how this simpler code wouldn't work early. Must have overlooked something before. Thanks again everyone for looking into this!

answered Oct 10, 2013 at 13:40

Ashley White

11 silver badge2 bronze badges

Collectives™ on Stack Overflow

Shell script to update row using SQL Plus

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related