Mega Code Archive
Create pattern dynamically and use it in like statement
<![CDATA[
SQL>
SQL>
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'E', 'F');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');
1 row created.
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE author_sel(i_lname IN emp.lname%TYPE,cv_author IN OUT SYS_REFCURSOR) IS
2 lnameValue emp.lname%TYPE;
3 BEGIN
4
5 lnameValue := '%'||UPPER(i_lname)||'%';
6
7 OPEN cv_author FOR SELECT id, fname, lname FROM emp WHERE UPPER(lname) LIKE lnameValue;
8
9 EXCEPTION
10 WHEN OTHERS
11 THEN
12 DBMS_OUTPUT.PUT_LINE(sqlerrm);
13 END;
14 /
Procedure created.
SQL>
SQL> COL fname FORMAT A20
SQL> COL lname FORMAT A20
SQL>
SQL> VARIABLE x REFCURSOR
SQL> EXEC author_sel('rin', :x)
PL/SQL procedure successfully completed.
SQL>
SQL> print x
no rows selected
SQL>
SQL>
SQL> drop table emp;
Table dropped.
]]>