Select for update skip locked example

#Session 1

SQL> delete del1;

0 rows deleted.

SQL>
SQL> insert into del1(n) (select level from dual connect by level < 100);

99 rows created.

SQL> var c1 refcursor;
SQL>
SQL> begin
  2    open :c1 for select n from del1 order by n for update skip locked;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> set serveroutput on;
SQL>
SQL> declare
  2    n number;
  3  begin
  4    fetch :c1 into n;
  5    dbms_output.put_line(to_char(n));
  6  end;
  7  /
1

PL/SQL procedure successfully completed.

#Session 2

SQL> var c1 refcursor;
SQL>
SQL> begin
  2    open :c1 for select n from del1 order by n for update skip locked;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> set serveroutput on;
SQL>
SQL> declare
  2    n number;
  3  begin
  4    fetch :c1 into n;
  5    dbms_output.put_line(to_char(n));
  6  end;
  7  /
2

PL/SQL procedure successfully completed.

#Session 1

SQL> declare
  2    n number;
  3  begin
  4    fetch :c1 into n;
  5    dbms_output.put_line(to_char(n));
  6  end;
  7  /
3

PL/SQL procedure successfully completed.

#Session 2

SQL> declare
  2    n number;
  3  begin
  4    fetch :c1 into n;
  5    dbms_output.put_line(to_char(n));
  6  end;
  7  /
4

PL/SQL procedure successfully completed.

How to get all classes in package

package saphsys.tayak;

import java.io.File;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

public class PackageClasses {
    
    private static final char PKG_SEPARATOR = '.';
    private static final String CLASS_FILE_SUFFIX = ".class";
    private static final char DIR_SEPARATOR = '/';

    public static List find(String scannedPackage, Class target) throws ClassNotFoundException {
        String scannedPath = scannedPackage.replace(PKG_SEPARATOR, DIR_SEPARATOR);
        List commands = new ArrayList<>();
        URL root = Thread.currentThread().getContextClassLoader().getResource(scannedPath);
        assert root != null;
        File[] files = new File(root.getFile()).listFiles((File dir, String name) -> name.endsWith(CLASS_FILE_SUFFIX));
        for (File file : files) {
            String className = file.getName().replaceAll(".class$", "");
            Class cls = Class.forName(scannedPackage + "." + className);
            if(target.isAssignableFrom(cls)) {
                commands.add(cls);
            }
        }
        return commands;
    }    
}

How to select full periods

with tbl as (
select 1 n, to_date('01.01.2017', 'dd.mm.yyyy') s, to_date('10.01.2017', 'dd.mm.yyyy') e from dual union all
select 2 n, to_date('05.01.2017', 'dd.mm.yyyy') s, to_date('15.01.2017', 'dd.mm.yyyy') e from dual union all
select 3 n, to_date('11.01.2017', 'dd.mm.yyyy') s, to_date('17.01.2017', 'dd.mm.yyyy') e from dual union all

select 4 n, to_date('01.02.2017', 'dd.mm.yyyy') s, to_date('10.02.2017', 'dd.mm.yyyy') e from dual union all
select 5 n, to_date('05.02.2017', 'dd.mm.yyyy') s, to_date('15.02.2017', 'dd.mm.yyyy') e from dual union all
select 6 n, to_date('11.02.2017', 'dd.mm.yyyy') s, to_date('17.02.2017', 'dd.mm.yyyy') e from dual)

select distinct CONNECT_BY_ROOT s s, max(e) over (partition by CONNECT_BY_ROOT s) e from tbl t1
start with not exists (select 1 from tbl t2 where t1.s between t2.s and t2.e and t1.n <> t2.n)
connect by prior n <> n and prior e >= s and prior e <= e;

S           E
----------- -----------
01.01.2017  17.01.2017
01.02.2017  17.02.2017

Oracle SYS_REFCURSOR to XMLTYPE

create or replace function CUR2XML(pCur in out sys_refcursor, pCloseCursor in boolean default true) return xmltype
as
/*

    #1 Performance
    **************
    with setNLS/restoreNLS: 3,4
    without setNLS/restoreNLS: 2,75

    Script: 

    declare
      x xmltype;
      dSeconds number;
      dSeconds2 number := 0;
      testc sys_refcursor;
    begin
      for i in 1..1000
      loop
        open testc for
          select dbms_random.value num, dbms_random.string('p', 16) str, sysdate + dbms_random.value dat from dual connect by level < 10;
        dSeconds := dbms_utility.get_time/100;
        x := CUR2XML(testc);
        dSeconds := dbms_utility.get_time/100 - dSeconds;
        dSeconds2 := dSeconds2 + dSeconds;
      end loop;
      dbms_output.put_line(dSeconds2);
    end;
    /

    #2 Oracle bug fixes (magic)
    ***************************
    https://asktom.oracle.com/pls/asktom/f?p=100:11:::NO:RP:P11_QUESTION_ID:9534431900346311764

*/
  cXML_NLS_DATE_FORMAT         constant varchar2(23) := 'yyyy-mm-dd"T"hh24:mi:ss';
  cXML_NLS_TIMESTAMP_FORMAT    constant varchar2(27) := 'yyyy-mm-dd"T"hh24:mi:ss.ff9';
  cXML_NLS_TIMESTAMP_TZ_FORMAT constant varchar2(34) := 'yyyy-mm-dd"T"hh24:mi:ss.ff9tzh:tzm';
  
  type tNLS_PARAMETERS is table of v$nls_parameters.value%type index by v$nls_parameters.parameter%type;
  vSTORED_NLS_PARAMETERS tNLS_PARAMETERS;
  
  vRESULT xmltype;
  vHANDLE dbms_xmlgen.ctxHandle;
  
  procedure setNLS as
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    vNEEDCOMMIT boolean := false;
  begin
    for rec in (
      select /*+ cursor_sharing_exact */ parameter,value,
        decode(parameter, 
          'NLS_DATE_FORMAT', cXML_NLS_DATE_FORMAT,
          'NLS_TIMESTAMP_FORMAT', cXML_NLS_TIMESTAMP_FORMAT,
          'NLS_TIMESTAMP_TZ_FORMAT', cXML_NLS_TIMESTAMP_TZ_FORMAT,
          null) newvalue
      from v$nls_parameters
      where parameter in ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_TIMESTAMP_TZ_FORMAT'))
    loop
      if rec.value <> rec.newvalue then
        vSTORED_NLS_PARAMETERS(rec.parameter) := rec.value;
        dbms_session.set_nls(rec.parameter, '''' || rec.newvalue || '''');
        vNEEDCOMMIT := true;
      end if;
    end loop;
    
    if vNEEDCOMMIT then
      commit;
    end if;
  end;
  
  procedure restoreNLS as
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    vNEEDCOMMIT boolean := false;
    vPARAMETER  v$nls_parameters.parameter%type;
  begin
    vPARAMETER := vSTORED_NLS_PARAMETERS.first;
    
    while vPARAMETER is not null
    loop
      dbms_session.set_nls(vPARAMETER, '''' || vSTORED_NLS_PARAMETERS(vPARAMETER) || '''');
      vNEEDCOMMIT := true;
      vPARAMETER := vSTORED_NLS_PARAMETERS.next(vPARAMETER);
    end loop;
 
    if vNEEDCOMMIT then
      commit;
    end if;
  exception
    when NO_DATA_FOUND then
      null;  
  end;
  
begin
  setNLS;
  
  vHANDLE := dbms_xmlgen.newContext(pCur);
  begin
    dbms_xmlgen.restartQuery(vHANDLE);
    vRESULT := dbms_xmlgen.getXMLType(vHANDLE);
    dbms_xmlgen.closeContext(vHANDLE);
  exception
    when OTHERS then
      dbms_xmlgen.closeContext(vHANDLE);
      raise;
  end;
  
  restoreNLS;
  
  if pCloseCursor then
    close pCur;
  end if;
  
  return vRESULT;
exception
  when others then
    restoreNLS;
    raise;
end;

  

Use parallel execution

-- Stored procedure to be run from the job: Uses pipes for job synchronization, executes PROC_DELETE_TEST_BONUS.
CREATE OR REPLACE PROCEDURE PROC_DELETE_TEST_BONUS_CONCUR(in_pipe_name IN VARCHAR2,
    in_job IN VARCHAR2)
IS
 flag INTEGER;
BEGIN
  -- Execute actual procedure
  ibm_odm_test.init;
--  logevent('dgv_pll', 'z');

 -- Signal completion
 -- Use the procedure to put a message in the local buffer. 
  DBMS_PIPE.PACK_MESSAGE(SYSDATE ||': Success ' ||in_job);
  -- Send message, success is a zero return value.
  flag := DBMS_PIPE.SEND_MESSAGE(in_pipe_name);
EXCEPTION
WHEN OTHERS THEN
  -- Signal completion
  -- Use the procedure to put a message in the local buffer. 
  DBMS_PIPE.PACK_MESSAGE(SYSDATE ||':Failed ' || in_job);
  -- Send message, success is a zero return value.
  flag := DBMS_PIPE.SEND_MESSAGE(in_pipe_name);
  RAISE;
END;
/


--
-- Run Jobs
--
DECLARE
  timestart NUMBER;
  duration_insert NUMBER;
  jobs_amount NUMBER := 0;
  retval INTEGER;
  message     VARCHAR2(4000);
  rows_amount NUMBER;

/** Create and define a program that calls PROG_DELETE_TEST_BONUS_CONCUR to be run as job. */
PROCEDURE create_prog_delete_test_bonus
IS  
BEGIN
  -- define new in each run in order to ease development. TODO Once it works, no need to redefine for each run!
  dbms_scheduler.drop_program(program_name => 'PROG_DELETE_TEST_BONUS_CONCUR', force=> TRUE);

  dbms_scheduler.create_program ( program_name => 'PROG_DELETE_TEST_BONUS_CONCUR', program_action =>
  'PROC_DELETE_TEST_BONUS_CONCUR', program_type => 'STORED_PROCEDURE', number_of_arguments => 2,
  enabled => FALSE );

  dbms_scheduler.DEFINE_PROGRAM_ARGUMENT( program_name => 'PROG_DELETE_TEST_BONUS_CONCUR',
  argument_position => 1, argument_name => 'in_pipe_name', argument_type => 'VARCHAR2');
  dbms_scheduler.DEFINE_PROGRAM_ARGUMENT( program_name=>'PROG_DELETE_TEST_BONUS_CONCUR',
  argument_position => 2, argument_name => 'in_job', argument_type => 'VARCHAR2');

  dbms_scheduler.enable('PROG_DELETE_TEST_BONUS_CONCUR');
END;

/** "Forks" a job that runs PROG_DELETE_TEST_BONUS_CONCUR */
PROCEDURE RUN_TEST_BONUS_JOB(
    in_pipe_name IN VARCHAR2,
    in_job    IN VARCHAR2,
    io_job_amount IN OUT NUMBER)
IS
  jobname VARCHAR2(100);
BEGIN
  jobname:=DBMS_SCHEDULER.GENERATE_JOB_NAME;

  dbms_scheduler.create_job(job_name => jobname, program_name =>
  'PROG_DELETE_TEST_BONUS_CONCUR');
  dbms_scheduler.set_job_argument_value(job_name => jobname, argument_name => 
  'in_pipe_name' , argument_value => in_pipe_name);
  dbms_scheduler.set_job_argument_value(job_name => jobname, argument_name => 
  'in_job' , argument_value => in_job);
  dbms_output.put_line(SYSDATE || ': Running job: '|| jobname);

  dbms_scheduler.RUN_JOB(jobname, false );
  io_job_amount:= io_job_amount+1;
END;


-- Anonymous "Main" block
BEGIN 
  delete histerr where module='ibm_odm_test';
  
   ibm_odm_test.init;
 create_prog_delete_test_bonus;

  -- Define private pipe
  retval := DBMS_PIPE.CREATE_PIPE(DBMS_PIPE.UNIQUE_SESSION_NAME, 100, FALSE);  
  dbms_output.put_line(SYSDATE || ': Created pipe: ''' || DBMS_PIPE.UNIQUE_SESSION_NAME || ''' returned ' ||retval);

  timestart := dbms_utility.get_time();

  -- start concurrent processing 
  RUN_TEST_BONUS_JOB(DBMS_PIPE.UNIQUE_SESSION_NAME, 'A', jobs_amount);
  RUN_TEST_BONUS_JOB(DBMS_PIPE.UNIQUE_SESSION_NAME, 'B', jobs_amount);
  RUN_TEST_BONUS_JOB(DBMS_PIPE.UNIQUE_SESSION_NAME, 'C', jobs_amount);

  -- "Barrier": Wait for all jobs to finish
  for i in 1 .. jobs_amount loop
    -- Reset the local buffer.
    DBMS_PIPE.RESET_BUFFER;

    -- Wait and receive message. Timeout after an hour.
    retval := SYS.DBMS_PIPE.RECEIVE_MESSAGE(SYS.DBMS_PIPE.UNIQUE_SESSION_NAME, 3600);
    -- Handle errors: timeout, etc.
    IF retval != 0 THEN
      raise_application_error(-20000, 'Error: '||to_char(retval)||' receiving on pipe. See Job Log in table user_scheduler_job_run_details');
    END IF;
    -- Read message from local buffer.
    DBMS_PIPE.UNPACK_MESSAGE(message);
    dbms_output.put_line(SYSDATE || ': Received message on '''|| DBMS_PIPE.UNIQUE_SESSION_NAME ||''' (Status='|| retval ||'): ' || message); 
  end loop;

  retval :=DBMS_PIPE.REMOVE_PIPE(DBMS_PIPE.UNIQUE_SESSION_NAME);
  dbms_output.put_line(SYSDATE || ': REMOVE_PIPE: ''' || DBMS_PIPE.UNIQUE_SESSION_NAME || ''' returned: ' ||retval);
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SYSDATE ||  SUBSTR(SQLERRM, 1, 1000) || ' ' ||
                                          SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 1000));
  retval := DBMS_PIPE.REMOVE_PIPE(DBMS_PIPE.UNIQUE_SESSION_NAME);
  dbms_output.put_line(SYSDATE || ': REMOVE_PIPE: ''' || DBMS_PIPE.UNIQUE_SESSION_NAME || ''' returned: ' ||retval);

  -- Clean up in case of error
--  PROC_DELETE_TEST_BONUS('A');
--  PROC_DELETE_TEST_BONUS('B');
--  PROC_DELETE_TEST_BONUS('C');
  RAISE;
END;
/