I want to run an update script for my Oracle 8i database in SQL*Plus which checks some data and run other scripts. The code looks like the following:
CREATE OR REPLACE PROCEDURE check_table (RESULT OUT NUMBER)
SELECT DISTINCT * FROM some_table WHERE some_column='x';
RESULT := 0;
FOR l_rec IN l_cur
RESULT := RESULT + 1;
IF RESULT > 0
DBMS_OUTPUT.put_line ('Invalid data.');
DBMS_OUTPUT.put_line ( 'Exception in procedure check_table(): '
|| DBMS_UTILITY.format_error_stack ()
VARIABLE ret_val char
This runs fine so far. Now I need something like
if ret_val > 0 then exit
I.e. if the check data procedure finds invalid data, the script execution shall terminate. Otherwise it shall continue and run some other update scripts. I do not want to put all the code into one (this particular update) script as there is a lot code to execute and the other scripts are standard scripts which are run at periodical points of database updates, such as recompilation of objects etc..
Thanks for any help