SQL & PL/SQL

Hello,

Here is my procedure which will get the set of result into SYS_REFCURSOR as OUT parameter.
The problem is :
if p_job is null then we will get "ORA-01006: bind variable does not exist"

Could anybody please let me know how to solve this problem, or please provide better way to put the set of result into SYS_REFCURSOR.

Thanks in advance!



CREATE OR REPLACE PROCEDURE pr_test
(p_start_date IN DATE,
p_end_date IN DATE,
p_job IN varchar2,
result_set IN OUT SYS_REFCURSOR)
AS
v_sql_string varchar2(2000);

BEGIN

v_sql_string := 'SELECT * FROM emp WHERE HIREDATE between :1 AND :2';

IF p_job is not null THEN
v_sql_string := v_sql_string || ' AND job = :3';
END IF;

OPEN result_set
FOR v_sql_string USING p_start_date, p_end_date, p_job;

END pr_test;
/