4 Replies Latest reply on May 21, 2019 6:22 PM by Glen Conway

    Startup script behavior question.

    2772914

      Hi,

       

      In my startup script, I have only 1 statement. WHENEVER SQLERROR EXIT ROLLBACK;

       

      This works fine when I open a new sql developer worksheet and press F5. However, on the second run (F5) the exit doesn't happen.

       

      Steps:

       

      1) alter your settings to include a startup script with "whenever sqlerror exit rollback;"

      2) open a new worksheet.

      3) Paste this in the worksheet

       

      select 'test' as sample_label from dual;
      select 1/0 from dual;
      select sysdate from dual;
      

       

      4) Press F5.

      5) I get the output as expected.

      SAMP
      ----
      test
      Error starting at line : 3 in command -
      select 1/0 from dual
      Error report -
      ORA-01476: divisor is equal to zero
      
      Rollback
      

       

      6) Press F5 again.

      7)  The output isn't as expected. Although I see the error, the sysdate is printed.

       

      SAMP
      ----
      test
      
      
      
      Error starting at line : 3 in command -
      select 1/0 from dual
      Error report -
      ORA-01476: divisor is equal to zero
      
      
      
      
      SYSDATE             
      --------------------
      16-MAY-2019 10:46:24
      

       

      Version 19.1

        • 2. Re: Startup script behavior question.
          Glen Conway

          One big hint you are seeing (upon the initial execution) is the failure to display the 'select sysdate from dual' output.  On the second execution, however, you do see it.

           

          So what's going on here?  If you specify EXIT in a login.sql script used by a sqlplus or sqlcl session, then your test case will actually disconnect the session, so of course the select sysdate never runs.  SQL Developer probably works the same way, but with this extra confusion (which I suppose many would count as a bug):

           

          1) No disconnect message appears in the Script Output and the connection icons (in the tree and the worksheet drop-down list) still indicate the connection is active.

          2) Immediately checking View -> Log -> Statements proves 'select sysdate' did not run.

          3) If you Run Script (F5) again, SQL Developer automatically reconnects, but without running the login.sql script again.

           

          Anyway, you could log a SR through MOS and wait for a bug fix, but it is probably better to adapt to the current way the product works.  Avoid

          whenever sqlerror exit rollback;

          and use

          whenever sqlerror continue rollback;

          instead.

          • 3. Re: Startup script behavior question.
            2772914

            Thanks for replying.

             

            Apart from EXIT, Is there a way to stop the execution when there is an error?

             

            The method you proposed (whenever sqlerror continue rollback;) would still execute the following statements in case of an error.

            • 4. Re: Startup script behavior question.
              Glen Conway

              No.  Wanting to exit the session upon hitting an error is perfectly reasonable, and of course SQL Developer does exactly that if "whenever sqlerror exit rollback;" is in your login.sql script.

               

              My preference, however, would be not to have to deal with this confusing behavior (no disconnect message in the results tab, and the automatic reconnect bit without running the login.sql again).  I would probably not remember that EXIT on error condition in my login script.

               

              I would much prefer to run SQL Developer with no login.sql, or one (specified in Preferences -> Database, it does not have to be called login.sql) without that EXIT and develop and test my code interactively in a worksheet.  Once I get things working there, I would create a script for sqlplus or sqlcl with appropriate output spooling and let those tools use a login.sql with the "whenever sqlerror exit rollback;".

               

              My two cents, Cheers