you can put resource limit on the user, but then it will terminate the call:

SQL> CREATE PROFILE LIMIT_USER_QUERY_TIME LIMIT CPU_PER_CALL 1000 ;


Profile created.


SQL> alter user kkishore profile LIMIT_USER_QUERY_TIME ;


User altered.


SQL> connect kkishore

Enter password:

Connected.

SQL> exec loop null ; end loop ;

BEGIN loop null ; end loop ; END;


*

ERROR at line 1:

ORA-02393: exceeded call limit on CPU usage



SQL>

I made a simple try, whether it works for you?
Based on passing number of seconds in procedure...
 
SQL> create or replace function timeout
  2  return number is
  3  begin
  4   return to_number(to_char(sysdate,'SSSSS'));
  5  end timeout;
  6  / 
 
Function created.
 
SQL> create or replace procedure tcal( t in number ) as
  2  a  number:=1;
  3  st number:=to_number(to_char(sysdate,'SSSSS'));
  4  x varchar2(12);
  5  y varchar2(12);
  6  begin
  7  while(a<99999999) loop
  8  dbms_output.enable(100000); ---> Just to make loop busy...
  9  a:=a+1;
 10  exit when (timeout - st)>=t;
 11  end loop;
 12  x:=to_char(trunc(sysdate)+st/(24*60*60),'HH:MI:SS AM');
 13  dbms_output.put_line('       Started: '||x);
 14  y:=to_char(trunc(sysdate)+timeout/(24*60*60),'HH:MI:SS AM');
 15  dbms_output.put_line('       Ended:   '||y);
 16  dbms_output.put_line(timeout-st||' seconds reached...');
 17  end ;
 18  / 
 
Procedure created.
 
SQL> execute tcal(5);
Started: 12:19:24 PM
Ended:   12:19:29 PM
5 seconds reached...
 
PL/SQL procedure successfully completed.
 
SQL> 

Message was edited by:
Jameel

Ignoring resource profiles for the moment and focusing specifically on the traditional way this will be done in a database client process.

Not in PL/SQL the way you are thinking of. Yes, it is possible from a client that can do multithreading and deals directly with the (complex) OCI.

In that case you would spawn a thread to run the SQL and from the main thread wait for it to complete. When it does not do it in time, you use an OCI call to "interrupt" the Oracle process servicing that query and tell it to abort it. SQL*Plus for example use this technique when you press CTRL-BREAK when running a query.

However, the biggest advantage of PL/SQL is not having to deal with the OCI. So this OCI call cannot be made. Nor does PL/SQL support POSIX-like threads. So there's the problem.

There are ways to work around this. You can use the Unix-like fork() model in PL/SQL, spawning "child" processes via DBMS_JOB. IPC (Inter Process Communication) between parent and child processes can be done using DBMS_LOCK, DBMS_PIPE, AQ or even a plain vanilla Oracle table to serve as the IPC "shared memory segment".

The parent process can kill a child using the 'ALTER SYSTEM' command - not a nice way to interrupt the process, but with PL/SQL there are no interrupts we can code. So The Big Hammer it is.

So what you want to do in PL/SQL is not impossible - but needs some careful thought and code to implement a sensible fashion that works effectively and produces the desired results.

Yes, but if your while loop was running a SQL statement that took enormous amount of time and you wanted to break ...........

How would your code accomplish that?

Jameel,

your example is flawed!

Can you image one SQL-statement that stops "looping" your loop because SQL-statement took enormous amount of time?

So your counter can't count => Line 10

exit when (timeout - st)>=t;

will be never reached...

Cheers!

Well, instead of the fork() command you would use on Unix, you use the DBMS_JOB.Submit command.

The "child" process (DBMS_JOB session running as Oracle process Jnnnn) does not inherit the data segment of the parent - but this should not be a problem. You simply directly call the client procedure you want to execute via DBMS_JOB.

If you want to control the number of client jobs that can execute, you can implement a user defined resource via DBMS_LOCK and the number of locks/latches that may exist on it. Each DBMS_JOB submission locks it - and unlocks it when terminating.

If you want some form of IPC between parent and child processes, Oracle provides a range of IPC-like features. There is DBMS_PIPE. There is advanced queues. Even a normal table can suffice for the clients to insert "messages" into for the parent to process.

The basic architecture of multithreading is thus the same. Just the implementation differs slightly.

Oracle is a lot like an operating system. It has a lot of the common features that you'll find in operating systems. Many of these features are published as APIs via Oracle supplied PL/SQL packages (like DBMS_PIPE, DBMS_SCHEDULE, UTL_TCP, etc) - in other words you have:
- a TCP client socet API
- a job queue API
- a web browser
- an e-mailer
- pipes (between database sessions)
- message queues
- a "ftp" like API (between databases)
- a file system I/O API
- an integrated HTTP server daemon
etc.

Even better, PL/SQL and Oracle itself (at SQL and db core level) not only supports object classes, but has the ability to persist these as columns in tables.

I do 99% of all my back-end code inside Oracle. The few times that I have to do stuff outside Oracle is when dealing with pure o/s stuff - which I do using shell scripts.

Oracle is a very capable application platform. As Oracle Application Express clearly shows.

Hi,

Did you find any feasible solution for this issue? If so, can you please share it with all. We are also looking for solution for the same issue.

thanks,
Prashant