Oracle PL_SQL set execution timeout

Question

I am using Oracle 12c Enterprise (12.1.0.2.0) and have a package which creates queries using dynamic SQL, according to some rules. The dynamic SQL query is then executed by opening a cursor on it, returning the cursor to the application. I need to add a query execution timeout also dynamically, so that depending on the type of SQL generated, the query may run for longer or shorter times, hence setting this value on the JDBC connection string/connection object is not a solution. I have tried using "ALTER SESSION SET PLSQL_TIMEOUT" command, prior to opening the cursor, but it fails stating that PLSQL_TIMEOUT is invalid (even though it is documented). I have searched for HINTs but with no luck. Does anyone know how to limit query execution time on this particular case? Is there any way? Thanks in advance!

Answer 1

As far as I can tell, there's no (simple) way to do that.

You might

• create a logging table which contains some data about query you're going to run (username, start time, expected duration, ...)
  • update that row (set its end time column) once query is done
• create a database job which runs frequently and checks whether current time is greater than [start time + expected duration] for all rows whose end time hasn't been set

  • if so, kill that session. It means that you should have appropriate privileges, because you should first query v$session and then alter system which is not what many users are allowed to do

    SELECT sid, serial# FROM v$session WHERE USERNAME = 'THAT_USER';
    

    and then (using info you collected above)

    ALTER SYSTEM KILL SESSION 'sid, serial#';