Long Active Session in PL/SQL Procedures

Question

We are calling PL/SQL Stored Procedures in Java code. Sometimes the package is taking a very long time to return the result. Is it possible to come out of the PL/SQL call and write an log in java?

Please see the below Java code:

long startTime = 0L;
long endTime = 0L;
startTime = System.currentTimeMillis();
logger.info("Starting Time PKGSM2_TOOLS Alerts Package      ------->"+startTime);
CallableStatement statement= con.getConnection().prepareCall("{call  PKGSM2_CONTROL_CHART.PROC_INDICATORCHART_ECHARANGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
statement.executeQuery();
endTime = System.currentTimeMillis();
logger.info("Ending Time PKGSM2_TOOLS Alerts Package ------->"+endTime);
logger.info("Total Package Execution Time Duration of Alerts Package ------->"+(endTime - startTime)+"ms");

In the above code if the PL/SQL package takes more time and the request resides inside the package. Is it possible to exit from the call if the package call takes more than 2 minutes?

And also what will be reason for Long Active Session Inside the PL/SQL Stored procedure?

what is your end target , to prematurely close DB session / process or Java process or both processes?Cancelling stored procedure process via DB driver is DB dependent so do specify your DB software.Also its not easy to answer **Long Active Session** reason unless somebody knows your procedure details as well as your system details well. — Sabir Khan, Jun 01 '16 at 06:33
If we do not get the result from PL/SQL Procedure, after certain time (Ex:2mins) I have to just write in logger that error has occured in Package call. i have to write an error log from java. How to calculate the timer here that is it took more than 2 mins? — Varun, Jun 01 '16 at 06:37
Ok. Could you please suggest some solution for the first question. — Varun, Jun 01 '16 at 06:39
If you just wish to move on and log a message , you will have to move DB calling code in another thread , see [this SO question](http://stackoverflow.com/questions/4252187/how-to-stop-execution-after-a-certain-time-in-java) — Sabir Khan, Jun 01 '16 at 06:42

score 0 · Answer 1 · edited Jun 01 '16 at 07:14

You can use Utl File

Use UTL_FILE.PUT_LINE, how to do file append in pl/sql

CREATE OR REPLACE PROCEDURE SHAREFLE IS
  v_MyFileHandle UTL_FILE.FILE_TYPE;
  BEGIN
       v_MyFileHandle := UTL_FILE.FOPEN('C:\','HELLO.TXT','a');
       UTL_FILE.PUT_LINE(v_MyFileHandle, 'Hello Again for the Second Time! ' || TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM'));
       UTL_FILE.FCLOSE(v_MyFileHandle);
 EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE
                ('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);
           NULL; 
 END;

Long Active Session in PL/SQL Procedures

1 Answers1