Stored procedure hangs during compilation

[umeshgopal]

DBA's

I have come across the following problem and would
like to know if you have a solution for it:

When I compile the following stored procedure:
(The procedure does nothing but demonstrate the
situation)

create or replace procedure test1 as

cursor c0 is
SELECT * from emp a where (a.deptno,a.sal) in (('10',100),('20','2000'),('30','3000'));


begin

dbms_output.put_line('hello world');
end test1;

The above cursor has 3 element pairs in the 'in' clause

However when I increase the number of elements in the
in clause to 4 the compilation of the procedure hangs


create or replace procedure test2 as

cursor c0 is
SELECT * from emp a where (a.deptno,a.sal) in (('10',100),('20','2000'),('30','3000'),('40','4000'));


begin

dbms_output.put_line('hello world');
end test2;

Any assistance or work arounds on this problem appreciated

Thanks
-U-

[dave777]

I've tried to replicate your problem, but both procedures compiled without any problems.

what version of Oracle do you have?

[pando]

look v$session_wait what it is waiting for