My soln to passing an IN clause to Oracle Reports 2.5

From: Charlton Purvis <purvis386_at_my-dejanews.com>
Date: Wed, 05 May 1999 14:55:17 GMT
Message-ID: <7gpm4i$js5$1_at_nnrp1.dejanews.com>



[Quoted] 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 commadelimited  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 Received on Wed May 05 1999 - 16:55:17 CEST

Original text of this message