Thanks for the question, Vinay.

Asked: October 22, 2007 - 2:07 pm UTC

Last updated: October 23, 2007 - 1:27 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I'd like to use a decode statement that returns multiple values instead of a single value. The statement where I will be using this is something like:

select * from v_viewname v where v.enterprise in
decode(v_in_enterprise,
'ALL', v.enterprise,
TABLE(f_stringtokenizer(v_in_enterprise))
)

In the above statement:

v_in_enterprise: Is an input parameter to the function which contains the above select statement. This parameter could either be 'ALL', in which case I would need to retrieve all records from the view v_viewname. OR it could be a comma-separated list of the form '12,29,33', in which case I would retrieve from the view, only those records that have enterprise in (12,29,33).

f_stringtokenizer is a function that accepts as input a comma-separated varchar2 string and returns a collection with these values. Using a type called t_tokenized_string, I cast this function's return value as a table and use it in SQL.

Of course the select statement above would not work since decode will complain that a 'Single-row subquery returns more than one row'.

My question is: what would be the best way to implement something like this?

Thanks,
Vinay

and Tom said...

do not use decode, use case - which can deal with more types....

ops$tkyte%ORA10GR2> create or replace type mytable as table of number;
  2  /

Type created.

ops$tkyte%ORA10GR2> create or replace function foo return  mytable
  2  as
  3  begin
  4      return mytable(1,2,3);
  5  end;
  6  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select username, case when username = USER then foo end foo_data
  2    from all_users x
  3   where username like 'OPS%' or username like 'SYS%'
  4  /

USERNAME
------------------------------
FOO_DATA
-------------------------------------------------------------------------------
SYS


SYSTEM


SYSMAN


OPS$TKYTE
MYTABLE(1, 2, 3)

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

CSV -> table discussed in Tom's blog, etc

Duke Ganote, October 23, 2007 - 11:31 am UTC

Tom discusses this in several places, e.g.
http://tkyte.blogspot.com/2006/06/varying-in-lists.html

variable p_list varchar2(75)
/
exec :p_list := 'COMPANY,COMPANY_ADDRESS'
/
create or replace  function foo
( p_str in varchar2
, p_delim in varchar2 default ',' 
)
 return mytable as
     l_str long default p_str || p_delim;
     l_n   number;
     l_data mytable := mytable();
begin
     loop
          l_n := instr( l_str, p_delim );
          exit when (nvl(l_n,0) = 0);
          l_data.extend;
          l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));
          l_str := substr( l_str, l_n+1 );
      end loop;
      return l_data;
  end;
/
select * from table(cast(foo(:p_list) as mytable))
/
select table_name
  from user_tables
 where table_name IN
( select * from table(cast(foo(:p_list) as mytable)) )
    or table_name = case :p_list when 'ALL' then table_name end
/

The results:

SQL> select * from table(cast(foo(:p_list) as mytable));

COLUMN_VALUE
------------------------------
COMPANY
COMPANY_ADDRESS


SQL> select table_name
  2    from user_tables
  3   where table_name IN
  4  ( select * from table(cast(foo(:p_list) as mytable)) )
  5      or table_name = case :p_list when 'ALL' then table_name end
  6  /

TABLE_NAME
------------------------------
COMPANY_ADDRESS
COMPANY

SQL> exec :p_list := 'ALL'

PL/SQL procedure successfully completed.

SQL> select table_name
  2    from user_tables
  3   where table_name IN
  4  ( select * from table(cast(foo(:p_list) as mytable)) )
  5      or table_name = case :p_list when 'ALL' then table_name end
  6  /

TABLE_NAME
------------------------------
CO
TEST
T_COMPANY_ADDRESS
T_COMPANY
T_ADDRESS
ADDRESS
COMPANY_ADDRESS
COMPANY
COMPANY_STATE
T_COPY
TOM
PIECES
CO_ST
CERT_OF_AUTHORITY

14 rows selected.

(assuming you don't have a table named ALL).

October 23, 2007 - 1:27 pm UTC

ahh, thanks, i didn't read the full thing -

case instead of decode, valid.

However, your answer, more appropriate :)

thanks!

Excellent

Vinay Chandrakant, October 23, 2007 - 2:01 pm UTC

Thanks a ton, both! That was good!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

CSV -> table discussed in Tom's blog, etc

Excellent

More to Explore

PL/SQL demos

PL/SQL docs

How can I have a decode statement that returns multiple values (e.g. a collection)?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

CSV -> table discussed in Tom's blog, etc

Excellent

More to Explore

PL/SQL demos

PL/SQL docs