Compilation of stored procedure hangs

[umeman]

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 and is run in the SCOTT schema)

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-

 

[ThomVF]

It happens to me as well (just so you know it's not your environment)... =================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[karluk]

I, on the other hand, get clean compiles of both procs.

The only possible problem I see with your procedure is that you only use one set of quotes in ('10',100) but use quotes around both numbers in ('20','2000') and the other pairs. You should be consistent, but since the mistake is in both procedures it doesn't explain why one compiles and the other hangs.

 

[ThomVF]

I used quotes around the first value for all sets, in both examples and it fails.

Here is my version of ORACLE: 8.1.7.0.0
right off the CD, no patches
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[karluk]

Well, that's a possible clue since I'm on Oracle 8.1.7.2.1. The different versions may indicate a bug that's been fixed in one of the 8.1.7 patches. A quick check of Metalink suggests that one likely candidate is bug 1396675.