My soln to passing an IN clause to Oracle Reports 2.5
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
(
str VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
Return(REPLACE(Wrap_SUBSTR_In(str,',',''''),''' ''',''','''));
END;
- PROGRAM UNIT
(
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
(
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
(
str VARCHAR2, delim VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
Return(SUBSTR(str,LENGTH(Get_Top_String(str,delim))+2)); END;
- EXPLANATION CONTINUED
- 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