Include the ability to handle PL/SQL exception variables in the %throws annotation

[markhawker76]

Within my internal PL/SQL subprograms, all errors are raised with exception variables. These exceptions are not linked to a specific error number until the exception is caught at the top level public PL/SQL procedure. This requires additional work to be done in the test code which hopefully could be done via the framework via the %throws annotation.

I would like the ability to put a PL/SQL exception variable name (e.g. package and variable name of type exception) into the %throws annotation without the need to provide or associate a SQL error number using the PRAGMA EXCEPTION_INIT directive.

[jgebal]

Hi @markhawker76
Please provide an example of code that you would like utPLSQL to work with.

utPLSQL already supports expectations with exceptions variables.
See this section of documentation: https://github.com/utPLSQL/utPLSQL/blob/develop/docs/userguide/annotations.md#throws

Is this not matching your needs?

[markhawker76]

Hi,

Thank you for the pointer to the documentation regarding the use of the "throws" annotation and concur that named exceptions are supported.

However, I would like to point out that a named exception will only work if it is associated with an exception number using the Exception_Init pragma. Without this pragma, the exception is not caught. An error might also occur but I cannot remember the specifics.

In my application, where named exceptions are (almost always) used, they are never given a SQL error number until the last possible moment at the outmost PL/SQL layer in the call stack. If unit testing any other layer with named exceptions, I have to create a wrapper of code similar to below. Whilst this is perfectly acceptable, it would be better if the named exception could be used natively without the exception_init restriction to keep down the amount of testing code required to be built and maintained.

--%throws(-20999)
BEGIN
  BEGIN
    some_test_here();
  EXCEPTION
    WHEN <named exception with no exception_init pragma>
    THEN
      SYS.DBMS_STANDARD.RAISE_APPLICATION_ERROR
        (
          num => -20999
         ,msg => 'Exception <named exception with no exception_init pragma> occured'
        );
  END;
END;

I hope this clarifies the issue.

Many thanks,
Mark.

[lwasylow]

Hi @markhawker76
Would you be so kind and provide and sample example of the test you want to do ?
I'm not sure I understand the problem.
The snippet above I believe should work in our testing framework as per documentation as you can use number or package spec defined variable number.

  --%test(Gives failure when an exception is expected and nothing is thrown)
  --%throws(-20459, -20136, -20145)
  procedure nothing_thrown;
  
  --%test(Throws package exception option1)
  --%throws(exc_pkg.c_e_option1)
  procedure raised_option1_exception;

[markhawker76]

Hi,

Thanks for reaching out and supplying additional information.

I believe in your example, the %throws annotation requires the PLSQL exception defined within it to be associated with a user-defined SQL error in order for the framework to detect it.

I have a multilayered application and PLSQL exceptions are used throughout. The declaration for these exceptions are deliberately NOT associated with a user-defined SQL error number using the compiler pragma exception_init. Only the outer most layer will catch expected PLSQL exceptions and perform a raise_application_error() with a specific exception number and message required for SQL.

When testing any subprogram that is not defined in the outer layer, I have to put an additional exception handler in each unit test to catch the PLSQL exception and then call raise_application_exception with a SQL number. This SQL number then specified in the %throws annotation in order for the framework to detect it.

Allowing me to put the PLSQL exception directly into the %throws annotation without the need to associate a sql error number via pragma is the utPLSQL enhancement asked for.

I hope this additional information helps,
Mark.

[lwasylow]

Hi Mark
It's been a while since look at the code but looking in tests (https://github.com/utPLSQL/utPLSQL/blob/develop/test/ut3_tester/core/annotations/test_annot_throws_exception.pkb) I can see that you don't need to define the pragma for that to work.
Unless I'm missing the point here in that case could you please mock up simple case you doing?

create package annotated_package_with_throws is
        --%suite(Dummy package to test annotation throws)

        --%test(Throws same annotated exception)
        --%throws(-20145)
        procedure raised_same_exception;
end;
/

create package body annotated_package_with_throws is
        procedure raised_same_exception is
        begin
          raise_application_error(-20145, ''Test error'');
        end;
End;
/

[jgebal]

I think what @markhawker76 is referring to is below use-case:

create or replace package tested_package is

    the_exception exception;

    procedure the_tested_procedure;

    procedure the_outer_procedure;

end;
/

create or replace package body tested_package is

    the_exception exception;

    procedure the_tested_procedure is
    begin
        raise the_exception;
    end;

    procedure the_outer_procedure is    
    begin
        the_tested_procedure;
    exception
        when the_exception then
            null;
            --do stuff here;
    end;

end;
/

create package annotated_package_with_throws is
        --%suite

        --%test
        --%throws(tested_package.the_exception)
        procedure raised;
end;
/

create package body annotated_package_with_throws is
        procedure raised is
        begin
          the_tested_procedure;
        end;
End;
/

[markhawker76]

Hi @jgebal this is exactly the functionality that I would like to see. The ability for utPLSQL to catch a PL/SQL exception without it being associated with a SQL error number -20000 - -20999.

Thanks!

[markhawker76]

In my code base I have dedicated packages that hold constants and exceptions in a different schema to the code so if the utPLSQL solution for catching named exceptions could be fully qualified e.g. .. this would be perfect.

[markhawker76]

hmmm the example was omitted from the chat for some reason. It was:

schema.package.exception