2 Replies Latest reply on Jun 26, 2012 12:55 PM by emss

    How to exit sql*plus script on condition

      Trying to do something that should be very simple - exit the .sql script on condition, but can't find an good example I understand.

      In genereal, I need to exit the .sql script when a record does not exist in a select.

      I found this statement in an Oracle support line script...but it uses a cursor. I don't need a cursor. EXIT WHEN v_ps%NOTFOUND;

      I've tried below sql:

      Variable bnd_Org_Id Number

      /* Limit processing to org 140. Change here to enable other facilitites if ever needed.*/

      Select wnd.Organization_id into :bnd_Org_id
      from apps.wsh_new_deliveries wnd
      Where 7 = 7
      and wnd.delivery_id = '&sv_Delivery_id'
      and wnd.organization_id in ('140');

      When the data is found, the script works. However, when the data not found - concurrent job goes red with

      begin * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 2

      Any suggestions or help certainly apprediated.
        • 1. Re: How to exit sql*plus script on condition
          Frank Kulash

          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.
          For example:
          "I have the following SQL*Plus script ... When I call it like this:
          @my_script KING
          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:
          @my_script FUBAR
          then I would like to get this output ... that is, I would like to skip the output where ... and continue with the output ..."
          • 2. Re: How to exit sql*plus script on condition
            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.