Variable in cursor where clause

From: Bill Tierney <wtierney_at_stanford.edu>
Date: 1996/09/05
Message-ID: <322F6F64.23E7_at_stanford.edu>#1/1


I'm trying to dynamically create my
where clause and then pass it to my
cursor .. but it doesn't seem to work.

I'm using p_query_str to pass the where
clause in. I realize that the way
I'm making the where clause is chunky
but I'm still learning to crawl..

The program acts as though the where
clause doesn't exit. I looked at the
pl/sql manual and it had a basic example but ....

Below is the code

Any suggestion is greatly appreciated...



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

   p_query_str := 'WHERE ';
end if;

--
 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(

'<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;
Received on Thu Sep 05 1996 - 00:00:00 CEST

Original text of this message