Mega Code Archive
Dbms_lock sleep
<![CDATA[
SQL>
SQL> create table job_parameters
2 ( jobid number primary key,
3 iterations number,
4 table_idx number );
Table created.
SQL>
SQL> create or replace procedure simulation( p_procedure in varchar2, p_jobs in number, p_iters in number )
2 authid current_user
3 as
4 l_job number;
5 l_cnt number;
6 begin
7 for i in 1 .. p_jobs
8 loop
9 begin
10 execute immediate 'drop table t' || i;
11 exception
12 when others then null;
13 end;
14 execute immediate 'create table t' || i || ' ( x int )';
15 end loop;
16
17 for i in 1 .. p_jobs
18 loop
19 dbms_job.submit( l_job, p_procedure || '(JOB);' );
20 insert into job_parameters( jobid, iterations, table_idx )values ( l_job, p_iters, i );
21 end loop;
22
23 commit;
24 loop
25 dbms_lock.sleep(30);
26 select count(*) into l_cnt from job_parameters;
27 exit when (l_cnt = 0);
28 end loop;
29 end;
30 /
Warning: Procedure created with compilation errors.
SQL>
SQL> show errors
Errors for PROCEDURE SIMULATION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
25/9 PL/SQL: Statement ignored
25/9 PLS-00201: identifier 'DBMS_LOCK' must be declared
SQL>
SQL> drop table job_parameters;
Table dropped.
SQL>
]]>