This might give you an idea: create table Customer ( c_firstname varchar2(50), c_lastname varchar2(50), c_userid varchar2(50) ); insert into Customer values ('Micky' , 'Mouse', 'mm'); insert into Customer values ('Donald', 'Duck' , 'dd'); insert into Customer values ('Peter' , 'Pan' , 'pp'); create or replace function GetCustomer( FirstN varchar2 := null, LastN varchar2 := null, CID varchar2 := null ) return sys_refcursor as stmt varchar2(4000); ret sys_refcursor; begin stmt := 'select * from Customer where 1=1'; if FirstN is not null then stmt := stmt || ' and c_firstname like ''%' || FirstN || '%'''; end if; if LastN is not null then stmt := stmt || ' and c_lastname like ''%' || LastN || '%'''; end if; if CID is not null then stmt := stmt || ' and c_userid like ''%' || CID || '%'''; end if; dbms_output.put_line(stmt); open ret for stmt; return ret; end; / Later, in SQL*Plus: set serveroutput on size 100000 format wrapped declare c sys_refcursor; fn Customer.c_firstname%type; ln Customer.c_lastname %type; id Customer.c_userid %type; begin c := GetCustomer(LastN => 'u'); fetch c into fn, ln, id; while c%found loop dbms_output.put_line('First Name: ' || fn); dbms_output.put_line('Last Name: ' || ln); dbms_output.put_line('user id: ' || id); fetch c into fn, ln, id; end loop; close c; end; / **Edit**: The comment is right, and the procedure is subject to *SQL injection*. So, in order to prevent that, you could go with bind variables such as in this modified procedure: create or replace function GetCustomer( FirstN varchar2 := null, LastN varchar2 := null, CID varchar2 := null ) return sys_refcursor as stmt varchar2(4000); ret sys_refcursor; type parameter_t is table of varchar2(50); parameters parameter_t := parameter_t(); begin stmt := 'select * from Customer where 1=1'; if FirstN is not null then parameters.extend; parameters(parameters.count) := '%' || FirstN || '%'; stmt := stmt || ' and c_firstname like :' || parameters.count; end if; if LastN is not null then parameters.extend; parameters(parameters.count) := '%' || LastN || '%'; stmt := stmt || ' and c_lastname like :' || parameters.count; end if; if CID is not null then parameters.extend; parameters(parameters.count) := '%' || CID || '%'; stmt := stmt || ' and c_userid like :' || parameters.count; end if; if parameters.count = 0 then open ret for stmt; elsif parameters.count = 1 then open ret for stmt using parameters(1); elsif parameters.count = 2 then open ret for stmt using parameters(1), parameters(2); elsif parameters.count = 3 then open ret for stmt using parameters(1), parameters(2), parameters(3); else raise_application_error(-20800, 'Too many parameters'); end if; return ret; end; / Note, that now, whatever the input, the select statement becomes something like `select ... from ... where 1=1 and col1 like :1 and col2 :2 ...` which is obviously much safer.