Re: My soln to passing an IN clause to Oracle Reports 2.5

Sorry, folks, but there's been a slight change.

Please make sure that INSTEAD of the Before_Report trigger, you put the call to Make_In_List in the After_Parameter_Form trigger! NOT the Before_Report becuase that doesn't do any good, and it took me forever to figure that out.

Charlton

In article <7gpm4i$js5$1_at_nnrp1.dejanews.com>,   Charlton Purvis <purvis386_at_my-dejanews.com> wrote:
> Hi there:
>
> Once in a while I get to contribute to solving a problem rather than cause
> one. I ran into problems when trying to pass a dynamic IN clause to my Oracle
> Reports, and here is my solution.
>
> If you pass a comma-delimited character string to the report, say it's called
> p_str, the BeforeReport trigger will call a few Program Units to build a
> proper IN clause.
>
> So here are the four Program Units:
>
> ****** PROGRAM UNIT
>
> FUNCTION Make_IN_List
> (
> str VARCHAR2
> ) RETURN VARCHAR2 IS
> BEGIN
> Return(REPLACE(Wrap_SUBSTR_In(str,',',''''),''' ''',''','''));
> END;
>
> ****** PROGRAM UNIT
>
> FUNCTION Wrap_SUBSTR_In
> (
> str VARCHAR2,
> delim VARCHAR2,
> wrap VARCHAR2
> ) RETURN VARCHAR2 IS
>
> tmp_wrap_str VARCHAR2(1024);
> tmp_str VARCHAR2(1024);
> top_str VARCHAR2(1024);
>
> BEGIN
> tmp_str := LTRIM(str);
> top_str := Get_Top_String(tmp_str,delim);
> WHILE (top_str IS NOT NULL) LOOP
> tmp_wrap_str := tmp_wrap_str || wrap || top_str || wrap || ' ';
> tmp_str := LTRIM(Pop_Top_String(tmp_str,delim));
> top_str := Get_Top_String(tmp_str,delim);
> END LOOP;
> Return(RTRIM(tmp_wrap_str));
> END;
>
> ****** PROGRAM UNIT
>
> FUNCTION Get_Top_String
> (
> str VARCHAR2,
> delim VARCHAR2
> ) RETURN VARCHAR2 IS
>
> delim_loc NUMBER;
>
> BEGIN
> delim_loc := INSTR(str,delim);
> IF (delim_loc = 0) THEN
> Return(str);
> ELSE
> Return(SUBSTR(str,1,delim_loc-1));
> END IF;
> END;
>
> ****** PROGRAM UNIT
>
> FUNCTION Pop_Top_String
> (
> str VARCHAR2,
> delim VARCHAR2
> ) RETURN VARCHAR2 IS
> BEGIN
> Return(SUBSTR(str,LENGTH(Get_Top_String(str,delim))+2));
> END;
>
> ****** EXPLANATION CONTINUED
>
> Sorry there are no comments, but it does do the trick, as long as a comma-
> delimited str is passed.
>
> ****** Back to the Before Report Trigger
>
> function BeforeReport return boolean is
> begin
> :p_str := Make_In_List(:p_str);
> return (TRUE);
> end;
>
> Now how to we refer to our new p_str?
>
> First, create a user parameter called p_str, type character, width whatever
> (1024), and make the default NULL. This is important, otherwise when Reports
> tries to build your query, it will bomb if the proper default dummy variable
> is not passed.
>
> Next is the query. Write your usual query and refer to p_str as follows:
> SELECT ... FROM ... WHERE col_name IN (&p_str);
>
> And that should do the trick!
>
> Charlton
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

--
Charlton R. Purvis

Advanced Information Systems
College of Engineering Incubator Center

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own