Re: DBA requires basic SQL assistance please (LONG POST)

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Thu, 13 Mar 2003 03:08:28 GMT
Message-ID: <Xns933CE09D56F3Cpobox002bebubcom@63.240.76.16>

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in news:E2F6A70FE45242488C865C3BC1245DA7036A69AB_at_lnewton.leeds.lfs.co.uk:

> Hi Martin,
>
> I now have found an 'official' bug in 64 bit 817 - or so it would
> appear. Oracle Support can reproduce this on Solaris & Windows but not
> on 920.
>

>:o)

>
> Cheers,
> Norman.

Norman, Bad luck.

I've used it on 817 Solaris and 902 Windows, so I suspect neither was 64 bit.

You mentioned there may be an alternative, the only one I've seen that doesn't use dynamic SQL doesn't really fit your description either. You can effectively do a dynamic in list using instr instead. Maybe it could be modified to help, though it may be not very good at performing and costly in some circumstances.

SQL> exec :str := '7499,7782,7902'

PL/SQL procedure successfully completed.

SQL> select empno, ename, job
2 from emp
3 where instr(','||:str||',',','||to_char(empno)||',') > 0 4 /

EMPNO ENAME JOB
---------- ---------- ---------

      7499 ALLEN      SALESMAN
      7782 CLARK      MANAGER
      7902 FORD       ANALYST

SQL> exec :str := '7844,7876,7900'

PL/SQL procedure successfully completed.

SQL> / EMPNO ENAME JOB
---------- ---------- ---------

      7844 TURNER     SALESMAN
      7876 ADAMS      CLERK
      7900 JAMES      CLERK

SQL> Otherwise stumped ...

Martin Received on Wed Mar 12 2003 - 21:08:28 CST