A good question would be : why did you want wait 10 sec. before inserting ?

Nicolas.

I don't understand what you mean by something taking more time than a timer duration?

Originally you asked
> can i let a procedure wait for a sepcific time( 10 seconds ) before inserting values into a table

Are you now asking how to set a limit on how long to wait for a procedure to execute?

Yeah, as I said nice thoughts, but just that!

Reason?

Well, this time I will use Billy explanation just to avoid absence of belief in Faust statements:

"Grants and privs assigned via a role is not applicable inside named
PL/SQL (e.g. PL/SQL procs, packages and functions).

Reason: any changes to the privs for a role will invalidate all PL/SQL code
relying on that role - this can cause a major fubar in the database.

Solution: privs required for named PL/SQL code must be assigned explicitly and not via a role.

Roles privs work via anonymoys PL/SQL blocks as these are processed at runtime"

Cheers!

Differentiation between ideal solution and real life solution (also sometimes called practical solution).

    ...

    loop

      ndiff := (sysdate - dstart)*86400;

      exit when ndiff >= 10;

    end loop;

    ...

Geez, I've already seen such code elsewhere but it still sends chills down my spine.
Please - begging you - don't be tempted to use it in your production systems.
Because such code will overheat your CPU.

NEVER DO THAT.

Well, if you don't believe me, let's test it and measure:

SQL> alter session set tracefile_identifier = SLEEP;



Session altered.



SQL> alter session set sql_trace = TRUE;



Session altered.



SQL> -- Awful code

SQL> declare

  2    dstart date := sysdate;

  3    ndiff  number;

  4  begin

  5    loop

  6      ndiff := (sysdate - dstart)*86400;

  7      exit when ndiff >= 10;

  8    end loop;

  9  end;

 10  /



PL/SQL procedure successfully completed.



SQL> -- DBMS_LOCK code

SQL> exec dbms_lock.sleep(10)



PL/SQL procedure successfully completed.



SQL> alter session set sql_trace = FALSE;



Session altered.

And now, here is what TKPROF says:

BEGIN dbms_lock.sleep(10); END;


call     count       cpu    elapsed       disk      query    current        rows


------

----------

----------


Parse        1      0.03       0.02          0         12          0           0

Execute      1      0.00       9.98          0          0          0           1

Fetch        0      0.00       0.00          0          0          0           0


------

----------

----------


total        2      0.03      10.01          0         12          0           1



Misses in library cache during parse: 1

Optimizer mode: CHOOSE

Parsing user id: 72  

********************************************************************************



declare

  dstart date := sysdate;

  ndiff  number;

begin

  loop

    ndiff := (sysdate - dstart)*86400;

    exit when ndiff >= 10;

  end loop;

end;



call     count       cpu    elapsed       disk      query    current        rows


------

----------

----------


Parse        1      0.00       0.00          0          0          0           0

Execute      1      9.71       9.70          0          0          0           1

Fetch        0      0.00       0.00          0          0          0           0


------

----------

----------


total        2      9.71       9.71          0          0          0           1



Misses in library cache during parse: 1

Optimizer mode: CHOOSE

Parsing user id: 72  

********************************************************************************

ELAPSED time is near 10 sec in both cases, but look at CPU time.
DBMS_LOCK.SLEEP consumes no CPU time (at least, it can't be measured) whereas LOOP ... END LOOP can really overheat your CPU.

If DBMS_LOCK (or some wrapper routine for DBMS_LOCK.SLEEP) can not be granted due to DBA whims - well, show this testcase to him and try to explain things. And if he still doesn't get things right - I'm afraid it's better to get rid of such DBA.

Cheers.

What "What about it?"?

Precisely. What about that?

I have every confidence that there is a valid point being made here, and that it will turn out to be a good one and well worth the struggle.

Hmmm. Are you saying that in Central Europe if I walk in the rain without an umbrella I won't get wet? Surely not.

With databases there is absolute truth. Predicate logic assures us of that. Relative truth is a bug.

Which isn't to say that there are not workplaces where all sorts of inefficient and counterproductive practices are rife. Of course, people are stupid and lazy all over the world. So your problem (as I understand it) is political (how do I get the DBA to grant me privileges on DBMS_LOCK?) rather than technical (How can I make a procedure wait for a fixed duration of time?)

A technical fix for a political problem; write a PL/SQL wrapper to DBMS_LOCK.SLEEP, owned by SYS and granted to those who need it.

Cheers, APC