create or replace package  pg_people  
is  
 procedure ParameterizedSQL  (pFirstName NVARCHAR2, pLastName NVARCHAR2 
, oPeople out sys_refcursor) ; 
 
 end;

create or replace package body pg_people    
as    
 procedure ParameterizedSQL  (pFirstName NVARCHAR2, pLastName NVARCHAR2   
, oPeople out sys_refcursor)    
 is    
 optionalCriteria NVARCHAR2(2000) := N'';   
sqlStmt NVARCHAR2(32767) := N'';   
BEGIN	   
   
IF(pFirstName IS NOT NULL) THEN   
	optionalCriteria := N' and First_Name = :firstName';   
END IF;   
   
sqlStmt := N'SELECT employee_id, last_name FROM HR.EMPLOYEES WHERE Last_Name = :lastName' || optionalCriteria;   
   
OPEN oPeople FOR sqlStmt USING pLastName, pFirstName;   
 end;    
end;

declare 
 
pFirstName NVARCHAR2(200) := 'Steven'; 
pLastName NVARCHAR2(200) := 'King'; 
 
oPeople sys_refcursor; 
type p1 is record (a1 number, a2 varchar2(50));  
 
begin  
 
pg_people.ParameterizedSQL  (pFirstName, pLastName, oPeople) ; 
  
end; 

declare 
 
pFirstName NVARCHAR2(200) := null; 
pLastName NVARCHAR2(200) := 'King'; 
 
oPeople sys_refcursor; 
type p1 is record (a1 number, a2 varchar2(50));  
 
begin  
 
pg_people.ParameterizedSQL  (pFirstName, pLastName, oPeople) ; 
  
 
  
end;