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

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Tue, 11 Mar 2003 01:36:20 GMT
Message-ID: <Xns933AD0FFFC548pobox002bebubcom@63.240.76.16>

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

> Afternon all,
>
> =====================
> Oracle 8174 EE 64 bit.
> HPUX 11.00 64 bit.
> =====================
>
> I'm certain I've done this before, but try as I might, I cannot seems
> to make it work now ! What I think I've done before is this :
>
> select * from table where something in (some_function());
>
> but, the bit in parenthesis is generated as output from a function
> either via an OUT parameter or by a direct function result. So the
> function returns <'a','b','c'> and the in clause 'just worked'. Or at
> least, that's what I think happened.

<lots of snip>

Hello Norman,

I think you can do what you are trying if your function returns an object type, and you use the table cast syntax in the select in the subquery. There may be other ways to do this but this is what I normally use.

Here's an example the function get_list just takes a comma delimited string and returns an array of type str_list.

Hopefully you can make sense of it.

SQL> create type str_obj as object (s varchar2(100))   2 /

Type created.

SQL> create type str_list as table of str_obj;   2 /

Type created.

SQL> create or replace function
  2 get_list (p_str in out varchar2)   3 return str_list
  4 is
  5 l_str_list str_list := str_list(null);   6 begin

  7      while p_str is not null loop
  8          l_str_list(l_str_list.count) := str_obj(
  9              rtrim(substr(
 10                  p_str,1,instr(p_str||',',',')),',')
 11              );
 12          p_str := substr(p_str,instr(p_str||',',',')+1);
 13          l_str_list.extend(1);
 14      end loop;
 15      l_str_list.trim(1);
 16      return l_str_list;

 17 end;
 18 /

Function created.

SQL> declare

  2      l_str varchar2(80) := 'SMITH,SCOTT,MILLER';
  3      l_str_list str_list := str_list(null);
  4  begin
  5      l_str_list := get_list(l_str);
  6      for e in (
  7          select empno, ename, job
  8          from emp where ename in (
  9              select s.s
 10              from table(cast(l_str_list as str_list)) s)
 11          ) loop
 12          dbms_output.put_line(to_char(e.empno)||' '
 13              ||e.ename||' '||e.job);
 14      end loop;

 15 end;
 16 /
7934 MILLER CLERK
7788 SCOTT ANALYST
7369 SMITH CLERK PL/SQL procedure successfully completed.

SQL> Hth

Martin Received on Mon Mar 10 2003 - 19:36:20 CST