Home » Developer & Programmer » Application Express & MOD_PLSQL » Query Process Help (Oracle 10g / Apex 2.1.2)
Query Process Help [message #351947] Fri, 03 October 2008 15:49 Go to next message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
Hello...

I've built an app that contains a report search tool.
You punch in your search criteria in the search fields, hit submit and it produce results.
The results are produced with the following pl/sql quoted at the end of the message. This element works fine.

I also have a but to clear the search fields... that's no problem as well.

My problem is that I would like to include a button "SHOWALLRECORDS" that, when clicked will query table and return all results. I've tried to incorporate this into the PL/SQL below with no luck.

Any suggestions would be appreciated.

Thank You

PL/SQL BELOW

DECLARE
v_p2_searchgeoid varchar2(200);
v_p2_searchwater varchar2(200);
v_p2_searchstreet varchar2(200);
query VARCHAR(32767);
wexists BOOLEAN;
BEGIN
query := 'select * from "AWBINORDER" '
;
wexists := false;
v_p2_searchgeoid := NULL;
v_p2_searchwater := NULL;
v_p2_searchstreet := NULL;

If :REQUEST in ('SHOWALLRECORDS') then
query := query;
end if;

if v('P2_SEARCHSTREET')is null then
v_p2_searchstreet := NULL;
ELSIF (wexists) THEN
v_p2_searchstreet := ' AND STREET_NAME like ' || chr(39) || upper(Trim(:P2_SEARCHSTREET)) || '%' || chr(39) ;

else v_p2_searchstreet := ' awbinorder.stname = :P2_SEARCHSTREET ';

wexists := true;

end if;
--

if v('P2_SEARCHGEOID')is null then
v_p2_searchgeoid := NULL;

ELSIF (wexists) THEN
v_p2_searchgeoid := ' AND GEO_ID like ' || chr(39) || upper(Trim(:P2_SEARCHGEOID)) || '%' || chr(39) ;

else v_p2_searchgeoid := ' awbinorder.GEO_ID = :P2_SEARCHGEOID ' ;

wexists := true;
end if;

--

if v('P2_SEARCHWATER')is null then
v_p2_searchwater := NULL;
ELSIF (wexists) THEN
v_p2_searchwater := ' AND WATERACCTNUM like ' || chr(39) || upper(Trim(:P2_SEARCHWATER)) || '%' || chr(39) ;
else v_p2_searchwater := ' awbinorder.wateracctnum = :P2_SEARCHWATER ';
wexists := true;
end if;

--
IF (wexists) THEN
query := query || ' WHERE ' || v_p2_searchgeoid || v_p2_searchwater || v_p2_searchstreet;
else query := query || ' WHERE awbinorderuid is null ';
END IF;

return query;

END;


Re: Query Process Help [message #351966 is a reply to message #351947] Sat, 04 October 2008 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Query Process Help [message #352204 is a reply to message #351966] Mon, 06 October 2008 10:41 Go to previous message
greendba
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
Apologies for the rule violation.

I managed to trace the problem. It was PL/SQL coding order and then linking the request to the a branch forcing the page to reload.

Thanks to all who assisted.

Cheers
Eric
Previous Topic: User's IP address
Next Topic: Display image in HTML region???
Goto Forum:
  


Current Time: Fri Aug 01 09:44:06 CDT 2014

Total time taken to generate the page: 0.08278 seconds