Parameterized Cursors: Help Please.

From: Steve Shiflett <shiflett_at_lmsc.lockheed.com>
Date: 1996/03/25
Message-ID: <315714BE.BF3_at_lmsc.lockheed.com>#1/1

I want to pass a value to a stored procedure for a case insensitive search. Here is what I do:

---

CREATE OR REPLACE PROCEDURE search_movie   ( my_title IN varchar2 )
IS

   answer char;
BEGIN
   DECLARE
   CURSOR c1( c_title char) IS

      SELECT title FROM movie
      WHERE UPPER( title ) LIKE UPPER( '%c_title%' );
   BEGIN
      OPEN c1( my_title );
      LOOP
         FETCH c1 INTO answer;
         dbms_output.new_line;
         dbms_output.put( answer );
         EXIT WHEN c1%C1NOTFOUND;
      END LOOP;

    END;
END;
/
show errors
exec movie_search('g');

---

"Just to be sure", in SQLPLUS, I enter:

select title from movie where upper(title) like upper('%g%');

...and get back two rows.

Then:

exec search_movie('g');

...and get back nothing.

---

Questions:

1. How do I make a case insensitive search with a parameterized cursor?
2. How do I examine the cursor that was constructed, so I can figure out what is going on?

Received on Mon Mar 25 1996 - 00:00:00 CET