Re: PL/SQL Help! Trying to Use Variable String as Argument in "where in" clause
Date: Wed, 20 May 1998 06:45:43 GMT
Message-ID: <6jtu6p$cup$1_at_nnrp1.dejanews.com>
Hi
> >I'm trying to use a variable defined as a string of values in a SQL
> >select statement. So far, defining a variable as a string of values
> >works when I run the SQL but doesn't select anything when I try to
> >execute the same statement as part of a PL/SQL procedure's cursor.
> >Any tips?
You have to use dynamic SQL , this allows you to create SQL statements dynamically in your procedure and then execute them
Here is an example. It a bit long and it applies to web development but look for the DBMS_SQL code
CREATE OR REPLACE PROCEDURE
multi_sic_query_text IS
multisic owa_cookie.cookie;
num_cookies number;
c_count number;
offset number;
sub_text varchar2(5);
query_text varchar2(1000);
--These are cursor handles--
q_cursor integer;
ignore integer;
- cursor variables ----- tradename varchar2(200); sic varchar2(5);
BEGIN
query_text := 'select tradename,sic from sotiri.levy,sotiri.siclevy where
payernumber = levyno and ( ';
htp.br;
--get the cookie
multisic := owa_cookie.get('multisic'); --initialise the loop variables
c_count := 1; offset := 1; --check to see that a cookie value has actually been set IF (multisic.num_vals > 0) THEN -- devideby five to get the number of sic codes in the cookie string num_cookies := Length(multisic.vals(1)) / 5; --Increase the loop counter by one so that the right number of iterations can be performed num_cookies := num_cookies+1; while c_count < num_cookies loop -- the first where statement does not have an or in front of ittherefore trap the first one
if c_count = 1 then sub_text := substr(multisic.vals(1),offset,5); sub_text := ltrim(sub_text,'0'); c_count := c_count + 1; offset := offset + 5; query_text := query_text || ' sic like ''' || sub_text || '%''' ; end if; if c_count > 1 and c_count < num_cookies then sub_text := substr(multisic.vals(1),offset,5); sub_text := ltrim(sub_text,'0'); c_count := c_count + 1; offset := offset + 5; query_text := query_text || ' or sic like ''' || sub_text || '%''' ; end if; end loop;
htp.br;
htp.br;
- Add the last bracket (or parensesis if you prefer) to the query
query_text := query_text ||' ) ';
- Execute the query
q_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(q_cursor,query_text,1);
DBMS_SQL.DEFINE_COLUMN(q_cursor,1,tradename,200); DBMS_SQL.DEFINE_COLUMN(q_cursor,2,sic,5);
ignore := DBMS_SQL.EXECUTE(q_cursor);
- -- --This is the loop that Executes the fetch on the cursor and prints theresults
-- ------------------------------------------------------------------------------ - LOOP IF DBMS_SQL.FETCH_ROWS(q_cursor)>0 THEN -- get column values of the row DBMS_SQL.COLUMN_VALUE(q_cursor, 1, tradename); DBMS_SQL.COLUMN_VALUE(q_cursor, 2, sic); htp.print(tradename|| ','||sic ) ; htp.p('
'); ELSE -- no more row to copy EXIT; END IF; END LOOP; --Close the cursor DBMS_SQL.CLOSE_CURSOR(q_cursor); END IF; END; / ------------------------------------------------------------------------ -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreadingReceived on Wed May 20 1998 - 08:45:43 CEST