Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-12-12
2
Medium Priority
?
448 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 600 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 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 600 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

636 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