Stored Procedure For Main Operation
-- the output has to be a cursor so that Hibernate could deal with it
create or replace procedure test (out_cursor out sys_refcursor, v_context in number)
is
--the following two lines will cause Oracle insert a new row to the v$session_longops view
l_nohint number default dbms_application_info.set_session_longops_nohint;
l_rindex number default l_nohint;
--Oracle maintains this value
l_slno number;
table_id number;
name_nonsense varchar(20);
v_i number;
begin
table_id := 9;
-- a very long operation
for i in 1 .. 400000
loop
name_nonsense := 'nonsense' || to_char(i);
v_i := i;
insert into nonsense
values (nonsense_seq.nextval, name_nonsense, v_i, sysdate);
-- calling this procedure will update the respective row in the v$session_longops view
dbms_application_info.set_session_longops
( rindex => l_rindex,
slno => l_slno,
op_name => 'insertion of 400000 rows',
target => table_id,
target_desc => 'inserting nonsense',
context => v_context,
sofar => i,
totalwork => 400000,
units => 'rows'
);
end loop;
open out_cursor for
select 'one' a, 'two' b from dual;
exception
when others then
open out_cursor for
select 'one1' a, 'two2' b from dual;
end test;
Progress Tracking Procedure
--although bloated, this procedure seems to be doing what it should
--although bloated, this procedure seems to be doing what it should
create or replace procedure updateProgress(ref_out out sys_refcursor, v_context in number)
is
v_serialnumber number;
v_remainingtime number;
v_elapsedtime number;
v_accomplishedpercentage number;
v_found boolean;
begin
v_found := false;
select
serial#, time_remaining , elapsed_seconds , (100 * sofar) / totalwork
into v_serialnumber, v_remainingtime, v_elapsedtime, v_accomplishedpercentage
from v$session_longops
where target = 9 and target_desc = 'inserting nonsense' and context = v_context and time_remaining > 0;
v_found := true;
open ref_out for
select
v_serialnumber serialnumber,
v_remainingtime remainingtime,
v_elapsedtime elapsedtime,
v_accomplishedpercentage accomplishedpercentage
from dual;
if ref_out%isopen then
insert into nonsenselog values ('open' || to_char(v_elapsedtime), sysdate);
else
insert into nonsenselog values ('closed' || to_char(v_elapsedtime), sysdate);
end if;
open ref_out for
select
v_serialnumber serialnumber,
v_remainingtime remainingtime,
v_elapsedtime elapsedtime,
v_accomplishedpercentage accomplishedpercentage
from dual;
exception
when too_many_rows then
insert into nonsenselog values ('too many rows' || to_char(v_elapsedtime), sysdate);
when no_data_found then
if v_found then
v_serialnumber := -1;
v_remainingtime := -1;
v_elapsedtime := -1;
v_accomplishedpercentage := -1;
else
v_serialnumber := 0;
v_remainingtime := 0;
v_elapsedtime := 0;
v_accomplishedpercentage := 0;
end if;
open ref_out for
select
v_serialnumber serialnumber,
v_remainingtime remainingtime,
v_elapsedtime elapsedtime,
v_accomplishedpercentage accomplishedpercentage
from dual;
when others then
insert into nonsenselog values ('error' || to_char(v_elapsedtime), sysdate);
end updateProgress;
Progress.java
//this entity will represent the output produced by invocation of the progress tracking procedurepackage com.sandahaung.testsp.entities;
import java.io.Serializable;
import javax.persistence.*;
/**
*
* @author Sandah
*/
@NamedNativeQueries({
@NamedNativeQuery(name = "updateProgress",
query = "call updateProgress(?, :context)",
hints = {
@QueryHint(name = "org.hibernate.callable", value = "true"),
@QueryHint(name = "org.hibernate.cacheable", value = "false")
},
resultSetMapping = "progress")
})
@SqlResultSetMapping(name = "progress", entities = {
@EntityResult(entityClass = com.sandahaung.testsp.entities.Progress.class, fields = {
@FieldResult(name = "serialNumber", column = "serialnumber"),
@FieldResult(name = "remainingTime", column = "remainingtime"),
@FieldResult(name = "elapsedTime", column = "elapsedtime"),
@FieldResult(name = "accomplishedPercentage", column = "accomplishedPercentage")
})
})
@Entity
public class Progress implements Serializable {
@Id
private int serialNumber;
private int remainingTime;
private int elapsedTime;
private double accomplishedPercentage;
public double getAccomplishedPercentage() {
return accomplishedPercentage;
}
public void setAccomplishedPercentage(double accomplishedPercentage) {
this.accomplishedPercentage = accomplishedPercentage;
}
public int getElapsedTime() {
return elapsedTime;
}
public void setElapsedTime(int elapsedTime) {
this.elapsedTime = elapsedTime;
}
public int getSerialNumber() {
return serialNumber;
}
public void setSerialNumber(int serialNumber) {
this.serialNumber = serialNumber;
}
public int getRemainingTime() {
return remainingTime;
}
public void setRemainingTime(int remainingTime) {
this.remainingTime = remainingTime;
}
}
ProgressUpdater.java
package com.sandahaung.testsp;
import com.sandahaung.testsp.entities.Progress;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.FlushModeType;
import javax.persistence.Persistence;
import javax.persistence.Query;
/**
*
* @author Sandah
*/
public class ProgressUpdater implements Runnable {
int context;
private EntityManagerFactory factory;
private EntityManager em;
public ProgressUpdater() {
System.out.println("constructor run");
}
public ProgressUpdater(int context) {
this.context = context;
}
public void run() {
Progress progress = null;
System.out.println("new thread entered...");
try {
System.out.println("code 1");
Thread.sleep(5000);
} catch (InterruptedException ex) {
System.out.println("ex 1");
//Logger.getLogger(ProgressUpdater.class.getName()).log(Level.SEVERE, null, ex);
}
do {
try {
System.out.println("code 2");
Thread.sleep(1000);
} catch (InterruptedException ex) {
System.out.println("ex 2");
//Logger.getLogger(ProgressUpdater.class.getName()).log(Level.SEVERE, null, ex);
}
progress = updateProgress();
System.out.println("Elapsed time: " + progress.getElapsedTime());
//System.out.println("Remaining time: " + progress.getRemainingTime());
//System.out.println("Accomplished percentage: " + progress.getAccomplishedPercentage());
} while (progress.getAccomplishedPercentage() != -1);
em.close();
}
public Progress updateProgress() {
Progress progress = findProgress(context);
return progress;
}
public synchronized Progress findProgress(int context) {
if (factory == null)
factory = Persistence.createEntityManagerFactory("com.sandahaung_testsp_jar_1.0-SNAPSHOTPU");
if (em == null)
em = factory.createEntityManager();
Query query = em.createNamedQuery("updateProgress");
query.setParameter("context", context);
query.setFlushMode(FlushModeType.AUTO);
Progress progress = (Progress) query.getSingleResult();
return progress;
}
}
App.java
package com.sandahaung.testsp;
import com.sandahaung.testsp.entities.OneTwo;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
public class App {
public static void main(String[] args) {
int context = 1;
EntityManagerFactory emf = Persistence.createEntityManagerFactory("com.sandahaung_testsp_jar_1.0-SNAPSHOTPU");
EntityManager em = emf.createEntityManager();
Query query = em.createNamedQuery("testCall");
query.setParameter("context", context);
new Thread(new ProgressUpdater(context)).start();
OneTwo ot = (OneTwo) query.getSingleResult();
System.out.println("a: " + ot.getA());
System.out.println("b: " + ot.getB());
em.close();
}
}
