Invalid parameter value [constant_name] for "--%throws" annotation

[alshib]

Hi!
When we modify and recompile test package sometimes we get warnings like this:
"Invalid parameter value "test_blablabla.c_1" for "--%throws" annotation. Parameter ignored."

Provide version info
Information about utPLSQL and Database version,
11.2.0.4.0
11.2.0.4.0

UT_VERSION

v3.1.9.3270
1 row selected.

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

PARAMETER

VALUE

NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
CIS

NLS_CURRENCY
р.

NLS_ISO_CURRENCY
CIS

NLS_NUMERIC_CHARACTERS
,

NLS_CALENDAR
GREGORIAN

NLS_DATE_FORMAT

PARAMETER

VALUE

DD.MM.RR

NLS_DATE_LANGUAGE
AMERICAN

NLS_SORT
BINARY

NLS_TIME_FORMAT
HH24:MI:SSXFF

NLS_TIMESTAMP_FORMAT
DD.MM.RR HH24:MI:SSXFF

NLS_TIME_TZ_FORMAT
HH24:MI:SSXFF TZR

NLS_TIMESTAMP_TZ_FORMAT
DD.MM.RR HH24:MI:SSXFF TZR

PARAMETER

VALUE

NLS_DUAL_CURRENCY
р.

NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

NLS_NCHAR_CONV_EXCP
FALSE

17 rows selected.

PORT_STRING

x86_64/Linux 2.4.xx
1 row selected.

Information about client software
TOAD

To Reproduce
add new test in the package (an error does not always occur )
Expected behavior
Tests are working without warnings

Example code
example of package specification:
"create or replace package test_balablabla is
c_1 number := pkg_errors.get_exception_code('err_1_name');
c_2 number := pkg_errors.get_exception_code('err_2_name');
c_n number := pkg_errors.get_exception_code('err_n_name');

-- %suite(blabla)

-- %test
-- %throws(test_balablabla.c_1)
procedure precheck_something;

end;"

[pesse]

Hey @alshib ,
I cannot reproduce the problem:

create or replace package pkg_errors is
  function get_exception_code(i_identifier varchar2)
    return integer;
end;
/

create or replace package body pkg_errors is
  function get_exception_code(i_identifier varchar2)
    return integer
  as
    begin
      return
      case i_identifier
        when 'err_1_name' then -20001
        when 'err_2_name' then -20002
        when 'err_3_name' then -20003
      end;
    end;
end;
/

create or replace package test_balablabla is
  c_1 number := pkg_errors.get_exception_code('err_1_name');
  c_2 number := pkg_errors.get_exception_code('err_2_name');
  c_n number := pkg_errors.get_exception_code('err_n_name');

  -- %suite(blabla)

  -- %test
  -- %throws(test_balablabla.c_1)
  procedure precheck_something;

end;
/

create or replace package body test_balablabla is
  procedure precheck_something
  as
    begin
      raise_application_error(-20001, 'OMG! The rebels are attacking!');
    end;
end;
/

call ut.run('test_balablabla');

Output:

blabla
   precheck_something [,002 sec]

 Finished in ,004929 seconds
 1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

Is it possible that something is broken in pkg_errors - or that the error numbers change?

[alshib]

Unfortunately i cannot reproduce too when i want to do it. right now i cannot describe the algorithm for reproducing this bug. This one arises when i modify package specification but not each time. Sometime ago we were able to fix it by doing these staps 1 - delete constants 2 -recompile package 3 - run tests 4- restore constants but today it does not work((
Yesterday this bug was fixed only after reinstall UT.
Right now i executed your code and got good result. But when i added new test and recompiled package i got this warning "Invalid parameter value "test_balablabla.c_2" for "--%throws" annotation. Parameter ignored.
at package "PF_S.TEST_BALABLABLA.PRECHECK_SOMETHING1", line 13"

Code:

create or replace package test_balablabla is
  c_1 number := pkg_errors.get_exception_code('err_1_name');
  c_2 number := pkg_errors.get_exception_code('err_2_name');
  c_n number := pkg_errors.get_exception_code('err_n_name');

  -- %suite(blabla)

  -- %test
  -- %throws(test_balablabla.c_1)
  procedure precheck_something;
  
    -- %test
  -- %throws(test_balablabla.c_2)
  procedure precheck_something1;

end;
/

create or replace package body test_balablabla is
  procedure precheck_something
  as
    begin
      raise_application_error(-20001, 'OMG! The rebels are attacking!');
    end;
    
      procedure precheck_something1
  as
    begin
      raise_application_error(-20002, 'OMG! The rebels are attacking!');
    end;
end;
/

call ut.run('test_balablabla');

[alshib]

Hey,
i found the reason of this warning. It looks like as some optimisation shit. Constants can be able to be null when you call "execute immediate 'declare x positiven := -('||a_exception_name||'); begin null; end;';" in ut_suite_builder.check_exception_type maybe besause constant did not initialised due to optimisation(supposedly)

[alshib]

hi,
i have found a way for reproduce this bug:
create or replace package pkg_errors is
function get_exception_code(i_identifier varchar2)
return integer;
end;
/

create or replace package body pkg_errors is
function get_exception_code(i_identifier varchar2)
return integer
as
begin
return
case i_identifier
when 'err_1_name' then -20001
when 'err_2_name' then -20002
when 'err_3_name' then -20003
end;
end;
end;
/

create or replace package test_balablabla is
c_1 number := pkg_errors.get_exception_code('err_1_name');
-- %suite(blabla)

-- %test
-- %throws(test_balablabla.c_1)
procedure precheck_something;

end;
/

create or replace package body test_balablabla is
procedure precheck_something
as
begin
raise_application_error(-20001, 'OMG! The rebels are attacking!');
end;
end;
/

--!!!initialize suite cache
call ut.run('test_balablabla');
/
-- The result is ok

--add new test and constant in another session:
/*
--Add new test and constant
create or replace package test_balablabla is
c_1 number := pkg_errors.get_exception_code('err_1_name');
c_2 number := pkg_errors.get_exception_code('err_2_name');
-- %suite(blabla)

-- %test
-- %throws(test_balablabla.c_1)
procedure precheck_something;
-- %test
-- %throws(test_balablabla.c_2)
procedure precheck_something_2;
end;
/

create or replace package body test_balablabla is
procedure precheck_something
as
begin
raise_application_error(-20001, 'OMG! The rebels are attacking!');
end;
procedure precheck_something_2
as
begin
raise_application_error(-20002, 'OMG! The rebels are attacking!');
end;
END;
/
*/

--!!!reinitialize suite cache
call ut.run('test_balablabla');
/
--we got this error:
--ORA-04068: existing state of packages has been discarded
--ORA-04068: existing state of packages has been discarded
--ORA-06512: at "UT3.UT", line 112
--ORA-06512: at "UT3.UT", line 506
--ORA-06512: at "UT3.UT", line 623
--ORA-06512: at line 1

--!!!Call tests again
call ut.run('test_balablabla');
/
--we got warning:
--Invalid parameter value "test_balablabla.c_2" for "--%throws" annotation. Parameter ignored.
--at package "TEST_BALABLABLA.PRECHECK_SOMETHING_2", line 10

[alshib]

Second way for reproduce bug(thoday`s variant):
create or replace package pkg_errors is
function get_exception_code(i_identifier varchar2)
return integer;
end;
/

create or replace package body pkg_errors is
function get_exception_code(i_identifier varchar2)
return integer
as
begin

  case i_identifier
    when 'err_1_name' then raise_application_error(-20000,'unknown error name');
    when 'err_2_name' then return -20002;
    when 'err_3_name' then return -20003;
  end case;
end;

end;
/

create or replace package test_balablabla is
c_1 number := pkg_errors.get_exception_code('err_1_name');
-- %suite(blabla)

-- %test
-- %throws(test_balablabla.c_1)
procedure precheck_something;

end;
/

create or replace package body test_balablabla is
procedure precheck_something
as
begin
raise_application_error(-20001, 'OMG! The rebels are attacking!');
end;
end;
/

--!!!initialize suite cash with bug in the pkg_errors
call ut.run('test_balablabla');
/
-- We got the warning:
--Invalid parameter value "test_balablabla.c_1" for "--%throws" annotation. Parameter ignored.
--at package "TEST_BALABLABLA.PRECHECK_SOMETHING_1", line 6

--fix bug in the pkg_errors
create or replace package body pkg_errors is
function get_exception_code(i_identifier varchar2)
return integer
as
begin

  case i_identifier
    when 'err_1_name' then return -20001;
    when 'err_2_name' then return -20002;
    when 'err_3_name' then return -20003;
  end case;
end;

end;
/

--call tests again
call ut.run('test_balablabla');
/
-- !!!We STILL got the warning:
--Invalid parameter value "test_balablabla.c_1" for "--%throws" annotation. Parameter ignored.
--at package "TEST_BALABLABLA.PRECHECK_SOMETHING_1", line 6

[lwasylow]

If you replace function with constant does it break? If you initialize variable as part of begin block on package level will that break? E. G.
create or replace package test_balablabla is
c_1 number:
-- %suite(blabla)

-- %test
-- %throws(test_balablabla.c_1)
procedure precheck_something;
Begin
c_1 := pkg_errors.get_exception_code('err_1_name');
end;
/

[alshib]

hi, No. it does not give a good result. This bug can be fixed after force rebuild the suite(for refresh suite cache)

[jgebal]

This is related to: #721

The throws annotation is validated at suite parse time, not at suite-run time.

Also.

You're not using package constants but package variables.

Please give it a try with:

create or replace package test_balablabla is
  c_1 CONSTANT number := pkg_errors.get_exception_code('err_1_name');
  c_2 CONSTANT number := pkg_errors.get_exception_code('err_2_name');
  -- %suite(blabla)

  -- %test
  -- %throws(test_balablabla.c_1)
  procedure precheck_something;

  -- %test
  -- %throws(test_balablabla.c_2)
  procedure precheck_something_2;
end;
/

This looks like an ORACLE bug though.

Are you using v3.1.9 installation with DDL trigger or without?

Maybe the parsing at package compile time is the cause of issues in that case.

So what I would suggest trying is:

• make the variables actual constants
• install utPLSQL without DDL trigger (if you're running with trigger).

From our side, we should consider moving the interpretation of the throws annotation content into suite runtime engine.

[alshib]

Hi!
Unfortunately it is nit oracle bug. It is very strange oracle feature(https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS99926)

constant:
it has no effect because test package still keeps state and if the package is recompiled in another session this state will be reseted.

DDL trigger:
i will try to reinstall utPLSQL without DDL trigger later

"Maybe the parsing at package compile time is the cause of issues in that case." - you are right. In the general case this error coming up when teat package(or pkg_errors) is broken or has invalid state at moment of parse the suite

As second way to fix this error i can suggest to raise an exception when you are parsing the suite like this:

    function check_exception_type(a_exception_name in varchar2) return varchar2 is
      l_exception_type varchar2(50);
    begin
      --check if it is a predefined exception
      begin
        execute immediate 'begin null; exception when '||a_exception_name||' then null; end;';
        l_exception_type := gc_named_exception;
        exception
        when others then
        if dbms_utility.format_error_stack() like '%PLS-00485%' then
          begin
            execute immediate 'declare x positiven := -('||a_exception_name||'); begin null; end;';
            l_exception_type := gc_integer_exception;
            exception
            when others then
               raise;
            --invalid exception number (positive)
            --TODO add warning for this value
            null;
          end;
        end if;
      end;
      return l_exception_type;
    end;

[jgebal]

@alshib - you can use code block syntax of markdown to make the examples more readable
To do this surround your code with ```sql
select * from dual
```

to get this result

  select * from dual

I've updated your last comment

[jgebal]

@alshib
Thanks for your suggestion, but raising exception at parse time is a no-go.
For install without DDL trigger parsing is done at before suite is run and so this exception would prevent suite execution from success.

In general this would violate the principles of running all run-able tests even if other test suites are in invalid state.

I think the best we can do is to move the exception name/constants validation into suite runtime.

[jgebal]

@alshib
Do you have any more comments or observations on this issue?

[jgebal]

@alshib - can you give a try to the version from #1038 ?

This should resolve all your issues.

[alshib]

Hi,
I do not have any questions and comments.
Right now i can not try version from #1038 but i will try this one later. Thank you!!!