Use the LIKE operator : Like « PL SQL Operators « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Operators
3. Like

SQL>
SQL> set feedback on escape ~
SQL>
SQL> CREATE TABLE authors (
  2    id         NUMBER PRIMARY KEY,
  3    first_name VARCHAR2(50),
  4    last_name  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (1, 'Marlene', 'Theriault');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (2, 'Rachel', 'Carmichael');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (3, 'James', 'Viscusi');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE author_sel (
  2     i_last_name IN AUTHORS.LAST_NAME%TYPE,
  3     cv_author IN OUT SYS_REFCURSOR)
  4  IS
  5     v_last_name AUTHORS.LAST_NAME%TYPE;
  6  BEGIN
  7
  8     v_last_name := '%'||UPPER(i_last_name)||'%';
  9
 10     OPEN cv_author FOR
 11     SELECT id, first_name, last_name
 12     FROM authors
 13     WHERE UPPER(last_name) LIKE v_last_name;
 14
 15  EXCEPTION
 16     WHEN OTHERS
 17     THEN
 18        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 19  END;
 20  /

Procedure created.

SQL>
SQL> COL first_name FORMAT A20
SQL> COL last_name FORMAT A20
SQL>
SQL> VARIABLE x REFCURSOR
SQL> EXEC author_sel('u', :x)

PL/SQL procedure successfully completed.

SQL>
SQL> print x


        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 Marlene              Theriault
         3 James                Viscusi

2 rows selected.

SQL> drop table authors;

Table dropped.