Re: Variable in cursor where clause

Bill Tierney wrote:
>
> 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;

I don't think this cursor works. You have to use the parameter in the where clause of the cursor select statement; AMARENDRA

-- 
	AMARENDRA B NETTEM
        PH.No. (312)945-2116
        HOMEPAGE:  http://www.iit.edu/~nettama
	E-MAIL  :  nettama_at_charlie.acc.iit.edu