gen-utplsql

This command generates a utPLSQL PL/SQL package for the SQL-based tests in a dbLinter configuration. It considers registered test results to be ignored. The parameters used in the SQL-based tests are replaced by the values defined in the chosen configuration.

This feature is only available with a Professional Subscription.

Syntax

dblinter [<options>] gen-utplsql [<args>]

Options

See Options. Mandatory options are tenantName, userName, accessToken and configName.

Argument Syntax

in Command Line

--<argument>=<value>

in Property File

<argument>=<value>

Arguments

It is not possible to set arguments using environment variables. Beside that, in dbLinter, both arguments and options are treated in the same way. Therefore, they can be used in any order. For example, they can be used before or after the command name. We use options when the semantics are identical for each applicable command. Arguments are used when the semantics change for a command.

• outputName
  This is the relative path from the working directory to the base name of the output file(s). Any missing target directories will be created. Any existing files will be overwritten. The default is packageName, where packageName is replace with the value of the packageName argument.

• specExt
  This is the name of the file extension of the PL/SQL package specification. The default is .sql if the option bodyExt is not defined, otherwise .pks.

• bodyExt
  This is the name of the file extension of the PL/SQL package body. The default is .sql if the option specExt is not defined, otherwise .pkb.

• packageName
  This is the name of the PL/SQL package. The default is tenantName_configName, where tenantName is replaced with the value of the tenantName option and configName is replaced with the value of the configName option.

• suitePath This is the name of the utPLSQL suite-path. The default is dblinter.

Example

dbLinter gen-utplsql \
    --tenantName=Demo \
    --userName=philipp.salvisberg+42@gmail.com \
    --accessToken=*** \
    --configName=Minimal \
    --indent=3 \
    --outputName=dblinter_demo_test \
    --specExt=.pks \
    --bodyExt=.pkb \
    --packageName=dblinter_demo_test \
    --suitePath=dblinter

The console output looks like this:

Using connection to jdbc:oracle:thin:@localhost:1521/freepdb1 with user DBL_READ
utPLSQL package spec written to dblinter_demo_test.pks
utPLSQL package body written to dblinter_demo_test.pkb

Here is the content of the two generated files:

dblinter_demo_test.pks

create or replace package dblinter_demo_test is
   -- The utPLSQL test suite was generated by the 'Demo' tenant using the 'Minimal' configuration.

   --%suite
   --%suitepath(dblinter)

   --%test(Core G-1210: Never create a table without a primary key.)
   procedure core_g_1210;

   --%test(Core G-1310: Never keep database objects in an invalid state.)
   procedure core_g_1310;
end dblinter_demo_test;
/

show errors;

dblinter_demo_test.pkb

create or replace package body dblinter_demo_test is
   -- The utPLSQL test suite was generated by the 'Demo' tenant using the 'Minimal' configuration.

   -- -----------------------------------------------------------------------------------------------------------------
   -- disabled rules
   -- -----------------------------------------------------------------------------------------------------------------
   -- @dbLinter ignore(G-1050) Keep literals to make tests easier to read.
   -- @dbLinter ignore(G-2120) Creating local type to avoid keep everything in a single PL/SQL package.
   -- @dbLinter ignore(G-7210) Exceeding package size because this is a generated PL/SQL package.

   -- -----------------------------------------------------------------------------------------------------------------
   -- local types
   -- -----------------------------------------------------------------------------------------------------------------
   subtype string_type is varchar2(4000 byte);

   type r_result_row_type is record (
      found      boolean,
      identifier string_type,
      message    string_type
   );

   -- -----------------------------------------------------------------------------------------------------------------
   -- forward declarations
   -- -----------------------------------------------------------------------------------------------------------------
   procedure open_test_cursor(
      in_sql     in  clob,
      out_handle out pls_integer
   );

   procedure fetch_test_row(
      in_handle      in  pls_integer,
      out_result_row out r_result_row_type
   );

   procedure close_test_cursor(
      io_handle in out pls_integer
   );

   -- -----------------------------------------------------------------------------------------------------------------
   -- open_test_cursor (private)
   -- -----------------------------------------------------------------------------------------------------------------
   procedure open_test_cursor(
      in_sql     in  clob,
      out_handle out pls_integer
   ) is
      l_col_cnt  pls_integer;
      t_desc_tab sys.dbms_sql.desc_tab2;
      l_col1     string_type;
      l_col2     string_type;
      l_rc       integer;
   begin
      -- A SQL-based test must return 2 or 3 columns. The third column is ignored.
      -- However, the names of the columns in the query are unknown. It is also possible that no names are given.
      -- By convention we expect the first two columns to be of type VARCHAR2(4000 BYTE).
      -- Furthermore, the query may contain plsql_declarations.
      -- As a result, we have to process the query using DBMS_SQL to overcome the limitations of native dynamic SQL.
      out_handle := sys.dbms_sql.open_cursor;
      sys.dbms_sql.parse(out_handle, in_sql, sys.dbms_sql.native);
      sys.dbms_sql.describe_columns2(out_handle, l_col_cnt, t_desc_tab);
      ut.expect(l_col_cnt).to_(be_between(2, 3));
      sys.dbms_sql.define_column(out_handle, 1, l_col1, t_desc_tab(1).col_max_len);
      sys.dbms_sql.define_column(out_handle, 2, l_col2, t_desc_tab(2).col_max_len);
      l_rc := sys.dbms_sql.execute(out_handle);
      ut.expect(l_rc).to_equal(0);
   end open_test_cursor;

   -- -----------------------------------------------------------------------------------------------------------------
   -- fetch_test_row (private)
   -- -----------------------------------------------------------------------------------------------------------------
   procedure fetch_test_row(
      in_handle      in  pls_integer,
      out_result_row out r_result_row_type
   ) is
   begin
      if sys.dbms_sql.fetch_rows(in_handle) > 0 then
         out_result_row.found := true;
         sys.dbms_sql.column_value(in_handle, 1, out_result_row.identifier);
         sys.dbms_sql.column_value(in_handle, 2, out_result_row.message);
      else
         out_result_row.found      := false;
         out_result_row.identifier := null;
         out_result_row.message    := null;
      end if;
   end fetch_test_row;

   -- -----------------------------------------------------------------------------------------------------------------
   -- close_test_cursor (private)
   -- -----------------------------------------------------------------------------------------------------------------
   procedure close_test_cursor(
      io_handle in out pls_integer
   ) is
   begin
      sys.dbms_sql.close_cursor(io_handle);
   end close_test_cursor;

   -- -----------------------------------------------------------------------------------------------------------------
   -- Core G-1210: Never create a table without a primary key.
   -- -----------------------------------------------------------------------------------------------------------------
   procedure core_g_1210 is
      l_handle             pls_integer;
      r_result_row         r_result_row_type;
   begin
      -- act
      open_test_cursor(
         in_sql     => q'§
            with
               function number_of_relevant_columns( -- @dbLinter ignore(G-7460) nondeterministic function
                  in_owner       in varchar2,
                  in_object_name in varchar2
               ) return number is
                  l_number_of_relevant_columns integer := 0;
               begin
                 select count(*)
                   into l_number_of_relevant_columns
                   from dba_tab_cols
                  where owner = in_owner
                    and table_name = in_object_name
                    and hidden_column = 'NO'
                    and virtual_column = 'NO'
                    and column_name not in ('RESID', 'ETAG')
                    and data_type not in ('XMLTYPE', 'JSON', 'BFILE', 'ROWID', 'UROWID', 'BLOB', 'CLOB', 'NCLOB');
                 return l_number_of_relevant_columns;
               end number_of_relevant_columns;
               pk as (
                  select owner, table_name
                    from dba_constraints
                   where constraint_type = 'P'
                     and owner in ('DBL_OWNER')
               ),
               obj as (
                  select owner, object_type, object_name
                    from dba_objects o
                   where object_type in ('TABLE')
                     and owner in ('DBL_OWNER')
                     and object_name not like 'AQ$%'
                     and number_of_relevant_columns(o.owner, o.object_name) > 0
                     and not exists (
                            select 1
                              from dba_tables t
                             where t.owner = o.owner
                               and t.table_name = o.object_name
                               and (t.iot_type is not null or t.temporary = 'Y')
                         )

               )
            select obj.owner || '.' || obj.object_name as identifier,
                   'Missing primary key on ' || lower(obj.object_type) || ' ' || obj.object_name || '.' as message
              from obj
              left join pk
                on pk.owner = obj.owner
                   and pk.table_name = obj.object_name
             where pk.owner is null
            order by obj.owner, obj.object_type, obj.object_name
         §',
         out_handle => l_handle
      );

      -- assert
      <<process_result_rows>>
      loop
         fetch_test_row(
            in_handle      => l_handle,
            out_result_row => r_result_row
         );
         exit process_result_rows when not r_result_row.found;
         ut.expect(
            a_actual  => r_result_row.identifier,
            a_message => r_result_row.message
         ).to_(be_null());
      end loop process_result_rows;
      close_test_cursor(l_handle);
   exception
      when others then
         if l_handle is not null then
            close_test_cursor(l_handle);
         end if;
         raise;
   end core_g_1210;

   -- -----------------------------------------------------------------------------------------------------------------
   -- Core G-1310: Never keep database objects in an invalid state.
   -- -----------------------------------------------------------------------------------------------------------------
   procedure core_g_1310 is
      l_handle             pls_integer;
      r_result_row         r_result_row_type;
   begin
      -- act
      open_test_cursor(
         in_sql     => q'§
            select o.owner || '.' || o.object_name as identifier,
                   initcap(o.object_type) || ' ' || o.owner || '.' || o.object_name || ' is invalid.' as message
              from dba_invalid_objects o
              left join dba_synonyms s
                on s.owner = o.owner
                   and s.synonym_name = o.object_name
             where o.owner in ('DBL_OWNER')
                or (o.owner = 'PUBLIC' and o.object_type = 'SYNONYM' and s.table_owner in ('DBL_OWNER'))
             order by o.owner, o.object_type, o.object_name
         §',
         out_handle => l_handle
      );

      -- assert
      <<process_result_rows>>
      loop
         fetch_test_row(
            in_handle      => l_handle,
            out_result_row => r_result_row
         );
         exit process_result_rows when not r_result_row.found;
         ut.expect(
            a_actual  => r_result_row.identifier,
            a_message => r_result_row.message
         ).to_(be_null());
      end loop process_result_rows;
      close_test_cursor(l_handle);
   exception
      when others then
         if l_handle is not null then
            close_test_cursor(l_handle);
         end if;
         raise;
   end core_g_1310;
end dblinter_demo_test;
/

show errors;