Parameterized Cursor and the IN SQL clause

From: Steve Chapman <schapman_at_mindspring.com>
Date: Tue, 8 May 2001 22:47:46 -0400
Message-ID: <9dab6u$7n2$1@slb4.atl.mindspring.net>

I have a list of strings to find, pass them into a PL/SQL stored procedure, but interesting things happen if the cursor SQL contains the IN clause.

First create a test table with 2 rows of data:

   CREATE TABLE TEST_TABLE (

     COL1  VARCHAR2 (10),
     COL2  VARCHAR2 (10));

   insert into test_table values ('John','Doe');    insert into test_table values ('Jane','Doe');

Now create the stored procedure using an argument string list, and a parameterized cursor:

   CREATE OR REPLACE PROCEDURE PROC_TEST (in_list_arg varchar2) IS

      CURSOR c1 (in_list_arg varchar2) IS
         SELECT col1, col2 from test_table where col1 in (in_list_arg);
      c1rec c1%ROWTYPE;
   BEGIN
      dbms_output.put_line('in_list_arg='||in_list_arg);
      OPEN c1 (in_list_arg);
      LOOP
         FETCH c1 INTO c1rec;
         dbms_output.put_line('c1rec.col1='||c1rec.col1);
         dbms_output.put_line('c1rec.col2='||c1rec.col2);
         EXIT WHEN c1%NOTFOUND;
      END LOOP;
      CLOSE c1;

   END PROC_TEST;
   /

Q1: Why doesn't this work:

   declare

      var1 varchar2(50) := '''John'',''Jane''';    begin

      proc_test (var1);
   end;

DBMS_OUTPUT Results:

   in_list_arg='John','Jane'
   c1rec.col1=
   c1rec.col2=

You would think that the characters 'John','Jane' get plugged into the cursor query:

   CURSOR c1 (in_list_arg varchar2) IS

      SELECT col1, col2 from test_table where col1 in (in_list_arg);

to produce:

      SELECT col1, col2 from test_table where col1 in ('John','Jane');

Q2: Why doesn't this work:

   declare

      var1 varchar2(50) := 'John';
   begin

      proc_test (var1);
   end;

DBMS_OUTPUT Results:

   in_list_arg=John

   c1rec.col1=John
   c1rec.col2=Doe
   c1rec.col1=John
   c1rec.col2=Doe

Why is John Doe output twice?!?

Thanks! Received on Tue May 08 2001 - 21:47:46 CDT