• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

how to have if logic in a simple (oracle) sql file

Hi,

I have a shell script which invokes a sql file like below. When there is an error (ORA error) in SQL it rollback the changes and abort the sql execution.

Shell script. run.sh
sqlplus <1>/<2>@${target.db.instance} @runSqlScript.sql <3>

runSqlScript.sql content
whenever sqlerror exit failure rollback
start &1

My request is:
I would like add a logic/flag "ignore_error"  inside the SQL file "runSqlScript.sql" to determine to exit on error or continue on error.

sorry I'm a if else guy. not good in sql.

e.g.
if ignore_error=true
whenever sqlerror continue
else
whenever sqlerror exit failure rollback
end if
start &1
0
enthuguy
Asked:
enthuguy
2 Solutions
 
DavidSenior Oracle Database AdministratorCommented:
First pass, you would have to set the flag as a shell variable (if true then export var=string); then pass that variable to the whenever phrase - e.g., "whenever sqlerror 'var'".
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
This one's a quick shot/workaround:
You could set up a "set env" script, one with "whenever sqlerror continue" and the other with "whenever sqlerror exit failure rollback". Then you could decide from "outside" which one to load (or from within the DB -> e.g. some options table)...

You might want to take a look at this:
https://forums.oracle.com/thread/909622
https://forums.oracle.com/message/4499960#4499960
http://www.freelists.org/post/oracle-l/Conditional-logic-in-a-main-SQLPLUS-script-file,4
http://stackoverflow.com/questions/13345621/sqlplus-decode-to-execute-scripts
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now