Re: PL/SQL Help! Trying to Use Variable String as Argument in "where in" clause

From: <bonanos_at_yahoo.com>
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 it
therefore 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 the
results
--
------------------------------------------------------------------------------
-
  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('&#10');


    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 newsreading
Received on Wed May 20 1998 - 08:45:43 CEST

Original text of this message