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%' );
..

Your procedure searches for movies with an upper(title) containing the string 'C_TITLE'. However, you need the value stored in the parameter c_title. Try:
.. WHERE UPPER (title) LIKE UPPER ('%'||c_title||'%');

If you ever decide to have an index on movie.title, consider forcing titles to be uppercase:

   ALTER TABLE movie ADD CONSTRAINT movie_title_upper    CHECK (title = UPPER (title) );
The WHERE clause changes to
.. WHERE title LIKE UPPER ('%'||c_title||'%');

The query will use the index and thereby perform much better for large tables. The performance of INSERTs, UPDATEs of title and DELETEs will be decreased by the index and by the CHECK constraint (INSERT/UPDATE only).

Regards, Klaus

-- 
Klaus Eichler       | Haus 2, 3C7.1       | email ek2918_at_m30x.nbg.scn.de
Siemens AG, AUT 652 | Moorenbrunn         |
Postfach 4848       | Gleiwitzer Str. 555 | Tel   +49 911 895-2918
D-90327 Nuernberg   | D-90475 Nuernberg   | Fax   +49 911 895-4802