PL/SQL logger

PL'N'SQL LOGGER

Tool for DB comparison, mainly table contents (e.g. for regression tests, reconciliations)

BDComparatista

Function, to sort list of values

create or replace function TOKENSORT(
      P_INPUT                          varchar2,
      P_DELIMITER                      char := ' '
) return varchar2 is
      C_TOKEN_CLASS                    constant varchar2(4000) :=
         '[^' || P_DELIMITER || ']';
      C_INPUT_ESCAPED                  varchar2(32767) :=
         replace(P_INPUT, '''', '''''');

      V_POSITION                       integer := 1;
      V_TOKEN                          varchar2(4000);
      V_SORTED_TOKENS                  varchar2(4000) := 'to be fixed';
      V_STATEMENT                      varchar2(4000);
begin
      -- get number of tokens
      loop
            V_TOKEN := regexp_substr(P_INPUT, C_TOKEN_CLASS || '+', 1, V_POSITION);
            exit when V_TOKEN is null;
            V_POSITION := V_POSITION + 1;
      end loop;
      -- dbms_output.put_line('V_POSITION: ' || V_POSITION);
      -- assemble statement
      V_STATEMENT := 'select trim(' || chr(10);
      for I in 1..V_POSITION loop
            -- dbms_output.put_line('I: ' || I);
            V_STATEMENT := V_STATEMENT ||
               '              max(case when rownum = ' || I || ' then TOKEN end)';
            if I != V_POSITION then
               V_STATEMENT := V_STATEMENT ||
                  ' || ''' || P_DELIMITER || ''' ||' || chr(10);
            else
               V_STATEMENT := V_STATEMENT || ')' || chr(10);
            end if;
      end loop;
      V_STATEMENT := V_STATEMENT ||
         '  from (' || chr(10) ||
         '           select 1 as ID' || chr(10) ||
         '                , regexp_substr(''' || C_INPUT_ESCAPED ||
         ''' , ''' || C_TOKEN_CLASS || '+'', 1, rownum) as TOKEN' || chr(10) ||
         '             from DUAL' || chr(10) ||
         '            where rownum <= ' || V_POSITION || chr(10) ||
         '            connect by level <= length(regexp_replace(''' ||
         C_INPUT_ESCAPED || ''' , ''(' || C_TOKEN_CLASS ||
         ')+|.'', ''\1''))' || chr(10) ||
         '            order by 2' || chr(10) ||
         '        )' || chr(10) ||
         ' group by ID';
      dbms_output.put_line(V_STATEMENT);
      execute immediate V_STATEMENT into V_SORTED_TOKENS;
      return V_SORTED_TOKENS;
end;
/

Row generator (sequence)

For 256 values

select ROWNUM as SEQUENCEID
   from DUAL
  connect by level < 257
  order by SEQUENCEID asc;