This discussion is archived
4 Replies Latest reply: Mar 5, 2013 12:54 PM by chris227 RSS

How to get each value from a parameter passed like this '(25,23,35,1)'

716065 Newbie
Currently Being Moderated
Hi

One of the parameter passed to the function is
FUNCTION f_main_facility(pi_flag_codes VARCHAR2) return gc_result_set AS

pi_flag_codes will be passed a value in this way '(25,23,35,1)'

How to get each value from the string
like 25 first time
23 second time
35 third time
1 fourth time

I need to build a select query with each value as shown below:-
(SELECT t2.org_id, t4.description
from org_name t2, ref_org_name t3, code_table t4
where t2.att_data = t4.code
and t3.ref_code = t2.att_type
and t2.att_type = 25 and t3.code_type = t4.code_type
and to_date('01-JAN-10', 'DD-MON-YY') between t2.att_start_date AND t2.att_end_date) q1,
(SELECT t2.org_id, t4.description
from org_name t2, ref_org_name t3,code_table t4
where t2.att_data = t4.code
and t3.ref_code = t2.att_type
and t2.att_type = 23 and t3.code_type = t4.code_type
and to_date('01-JAN-10', 'DD-MON-YY') between t2.att_start_date AND t2.att_end_date) q2,
(SELECT t2.org_id, RTRIM(xmlagg(xmlelement(e, t4.description || ';')
ORDER BY t4.description).EXTRACT('//text()'), ';') AS DESCRIPTION
from org_name t2, ref_org_name t3,code_table t4
where t2.att_data = t4.code
and t3.ref_code = t2.att_type
and t2.att_type = 35 and t3.code_type = t4.code_type
and to_date('01-JAN-10', 'DD-MON-YY') between t2.att_start_date AND t2.att_end_date
group by t2.org_id) q3,
(SELECT t2.org_id, t4.description
from org_name t2, ref_org_name t3, code_table t4
where t2.att_data = t4.code
and t3.ref_code = t2.att_type
and t2.att_type = 1 and t3.code_type = t4.code_type
and to_date('01-JAN-10', 'DD-MON-YY') between t2.att_start_date AND t2.att_end_date) q4

Please help me with extracting each alue from the parm '(25,23,35,1)' for the above purpose. Thank You.
  • 1. Re: How to get each value from a parameter passed like this '(25,23,35,1)'
    chris227 Guru
    Currently Being Moderated
    I would propose the usage of regexp for readibiliy purposes and only in the case if this doesnt perform well, look at solutions using substr etc.
     select
       regexp_substr( '(25,23,35,1)', '\d+', 1, 1) s1
      ,regexp_substr( '(25,23,35,1)', '\d+', 1, 2) s2
      ,regexp_substr( '(25,23,35,1)', '\d+', 1, 3) s3
      ,regexp_substr( '(25,23,35,1)', '\d+', 1, 4) s4
     from dual  
    
    S1     S2     S3     S4
    "25"     "23"     "35"     "1"
    In pl/sql you do something like l_val:= regexp_substr( '(25,23,35,1)', '\d+', 1, 1);
    If t2.att_type is type of number you will do:
     t2.att_type= to_number(regexp_substr( '(25,23,35,1)', '\d+', 1, 1))
    Edited by: chris227 on 01.03.2013 08:00
  • 2. Re: How to get each value from a parameter passed like this '(25,23,35,1)'
    damorgan Oracle ACE Director
    Currently Being Moderated
    http://www.morganslibrary.org/reference/conditions.html

    Look at the demos titled "Complex IN Demo Using CAST" and "Complex IN Demo Using MEMBERSHIP OF." You may need to tweak them depending on what you wish to do with the separate values.
  • 3. Re: How to get each value from a parameter passed like this '(25,23,35,1)'
    716065 Newbie
    Currently Being Moderated
    chris227 wrote:
    I would propose the usage of regexp for readibiliy purposes and only in the case if this doesnt perform well, look at solutions using substr etc.
    select
    regexp_substr( '(25,23,35,1)', '\d+', 1, 1) s1
    ,regexp_substr( '(25,23,35,1)', '\d+', 1, 2) s2
    ,regexp_substr( '(25,23,35,1)', '\d+', 1, 3) s3
    ,regexp_substr( '(25,23,35,1)', '\d+', 1, 4) s4
    from dual  
    
    S1     S2     S3     S4
    "25"     "23"     "35"     "1"
    In pl/sql you do something like l_val:= regexp_substr( '(25,23,35,1)', '\d+', 1, 1);
    If t2.att_type is type of number you will do:
    t2.att_type= to_number(regexp_substr( '(25,23,35,1)', '\d+', 1, 1))
    Edited by: chris227 on 01.03.2013 08:00
    Sir,

    I am using oracle 10g.
    In the process of getting each number from the parm '(25,23,35,1)' , I also need the position of the number

    say 25 is at 1 position.
    23 is at 2
    35 is at 3
    1 is at 4.

    the reason I need that is when I build seperate select for each value, I need to add the query number at the end of the select query.

    Please see the code I wrote for it, But the select query is having error:-
    BEGIN

    IF(pi_flag_codes IS NOT NULL) THEN

    SELECT length(V_CNT) - length(replace(V_CNT,',','')) FROM+ ----> the compiler gives an error for this select query : PLS-00428:
    *(SELECT '(25,23,35,1)' V_CNT  FROM dual);*
    DBMS_OUTPUT.PUT_LINE(V_CNT);

    -- V_CNT := 3;
    FOR L_CNT IN 0..V_CNT LOOP

    if L_CNT=0 then
    V_S_POS:=1;
    V_E_POS:=instr(pi_flag_codes, ',', 1, 1)-1;
    else
    V_S_POS:=instr(pi_flag_codes,',',1,L_CNT)+1;
    V_E_POS:=instr(pi_flag_codes, ',', 1, L_CNT+1)-V_S_POS;
    end if;

    if L_CNT=V_CNT then
    V_ID:=TO_NUMBER(substr(pi_flag_codes,V_S_POS));
    else
    V_ID:=TO_NUMBER(substr(pi_flag_codes,V_S_POS,V_E_POS));
    end if;

    VN_ATYPE := ' t2.att_type = ' || V_ID;

    rec_count := rec_count +1;
    query_no := 'Q' || rec_count;




    Pls help me with fetching each value to build the where cond of the select query along with the query number.
    Thank You.
  • 4. Re: How to get each value from a parameter passed like this '(25,23,35,1)'
    chris227 Guru
    Currently Being Moderated
    Dont know exactly what you mean. Perhaps something like
    declare
      l_list sys.ODCInumberlist;
    function f_split_values (p_val_list varchar2)
    return sys.ODCInumberlist
    is
      l_splitted_list sys.ODCInumberlist;
    begin
      select to_number(regexp_substr(p_val_list, '\d+', 1, level))
      bulk collect into l_splitted_list
      from dual
      connect by
      level <= length(p_val_list) - length(replace(p_val_list,',')) + 1;
      return l_splitted_list;
    end;
    begin
      l_list := f_split_values ('(25,23,35,1)');
      for i in 1..l_list.count loop
        DBMS_OUTPUT.PUT_LINE(i||': '||l_list(i));
      end loop;
    end;
    
    1: 25
    2: 23
    3: 35
    4: 1

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points