Evaluating variable from PL/SQL procedure in SQL*Plus

Hi all,

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)
AS
   invalid_data   EXCEPTION;

   CURSOR l_cur
   IS
      SELECT DISTINCT * FROM some_table WHERE some_column='x';
BEGIN
   RESULT := 0;
   FOR l_rec IN l_cur
   LOOP
      RESULT := RESULT + 1;
   END LOOP;

   IF RESULT > 0
   THEN
      DBMS_OUTPUT.put_line ('Invalid data.');
      RAISE invalid_data;
   ELSE
      DBMS_OUTPUT.put_line ('Ok.');
   END IF;
EXCEPTION
   WHEN invalid_data
   THEN
      NULL;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'Exception in procedure check_table(): '
                            || DBMS_UTILITY.format_error_stack ()
                           );
END;
/

VARIABLE ret_val char
EXEC check_table(:ret_val);


This runs fine so far. Now I need something like
 if ret_val > 0 then exit
else
@@the_next_script.sql

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
Alexander Pohl
ThinkAppsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sapnamCommented:
I think you have to use DBMS_SQL
ThinkAppsAuthor Commented:
Can you go more into detail with your suggestion? I have found nothing in the package which might solve the problem. Another approach might be to terminate the session from within the procedure code, but I have not found anything like that either.
sapnamCommented:
DBMS_SQL or its later and better version EXEC_SQL are used to execute SQL statements from within PL_SQL.  In your context, the only problem I see is that these packages require the SQL statement to be parsed in the PL/SQL whereas you would just like to execute existing SQL statements.  

One suggestion. If you convert your update sqls into stored procedures you can easily call them from within your PL/SQL depending on the result of the IF condition
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ThinkAppsAuthor Commented:
> If you convert your update sqls into stored procedures...

This is what I need to avoid because the other scripts are huge and create / modify lots of tables and are inserting hundreds of rows for a dynamicly configured java frontend tool.
sapnamCommented:
OK, let me find out how to call a SQL script in a file from within PL/SQL and I will post the same to you
sapnamCommented:
This is one of the approaches I found.  From the command prompt we can invoke SQL and execute our SQL script and get the desired results.  The problem is that you cannot use the HOST command in PL/SQL.

  There is a workaround given to this in http://www.oracle-base.com/articles/8i/ShellCommandsFromPLSQL.php.

This involves creating a Java stored procedure which you can call from within PL/SQL to execute commands as if you are using the HOST command

DrSQLCommented:
ThinkApps,
   Did you try WHENEVER SQLERROR EXIT?  You'll want to be spooling so you can see what the error was, but it will ensure that your execution stops.  Just put it at the beginning of the sqlplus jobstream.  And, you can put "WHENEVER SQLERROR CONTINUE before any sections wher eyou don't want it to exit (like before you drop a table that may or may not be there).

Good luck!
DrSQL
DrSQLCommented:
ThinkApps,
   If you REALLY want to check, create a small .sql script that has this in it:

store set sqlplus_settings.sql replace
set termout off
set sqlprompt REM
set echo off

spool get_sqlcode.sql
show SQLCODE
spool off

select
#get get_sqlcode.sql

a ' then '@@&&1' else 'EXIT' end from dual
c/sqlcode /select case when '0' = '/
spool continue_on_success.sql
/
spool off
start sqlplus_settings
start continue_on_success

Then call this right after the modules you think might fail.  Pass the name of the next step and it will perform it.  Just don't call this script get_sqlcode or continue_on_success, since those filenames are used by the script.

Good luck!
DrSQL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ThinkAppsAuthor Commented:
Hi DrSql,

I honestly do not understand the script. I've tried it but didn't succeeded. Anyway, you put me on the right track. I am now creating a table, writing the SQL command (exit or @@next_script.sql) into it and spool the select on this table into a script executed after the procedure call. I drop the table then, so there's no waste left. This is why I accepted your answer. Anyway, if you could take the effort to explain what your script is supposed to do, I'd appreciate that. I think >show SQLCODE is supposed to write the last sql_error into get_sqlcode.sql, which is then evaluated by the code between 'select ... ' and the last '/'. This is the portion I do not understand at all :-P. My procedure does not produce an error code, this might be the reason why your script does not work for me. All the procedure does is store the count of records in the result var. If the result var is greater than zero, the script should exit.

Thanks all for your time and help,
ali
DrSQLCommented:
Ali,
    I'd be happy to explain.  I do hope that in future questions, you'll give the experts the opportunity to respond to any issues BEFORE you give a grade of B.  As you guess, the procedure I gave you is designed to be an error-based check routine.  But, it could still be used by your procedure.  If you don't return any data you could add a line in your exception handler that said:

Raise invalid_data;

But to explain my code:

-- This section sets the environment and remembers our current sql plus settings
store set sqlplus_settings.sql replace
set termout off
set sqlprompt REM
set echo off

-- Next we want to capture the current sqlcode in a file
spool get_sqlcode.sql
show SQLCODE
spool off

--  this is the part that is a trick.  We have a line that starts with "select", this tells sqlplus to start the sql command buffer.
-- then we use a "get" instruction to fetch the file that we just created with the sqlcode in it.
select
#get get_sqlcode.sql

-- now we build our select command to basically say, if the sqlcode was 0 then run the command we passed in, otherwise exit.
a ' then '@@&&1' else 'EXIT' end from dual
c/sqlcode /case when '0' = '/                         -- and I had an error here, I shouldn't have had "select" again
--  The buffer would now have
-- select
-- case when '0' = '0' then '@@<your next command script>' else 'exit' end from dual
--
--  We put that into a file
spool continue_on_success.sql
/
spool off
-- Bring back the sqlplus settings and run the file we just created.  It should have either 'EXIT' or '@@<your script>' in it
start sqlplus_settings
start continue_on_success


Good luck!
DrSQL
ThinkAppsAuthor Commented:
DrSql,

> I do hope that in future questions, you'll give the experts the opportunity to respond to any issues BEFORE you give a grade of B.

I will.

Thanks for explaining the code. Problem is solved now.

Regards,
ali
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.