4 Replies Latest reply on Mar 5, 2013 8:54 PM by chris227

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

    716065
      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
          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
            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
              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
                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