Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

oracle - How do I exit a script in SQLPlus when an error occurs and return to the SQLPlus prompt, without disconnecting or exiting SQLPlus?

I have some scripts that get run often, always from within a connected SQLPlus session.

I need a way to exit the script when an error occurs, without disconnecting or exiting SQLPlus itself. 100% of the time, when an error occurs, the connected DBA will need to issue one or more commands into the session. 100% of the time, when an error occurs, there are other SQLPlus statements (and thus must be outside of a BEGIN..END;) later on in the script that must not be executed or serious problems could arise.

NOTE: If you suggest WHENEVER SQLERROR EXIT then you didn't read the above text. That will disconnect and exit SQLPlus in addition to the script, which is not acceptable behavior.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I've found an interesting idea here which, when combined with spencer7593's answer, will get me selective sub-script calling, to which I can pass the PL/SQL output values. To wit:

VAR continue number;
EXEC :continue := 1;
BEGIN
   SELECT some_bool_test() INTO :continue FROM dual;
END;

SET termout OFF
COLUMN script_name NEW_VALUE v_script_name
SELECT decode(:continue, 1, 'run_stuff.sql', 'skip.sql') script_name FROM dual;
SET termout ON

@&v_script_name :some_other_values

Where skip.sql is an empty text file.
 

UPDATE: I've moved most of this into a RUN.SQL file, where I pass in the boolean (0 or 1) as &1, the script name to call on success as &2, and then any other expected parameters to pass to the called script. Thus, it ends up looking something like this:

VAR continue number;
EXEC :continue := 1;
BEGIN
   SELECT some_bool_test() INTO :continue FROM dual;
END;

@run.sql :continue 'run_stuff.sql' :some_other_values

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...