1 Reply Latest reply: Jun 25, 2012 1:09 AM by Johnreardon RSS

    How to pass values in select statement as a parameter?

    923648
      Hi,

      Very simple query, how do I pass the values that i get in the cursor to a select statement. If table1 values are 1,2,3,4 etc , each time the cursor goes through , I will get one value in the variable - Offer
      So I want to pass that value to the select statement.. how do i do it?
      the one below does not work.
      drop table L1;
      create table L1
      (col1 varchar(300) null) ;

      insert into L1 (col1)
      select filter_name from table1 ;

      SET SERVEROUTPUT ON;
      DECLARE
      offer table1.col1%TYPE;
      factor INTEGER := 0;
      CURSOR c1 IS
      SELECT col1 FROM table1;
      BEGIN
      OPEN c1; -- PL/SQL evaluates factor
      LOOP
      FETCH c1 INTO offer;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(offer);
      select * from table1 f where f.filter_name =:offer ;
      factor := factor + 1;
      DBMS_OUTPUT.PUT_LINE(factor);
      END LOOP;

      CLOSE c1;
      END;