Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Marco.

Asked: July 18, 2016 - 9:45 am UTC

Last updated: July 19, 2016 - 2:00 pm UTC

Version: oracle 11g2

Viewed 1000+ times

You Asked

Hello Chris Saxon,

I have a below table structure.

CREATE TABLE "PEOPLE"
( "PERSON_ID" NUMBER,
"GIVEN_NAME" VARCHAR2(100),
"CATEGORY" number(50)
)

INSERT INTO people VALUES(1,Connor,1001);
/
INSERT INTO people VALUES(2,Chris Saxon,1002);
/
INSERT INTO people VALUES(3,Tom,1003);

There is ':' separated values which will come from another input source,
So, basically i am getting those values as :1:2:3:4 and converting them in to ',' separated values so it can be used in IN clause by using below code.

select given_name from people where CATEGORY in (substr(':'||:P_VALUE||':',instr(':'||:P_VALUE||':',':',1,level)+1,(instr(':'||:P_VALUE||':',':',1,level + 1) - instr(':'||:P_VALUE||':',':',1,level))- 1))
CONNECT BY LEVEL <= regexp_count(':'||:P_VALUE||':',':') - 1

Runing above code to get desire output which works as per need.

Is there any better solution which i can use.

Thank you.



and Chris said...

So you want to have a dynamic in-list, that receives values in the form:

:1:2:3:

?

If so, you're on the right track. But your example produces waaaay too many rows for me:

CREATE TABLE PEOPLE 
( PERSON_ID NUMBER, 
GIVEN_NAME VARCHAR2(100), 
CATEGORY number
);

INSERT INTO people VALUES(1,'Connor',1001);
INSERT INTO people VALUES(2,'Chris Saxon',1002);
INSERT INTO people VALUES(3,'Tom',1003);

var p_value varchar2(20);
exec :p_value := ':1:2:3:4';

select count(*) from people 
where person_id in (
 substr(':'||:P_VALUE||':',
   instr(':'||:P_VALUE||':',':',1,level)+1,
   (instr(':'||:P_VALUE||':',':',1,level + 1) - instr(':'||:P_VALUE||':',':',1,level))- 1)
 )
CONNECT BY LEVEL <= regexp_count(':'||:P_VALUE||':',':') - 1;

COUNT(*)  
39


To overcome this, put the code to split the rows in a with clause:

with vals as (
 select substr(':'||:P_VALUE||':',
   instr(':'||:P_VALUE||':',':',1,level)+1,
   (instr(':'||:P_VALUE||':',':',1,level + 1) - instr(':'||:P_VALUE||':',':',1,level))- 1) 
 from dual
 CONNECT BY LEVEL <= regexp_count(':'||:P_VALUE||':',':') - 1
)
  select * from people
  where  person_id in (select * from vals);

PERSON_ID  GIVEN_NAME   CATEGORY  
1          Connor       1,001     
2          Chris Saxon  1,002     
3          Tom          1,003


You can see other approaches at:

http://asktom.oracle.com/Misc/varying-in-lists.html
https://oracle-base.com/articles/misc/dynamic-in-lists

Rating

  (8 ratings)

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

Comments

Bang on

Marco Foxx, July 18, 2016 - 11:06 am UTC

this is cool.thank you so much Mr. Chris Saxon.
You made my day :)
Chris Saxon
July 18, 2016 - 12:04 pm UTC

You're welcome!

Nitpicking

Stew Ashton, July 18, 2016 - 12:17 pm UTC

With Chris' solution, :p_value is limited to a maximum length of 3998. To avoid that restriction, use this approach inspired by Connor's KISS analytics series.
with vals as (
  select substr(
    :p_value,
    pos + 1,
    lead(pos,1,4000) over(order by pos) - pos - 1
  ) subs
  from (
    select instr(:p_value, ':', 1, level) pos
    from dual
    connect by
      level <= length(:p_value) - nvl(length(replace(:p_value, ':', '')), 0) + 1
  )
)
select * from people
where person_id in (select * from vals);
I explain my variant in detail here:
https://stewashton.wordpress.com/2016/06/22/new-improved-in-lists/
Chris Saxon
July 18, 2016 - 12:26 pm UTC

Nitpicker ;)

But thanks for pointing out the improvement.

Can you help me with this !!

Marco Foxx, July 19, 2016 - 8:58 am UTC

i have a dynamic sql which will render result .
-------------------------------------------
When i run this query i am not able see any result if you can guide..if not its okay. :)
-----------------
declare 
    v_where varchar2(1000) := ' where (to_date(BIRTH_DATE,:app_nls_date_format)>=:p100_start_date or :p100_start_date is null)
                               and (to_date(BIRTH_DATE,:app_nls_date_format)<=:p100_end_date or :p100_end_date is null)';  
begin
if :P100_CONDITION is not null and :P100_AMOUNT is not null then --both fields must be spefified, otherwise it doesnt make sense
 v_where := v_where ||' and amount ' || :p100_condition ||:P100_AMOUNT;
 apex_debug.message ('P%s DEBUG: v_where is now: %s', :app_page_id, v_where);
end if;
 

apex_debug.message ('P%s DEBUG: v_where is now: %s', :app_page_id, v_where);

--for example you can add condition here if all your conditions is null (test all your  significant items...)
if :p100_condition is null and :p100_amount is null and :p100_start_date is null and :p100_end_date is null then
   v_where := v_where || ' and 0 = 1 '; --add condition that cannot be met
end if;

IF :P100_X IS NOT NULL THEN
     --insert into temp(str2) values(:P100_X)
    v_where := v_where || 'and with vals as (
  select substr(
    :P100_X,
    pos + 1,
    lead(pos,1,4000) over(order by pos) - pos - 1
  ) subs
  from (
    select instr(:P100_X, '':'', 1, level) pos
    from dual
    connect by
      level <= length(:P100_X) - nvl(length(replace(:P100_X, '':'', '')), 0) + 1
  )
)
select CATEGORY from people
where CATEGORY in (select * from vals)';
apex_debug.message ('P%s DEBUG: v_where is now: %s', :app_page_id, v_where);
     END IF;

--and add one more debug message just to see what is happening under the hood
apex_debug.message ('P%s DEBUG: v_where after if condition: %s', :app_page_id, v_where);

return 'select person_id, given_name, birth_date, amount,ANNIV_DATE from people'|| v_where;

end;

Chris Saxon
July 19, 2016 - 9:06 am UTC

The with clause has to go at the top of the statement. You can't have "and with" like there is if :P100_X is not null:

v_where := v_where || 'and with vals as (


You need to place this before the select:

with vals as ( ... )
select person_id, given_name, birth_date, amount,ANNIV_DATE from people'|| v_where

Quoted string issues.

Marco Foxx, July 19, 2016 - 9:19 am UTC 

return 'with vals as (
  select substr(
    :P100_X,
    pos + 1,
    lead(pos,1,4000) over(order by pos) - pos - 1
  ) subs
  from (
    select instr(:P100_X, '':'', 1, level) pos
    from dual
    connect by
      level <= length(:P100_X) - nvl(length(replace(:P100_X, '':'', '')), 0) + 1
  )
)
select  person_id, given_name, birth_date, amount,ANNIV_DATE from people
where CATEGORY in (select * from vals)'|| v_where;


If i use this it says quoted string not properly terminated if i remove this code it works fine !!
Chris Saxon
July 19, 2016 - 9:29 am UTC

I believe you need to add more double quotes in:

nvl(length(replace(:P100_X, '':'', '')), 0)


so it becomes:

nvl(length(replace(:P100_X, '':'', '''')), 0)


Or, as you're replacing it with null, you could omit the final argument:

nvl(length(replace(:P100_X, '':'')), 0)

Thank you for Quick responses :)

Marco Foxx, July 19, 2016 - 9:41 am UTC

yes i have used
nvl(length(replace(:P100_X, '':'', '''')), 0)
which solves Quoted string issue thank you :)
Now, I need to solve why my query is not returning any data from given filtered criteria.
Which works in Sql Developer or Toad.

Chris Saxon
July 19, 2016 - 11:05 am UTC

Start debugging then! Log the query text and bind variables to verify you're running the same statement in all environments.

Q - quotes

Rajeshwaran, Jeyabal, July 19, 2016 - 12:04 pm UTC

I believe you need to add more double quotes in:
nvl(length(replace(:P100_X, '':'', '')), 0)

so it becomes:
nvl(length(replace(:P100_X, '':'', '''')), 0)

or just with q-quotes, makes life simple.

q'| nvl(length(replace(:P100_X, '':'', '')), 0) |'

Nitpicking

Rajeshwaran, Jeyabal, July 19, 2016 - 12:49 pm UTC 

demo@ORA11G> variable x varchar2(20)
demo@ORA11G> exec :x :='a,b,c,d,e';

PL/SQL procedure successfully completed.

demo@ORA11G> select instr(:x,',',1,level) pos
  2  from dual
  3  connect by level <= length(:x) -
  4      length( replace(:x,','))+1 ;

       POS
----------
         2
         4
         6
         8
         0

5 rows selected.

demo@ORA11G> select :x input_txt  ,  pos+1  start_pos,
  2                lead(pos,1,4000) over(order by pos) - pos-1 end_pos
  3  from (
  4  select instr(:x,',',1,level) pos
  5  from dual
  6  connect by level <= length(:x) -
  7      length( replace(:x,','))+1
  8       ) ;

INPUT_TXT                         START_POS    END_POS
-------------------------------- ---------- ----------
a,b,c,d,e                                 1          1
a,b,c,d,e                                 3          1
a,b,c,d,e                                 5          1
a,b,c,d,e                                 7          1
a,b,c,d,e                                 9       3991

5 rows selected.

demo@ORA11G> select :x input_txt  ,  pos+1  start_pos,
  2                lead(pos,1,4000) over(order by pos) - pos-1 end_pos
  3  from (
  4  select instr(:x,',',1,level) pos
  5  from dual
  6  connect by level <= length(:x) -
  7      length( replace(:x,','))+1
  8     order by pos
  9       ) ;

INPUT_TXT                         START_POS    END_POS
-------------------------------- ---------- ----------
a,b,c,d,e                                 1          1
a,b,c,d,e                                 3          1
a,b,c,d,e                                 5          1
a,b,c,d,e                                 7          1
a,b,c,d,e                                 9       3991

5 rows selected.

demo@ORA11G> select substr(:x ,  pos+1  ,
  2                lead(pos,1,4000) over(order by pos) - pos-1) final_value
  3  from (
  4  select instr(:x,',',1,level) pos
  5  from dual
  6  connect by level <= length(:x) -
  7      length( replace(:x,','))+1
  8     order by pos
  9       ) ;

FINAL_VALUE
--------------------------------
a
b
c
d
e

5 rows selected.

demo@ORA11G>


Without the "Order by" in subquery, the rows come out in random order, and it won't be deterministic.

Don't we need an "Order by" in In-line sql's? to make the result set to be deterministic, while passing to outer layers. please confirm.
Chris Saxon
July 19, 2016 - 2:00 pm UTC

I'm not following. Why does it matter what order the subquery is?

It's only if you need the final result in a particular order that you require an order by. In this case it should go outside the inner query:

select substr(:x ,  pos+1  ,
              lead(pos,1,4000) over(order by pos) - pos-1) final_value
from (
  select instr(:x,',',1,level) pos
  from dual
  connect by level <= length(:x) - length( replace(:x,','))+1
) 
order by pos;

Nitpicking

Rajeshwaran, Jeyabal, July 19, 2016 - 2:59 pm UTC

sorry, you are right. i think i got confused.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.