Variable in cursor where clause
Date: 1996/09/05
Message-ID: <322F6F64.23E7_at_stanford.edu>#1/1
I'm trying to dynamically create my
I'm using p_query_str to pass the where
The program acts as though the where
Below is the code
Any suggestion is greatly appreciated...
p_query_str := 'WHERE ';
where clause and then pass it to my
cursor .. but it doesn't seem to work.
clause in. I realize that the way
I'm making the where clause is chunky
but I'm still learning to crawl..
clause doesn't exit. I looked at the
pl/sql manual and it had a basic example
but ....
PROCEDURE do_it2 (
p_damname IN VARCHAR2 DEFAULT NULL,
p_damid IN VARCHAR2 DEFAULT NULL,
p_state IN VARCHAR2 DEFAULT NULL,
p_damtype IN VARCHAR2 DEFAULT NULL,
p_damhtmin IN VARCHAR2 DEFAULT NULL,
p_damhtmax IN VARCHAR2 DEFAULT NULL,
p_incident IN VARCHAR2 DEFAULT NULL)
AS
p_where number;
p_query_str VARCHAR(500);
CURSOR search_CUR (p_query_str char) IS
SELECT DAM_NAME,
OTHER_DAM_NAMES,
NATIONAL_ID,
DAM_TYPE,
DOWNSTREAM_HAZARD,
DAM_HEIGHT,
EMERGENCY_ACTION_PLAN,
NPDP
FROM nid_table p_query_str;
link_REC search_CUR%ROWTYPE;
BEGIN
if ((p_damname is NOT NULL) OR (p_damid is NOT NULL)) then
-- if ( p_damname is NOT NULL ) then p_query_str := p_query_str || '(DAM_NAME like "%'; p_query_str := p_query_str || upper(p_damname); p_query_str := p_query_str || '%")'; end if; -- htp.htmlOpen; htp.headOpen; htp.Title ( 'Dam Query Results' ); htp.headClose; htp.bodyOpen('',''); htp.p( '<h1><img src="../images/npdp3.gif"> Dam Query Results </h1> ' || '<img src="../gifs/line.marble1.gif"><p>' ); htp.p( '<P>' ); htp.tableOpen; do_col_headings; OPEN search_CUR(p_query_str); LOOP FETCH search_CUR INTO link_REC; EXIT WHEN search_CUR%NOTFOUND; htp.p(Received on Thu Sep 05 1996 - 00:00:00 CEST
'<TR>' ||
'<TD>' || link_REC.dam_name || '</TD>' ||
'<TD>' || link_REC.other_dam_names || '</TD>' ||
'<TD>' || link_REC.national_id || '</TD>' ||
'<TD>' || link_REC.dam_type || '</TD>' ||
'<TD>' || link_REC.dam_height || '</TD>' ||
'<TD>' || link_REC.downstream_hazard || '</TD>' ||
'<TD>' || link_REC.emergency_action_plan || '</TD>' ||
'<TD>' || link_REC.npdp || '</TD>' ||
'</TR>'
); -- write_the_output( link_REC.national_id); n := add_one( n ); IF n = max_search_hits THEN max_exceeded := TRUE; END IF; EXIT WHEN n = max_search_hits; END LOOP; CLOSE search_CUR; htp.tableClose; htp.bodyClose; htp.htmlClose; END do_it2;