Re: IN Clause limitations

From: Mladen Gogala <mgogala.spam-me-not_at_verizon.net>
Date: Thu, 16 Nov 2006 06:07:00 GMT
Message-ID: <pan.2006.11.16.06.07.00.723506@verizon.net>

On Wed, 15 Nov 2006 12:54:06 -0800, Kavita wrote:

> is any one knows ....is there any limitations to pass the number of
> total count values not more then 999
>
> where (PC_CONT) IN (" & gc_PolicyNumberString(i_NoOfRecInArr) & ")
>
> where array should not accessed with more then 999 values?
>
> -Kavita

Here is an alternative:

SQL> create type ename_tbl as table of varchar2(10);   2 /

Type created.

SQL> declare
  2 some_name ename_tbl := ename_tbl('SCOTT','KING','WARD','ALLEN');   3 cursor csr is
  4 select empno from emp where ename in (select value(p)

  5                                        from table(some_name) p);

  6 begin
  7 for c in csr loop
  8 dbms_output.put_line('Empno is:'||c.empno);   9 end loop;
 10 end;
 11 /

Empno is:7788
Empno is:7839
Empno is:7521
Empno is:7499

PL/SQL procedure successfully completed.

SQL> Unfortunately, Oracle will not allow you to do that with local types, you will have to create type, so that might be a bit over the top for many SQL statements.

-- 
http://www.mladen-gogala.com

Received on Thu Nov 16 2006 - 00:07:00 CST