Xref: alice comp.databases.oracle.tools:24362
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: My soln to passing an IN clause to Oracle Reports 2.5
Date: Wed, 05 May 1999 14:55:17 GMT
Organization: Deja News - The Leader in Internet Discussion
Lines: 105
Message-ID: <7gpm4i$js5$1@nnrp1.dejanews.com>
X-Article-Creation-Date: Wed May 05 14:55:17 1999 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 4.01; Windows 95)
X-Http-Proxy: 1.1 x4.dejanews.com:80 (Squid/1.1.22) for client 129.252.22.247

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