Solved

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

Posted on 2013-12-12
2
440 Views
Last Modified: 2013-12-13
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
Comment
Question by:enthuguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Accepted Solution

by:
David earned 200 total points
ID: 39714150
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
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 200 total points
ID: 39714158
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

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question