This content has been marked as final. Show 2 replies
Sorry, it's unclear what you want to do.
I don't believe there's any way to conditionally stop executing a SQL*Plus script, but continue in the same session.
Using substitution variables, you can get the same effect, by dividing the script into pieces. At decision points, assign the name of the next script you want to run to a substitution variable (using COLUMN ... NEW_VALUE), and end the script like this:
Are you trying to exit the PL/SQL block?
Depending on what exactly you want to do, you can use IF statements or EXCEPTION sections.
WHENEVER SQLERROR ... is one way to exist a session. Is that what you want to do?
If would help a lot if you posted a concrete example. Include CREATE TABLE and INSERT statements for tables used (unless you're using commonly available tables, such as those in the scott schema) and the results you want from that data.
"I have the following SQL*Plus script ... When I call it like this:
it (correctly) produces this output ... because it finds an emplyee with ename='KING' in the scott.emp table. That much works great.
If I pass a name that does not exist, like this:
then I would like to get this output ... that is, I would like to skip the output where ... and continue with the output ..."
I think I had the answer from the start. The When SQLERROR statement had been ending the script, but with a printed message I confused as a hard-halt message.
What I am trying to accomplish is exiting a SQL*Plus script (not pl/sql) when a not found condition occurs in sql block. The catch is, I have the .sql script launched by an Oracle concurrent job. The concurrent job finishes normal when the record is found, but when the not found condition occurs, the concurrent job goes red...a misleading condition to the end-user.
So, I was trying to find a graceful exit, from a .sql plus script, launched by a concurent job.
Seem unnecessary, but I think I need to call the script using a front-end pl/sql package - filter the data and if found, call the script.