How to run "alter session set something" from the command line?

[rafael-trevisan]

Hi all,

Starting using utPLSLQ-cli here.

Is there a way to run a custom SQL command or script before running the tests from the command-line?

For i.e., I'd like to set a VPD calling some procedure in my db, but would be nice if the parameter for this procedure could be passed in the command-line somehow.

Thx

[pesse]

Hi @rafael-trevisan ,
at the moment there is no possibility for "global" setup/teardown scripts. You can, however, use the %beforeall, %afterall annotations in every test suite to setup your specific test. That's what I do all the time, especially with data-heavy tests.
You can, of course, outsource setup-methods which you need over and over again into a help-package so you just have to call it in the specific test suites.

One real-life example from my projects:

create or replace package ut_testdata_helper as
   procedure setup_session;
end;
/

create or replace package body ut_testdata_helper as
   procedure setup_session 
   as
      begin
         DBMS_SESSION.set_nls('nls_date_format', '''MM/DD/SYYYY HH24:MI:SS''');
         DBMS_SESSION.set_nls('NLS_TIMESTAMP_TZ_FORMAT', '''MM/DD/SYYYY HH24:MI:SS.FF TZH:TZM''');         
         DBMS_SESSION.set_nls('NLS_TIMESTAMP_FORMAT', '''MM/DD/SYYYY HH24:MI:SS.FF''');
         DBMS_SESSION.set_nls('NLS_NUMERIC_CHARACTERS', '''.,''');
      end;
end;
/

create or replace package ut_mytest as
  -- %suite(My test-suite)

...
  
  -- %beforeall
  procedure setup;
end;
/

create or replace package body ut_mytest as
   procedure setup 
   as
      begin
         ut_testdata_helper.setup_session();
      end;
end;
/

@jgebal @Pazus Any thoughts on whether global setup/teardown scripting possibility would be beneficial to cli?

[jgebal]

If your tests share the same suitepath, you can benefit from defining this just once for whole set of test packages (suite)

[pesse]

Hm, does this work with hierarchical suite-paths, too?
e.g.

create or replace package ut_main as
   -- %suite(Main)
   -- %suitepath(main)

   -- %beforeall
   procedure setup;
end;
/

create or replace package ut_sub as
   -- %suite(Sub)
   -- %suitepath(main.ut_main.my.sub)

   -- %test
   procedure my_test;
end;
/

[jgebal]

package common is

--%suite

--%beforeall
procedure setup_sessionh
end;

package my_tests is

--%suite
--%suitepath(common)

....

end;

[rafael-trevisan]

Thank you for such a quick reply.

What if I use Liquibase or Editions and I need to set the “version” I want to test.

Let’s say I am using Editions and I have “release_v1” and “release_v2”. What’d be the best approach to switch between two or more releases?

I mean, it’d be nice if I could test “release_v2” while other developers still work or even test “release_v1”.

Should I have a suite-path for each release/edition and do my alter session there?

Thx!

[jgebal]

Well, given this scenario, it would indeed be beneficial to have some basic support for executing some sort of logon.sql or startup.sql
That should be then a feature in api so it could be used on both cli and maven plugin.

I have no idea what we would be dealing with in terms of implementation cost.

Would you be editioning your unit tests too?
I'm nit sure though if Oracle allows / recommends to have more than one edition that is actively changed.
Is this your current work model or it's just hypothetical?

[rafael-trevisan]

Yeah, I’ll try to make a logon.sql reading from some environment variable, I’ll see if it works.

Running tests through db is fine as I can put my alter session edition there in SQLPlus. The “issue” in my case comes with the cli. A solution could be cli accepting an extra parameter with a SQL script path to run before starting the tests.

Oracle allows to have more than one edition active. Unit tests are editionable as they need to reflect the changes in the code for that edition. This is my current work model. On the same database (but within my own schema) I can be working on “version 1.0” while other developers can be working on different versions on their own schemas (doing alter session set edition).

An unit test for v1.0 is certainly not the same for v2.0 as, let’s say, new methods were create to some pre-existing package, and the unit test “v2.0” needs to cover these new methods.

Versioning and editioning unit tests is okay! Running tests through db is also okay! The only issue is to run using cli as I cannot set the edition I’m working on.

Thx!

[pesse]

I can see the benefit in having startup.sql possibility.
Question is if we should put effort into preventing anything else in there than alter session-statements or if we leave that freedom to the user.
Main functionality (read file, run SQL in batch) would be located in java-api, parameter support has to be done in cli/maven plugIn respectively.

[rafael-trevisan]

👍 for leaving that freedom to the user.

[rafael-trevisan]

I tried to use a --%beforeall annotation to run execute immediate 'alter session set edition = release_1'; but I get an: ORA-38815: ALTER SESSION SET EDITION must be a top-level SQL statement 🤔

[rafael-trevisan]

Tried running with pragma autonomous_transaction; but no luck...

create or replace package test1 is
  --%suite(Set Edition)

  --%beforeall
  procedure setup_version;

  --%test(Check if Edition has been set)
  procedure test1;
end;

create or replace package body  test1 is

  procedure execute_autonomous(a_sql varchar2) is
    pragma autonomous_transaction;
  begin
    if a_sql is not null then
      execute immediate a_sql;
    end if;
    commit;
  end;

  procedure setup_version is
  begin
    execute_autonomous('alter session set edition = release_1');
  end;

  procedure test1 is
    l_version varchar2(100);
  begin
    select sys_context('USERENV', 'SESSION_EDITION_NAME')
      into l_version
      from dual;
      
    ut.expect(l_version).to_equal('release_1');
  end;

end;

[rafael-trevisan]

Got it working through a schema logon trigger. For now, I'm going to set the latest edition as the current one when the user creates a new session. That'll allow proceeding with my work model.

If we need to test some older version we can restore our database to that specific version.

However, a startup.sql option would be a very nice feature.