Re: Variable in cursor where clause

From: <gsalem_at_fr.oracle.com>
Date: 1996/09/06
Message-ID: <32304CF0.5CA6_at_fr.oracle.com>#1/1


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;
> --
>......hi,
this will not even compile correctly,
you should use dynamic sql (using the dbms_sql package) to do what you want:
the sql statement you pass to dbms_sql.parse will be constructed using the fixed part concatenated to p_query_str hth Received on Fri Sep 06 1996 - 00:00:00 CEST

Original text of this message