0

I have a procedure that takes in 2 parameters formatted like so:

i_rule_type: 'val1', 'val2', 'val3'

i_geo: 'val1', 'val2', val3'

I know that they are coming in correctly, because the if/else statement at the bottom is working. When I hard code the values in or pass in null for both parameters, the query works. I can tell that it is running in the situation, because it takes a little time to complete and actually returns the rows. However, when I pass the parameters in, it's like the query is not even running - it finishes instantly and empty without any errors. My current suspicions are that the variables are not actually being bound, but I'm stuck. 

PROCEDURE REPORT_PRC (i_rule_type IN VARCHAR2, i_geo IN VARCHAR2, results_out OUT sys_refcursor) IS

v_report_query    VARCHAR2(800);

BEGIN

v_report_query := 'SELECT r.NAME as RULE_NAME, r.DESCRIPTION as DESCRIPTION, r.RULE_ID as RULE_ID, r.AUDIENCE as RULE_TYPE, g.NAME as GEO, count(l.rule_id) as LEADS_ROUTED, max(l.LEAD_ASSIGNMENT_END) as LAST_ROUTED, a.NAME as ATTRIBUTE
FROM rule r
LEFT OUTER JOIN LEAD_DETAILED_LOG l on r.RULE_ID = l.RULE_ID
LEFT OUTER JOIN GEO g on r.GEO_ID = g.GEO_ID
LEFT OUTER JOIN RULE_ATTRIBUTE ra on r.RULE_ID = ra.RULE_ID
LEFT OUTER JOIN ATTRIBUTE a on ra.ATTRIBUTE_ID = a.ATTRIBUTE_ID';

if i_geo IS NOT NULL OR i_rule_type IS NOT NULL then 
    v_report_query := v_report_query || ' WHERE';
end if;

if i_rule_type IS NOT NULL then 
    v_report_query := v_report_query || ' r.AUDIENCE in (:i_rule_type)';
end if;

if i_geo IS NOT NULL then 
    if i_rule_type IS NOT NULL then
        v_report_query := v_report_query || ' AND';
    end if;
    v_report_query := v_report_query || ' g.NAME in (:i_geo)';
end if;

v_report_query := v_report_query || ' GROUP BY r.rule_id, r.NAME, r.DESCRIPTION, r.RULE_ID, r.AUDIENCE, l.RULE_ID, g.NAME, a.NAME ORDER BY r.NAME'; 

if i_geo IS NOT NULL AND i_rule_type IS NOT NULL then
    OPEN results_out FOR v_report_query USING i_rule_type, i_geo;
elsif i_geo IS NOT NULL then
    OPEN results_out FOR v_report_query USING i_geo;
elsif i_rule_type IS NOT NULL then
    OPEN results_out FOR v_report_query USING i_rule_type;
else
    OPEN results_out FOR v_report_query;
end if;

end REPORT_PRC;
kb_
  • 1,245
  • 4
  • 18
  • 33
  • `i_rule_type` for this are you sending comma separated values? If so, you have to understand it will be treated as `=`. – Maheswaran Ravisankar Jun 05 '14 at 18:08
  • What do you mean by treated as `=`? Will it not act like `COLUMN IN ('val1','val2','val3')`? The values come in with the single quotes as well. – kb_ Jun 05 '14 at 18:36
  • 2
    No kristin, Bind variable _does not_ work the way you think for IN clause.. you have to set it like `IN ( :v1, :v2 , :v3)` and you have to know the count before hand unfortunately. In your case the resultant comparison is going to be `COLUMN IN ( '''val1''','''val2''','''val3'''')` , which is like equals to of the csv string. – Maheswaran Ravisankar Jun 05 '14 at 18:43
  • ahhh ok!! thanks for the clarification. i would've had no idea. i'll test it out. – kb_ Jun 05 '14 at 18:44
  • Update - that was exactly the issue. Solved it with using INSTR instead. thanks for your help! – kb_ Jun 05 '14 at 19:08
  • Great.! But you might not get the usage of index if any over those columns. Not an issue for smaller dataset though!. – Maheswaran Ravisankar Jun 05 '14 at 19:12
  • 1
    If you have a working solution you should post it as an answer. Add to the SO knowledge base! However I doubt you have a working answer. This evaluates to true, but it isn't what you want: `instr('VAL1', 'VAL9, VAL10, VAL11, VAL20') > 0` – APC Jun 06 '14 at 04:19
  • 1
    You should look at this thread on tokenizing CSV strings: http://stackoverflow.com/a/1092339/146325 – APC Jun 06 '14 at 04:27

0 Answers0