Problem calling DBMS_LOCK(dot)SLEEP from Forms

Question:
Steven, we have been using Oracle Forms 5 and Oracle Server 7.3.4. In our application we use DBMS_LOCK.SLEEP to suspend Forms for a few seconds. However, after recently upgraded to Oracle9i (still using Forms 5), we now find that the forms using DBMS_LOCK.SLEEP no longer compile (error is on the DBMS_LOCK statement). Any suggestions?
Answer:
Tom, the problem is undoubtedly not in the DBMS_LOCK package, but rather in your ability to call DBMS_LOCK.SLEEP altogether and/or directly from your older version of Oracle Forms.
The first thing you should do is ensure that in your Oracle9i instance your user schema has the authority to run DBMS_LOCK; this authority is not granted by default when the instance is set up.
You can check availability of DBMS_LOCK from SQL*Plus directly. You should test access not only with an anonymous block as follows:
BEGIN
DBMS_LOCK.sleep (1);
END;
/
but also from within a stored procedure, such as:
CREATE OR REPLACE PROCEDURE p
IS
BEGIN
DBMS_LOCK.sleep (1);
END;
/
If you are able to run the anonymous block as well as compile the stored procedure, you can then move on to addressing the problem of accessing that code from Oracle Forms.
The problem here may be that your older version of Oracle Forms is having problems calling backend packages. If you cannot upgrade your Oracle Forms installation, I suggest that you create your own procedure, which will in turn call DBMS_LOCK.SLEEP. Then see if you can successfully call that program from Oracle Forms. Here is such a procedure:
CREATE OR REPLACE PROCEDURE my_sleep (seconds IN NUMBER)
IS
BEGIN
DBMS_LOCK.SLEEP (seconds);
END my_sleep;
/
Assuming you are able to call any backend code with your configuration, this approach should resolve the problem.