4 Replies Latest reply on Jun 6, 2014 5:18 PM by SKP

    exit with error code from bind variable

    946279

      Could anyone explain me what is wrong with the last case presented below? Why is there an error "not number"?

       

      When I do this in sqlplus script it works and in the OS I can see echo $? equal 7:

       

      var v_b number

      begin

      raise zero_divide;

      exception

      when others then :v_b := 7;

      end;

      /

      exit :v_b

       

      But when I do this it doesnt work:

       

      var v_b number

      var v_dummy varchar2(1)

       

      begin

      select dummy into :v_dummy from duelllly;

      exception

      when others then :v_b := 7;

      end;

      /

      exit :v_b

       

      ERROR at line 3:
      ORA-06550: line 3, column 34:
      PL/SQL: ORA-00942: table or view does not exist
      ORA-06550: line 3, column 2:
      PL/SQL: SQL Statement ignored


      SP2-0670: Internal number conversion failed
      Usage: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n | <variable> | :<bindvariable> ] [ COMMIT | ROLLBACK ]

       

      I expected v_b containing  7 again, but it aparently isn't. Why is that? I would appreciate clarification

       

      Thank you

        • 1. Re: exit with error code from bind variable
          user8987724

          Hi,

          The 'EXCEPTION' block in the PL/SQL block is to catch a error raise during the execution of the program ('RUN TIME'). But not the 'COMPLIATION ERRORS'.

          In your first experiment it was a run time error raised while program execution. But the "PL/SQL: ORA-00942: table or view does not exist" is compilation error raised as part of the Parsing (syntax and semantic checks) the SQL statements in the pl/sql block.

          • 2. Re: exit with error code from bind variable
            SKP

            ERROR at line 3:
            ORA-06550: line 3, column 34:
            PL/SQL: ORA-00942: table or view does not exist
            ORA-06550: line 3, column 2:
            PL/SQL: SQL Statement ignored

            This is a compilation error.

            Still your code has not been executed.

            During the compilation if found the Table does not exist

            1 person found this helpful
            • 3. Re: exit with error code from bind variable
              946279

              Thanks guys for answer, it does make sense to me. this works too:

               

              var v_b number

              var v_dummy varchar2(1)

              begin

              select dummy into :v_dummy from dual where 1=0;

              exception

              when others then :v_b := 7;

              end;

              /

              exit :v_b

               

              Thanks!

              • 4. Re: exit with error code from bind variable
                SKP

                If you are using shell script  then you can use

                WHENEVER SQLERROR EXIT 7

                lets say you create the script

                abc.sh

                sqlplus -s scott/tiger << eof

                  whenever sqlerror exit 7;

                  select 1 from xxxx;--table not exist

                eof

                 

                echo Exited with $?

                 

                run the script

                ./abc.sh

                 

                select 1 from xxxx

                  *

                ERROR at line 1:

                ORA-00942: table or view does not exist

                 

                Exited with 7