Re: Parameterized Cursors: Help Please.

Steve Shiflett <shiflett_at_lmsc.lockheed.com> wrote:
>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');
>

c_title is a cursor parameter, should not be enclosed in quotes. upper('%c_title%') should be replaced with %||upper(c_title)||'%'

-- 
Sanjay D.S.
Oracle Consultant
Prudential Securities, Inc.