Xref: alice comp.databases.oracle.tools:24369
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!nntp2.dejanews.com!nnrp1.dejanews.com!not-for-mail
From: Charlton Purvis <purvis386@my-dejanews.com>
Newsgroups: comp.databases.oracle.tools
Subject: Re: My soln to passing an IN clause to Oracle Reports 2.5
Date: Wed, 05 May 1999 16:02:17 GMT
Organization: Deja News - The Leader in Internet Discussion
Lines: 125
Message-ID: <7gpq26$nk5$1@nnrp1.dejanews.com>
References: <7gpm4i$js5$1@nnrp1.dejanews.com>
X-Article-Creation-Date: Wed May 05 16:02:17 1999 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 4.01; Windows 95)
X-Http-Proxy: 1.1 x7.dejanews.com:80 (Squid/1.1.22) for client 129.252.22.247

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@nnrp1.dejanews.com>,
  Charlton Purvis <purvis386@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