SQL & PL/SQL

CREATE OR REPLACE PACKAGE BODY Pkg_Timesheet AS

PROCEDURE Proc_Timesheet (periodid IN NUMBER,
rProjTask OUT refcur,
rNonProjTask OUT refcur,
rBenchTmMiss OUT refcur,
rIncompTmsWrkDay OUT refcur,
rBnchTmWrkDy OUT refcur,
rBnchTm8hrsWrkDy OUT refcur)
AS

CURSOR Cur_Month(crPeriod IN NUMBER) IS
SELECT id,
TO_DATE('1-'||SUBSTR(period,1,3)||'-'||SUBSTR(period,INSTR(period,'/',1)+1),'DD-MON-RRRR') dt
FROM TBL_PERIOD
WHERE id=crPeriod;

CURSOR Cur_WorkingDays(crFirstDayMonth IN VARCHAR2) IS
WITH this_month AS (
SELECT TRUNC(TO_DATE(crFirstDayMonth,'mm/dd/yyyy'), 'mm')+LEVEL-1 dy
FROM dual
CONNECT BY LEVEL < (TRUNC(ADD_MONTHS(TO_DATE(crFirstDayMonth,'mm/dd/yyyy'),1), 'mm')- TRUNC(TO_DATE(crFirstDayMonth,'mm/dd/yyyy'), 'mm'))+1
)
SELECT 'Day'||LTRIM(SUBSTR(TO_CHAR(dy,'mm/dd/yyyy'),(INSTR(TO_CHAR(dy,'mm/dd/yyyy'),'/',1,1)+1),
(INSTR(TO_CHAR(dy,'mm/dd/yyyy'),'/',1,2)-(INSTR(TO_CHAR(dy,'mm/dd/yyyy'),'/',1,1)+1))),'0') days
, dy
, DAY
FROM(
SELECT
dy
, TO_CHAR(dy, 'dy') dy3
, TO_CHAR(dy, 'day') DAY
FROM this_month
WHERE TO_CHAR(dy, 'dy') NOT IN ('sat', 'sun'));


crPeriodId NUMBER(5) := 0;
crFirstdayMonthDt DATE := NULL;
crFirstdayMonthChar VARCHAR2(100) :=NULL;
i NUMBER(5) :=0;
column_days VARCHAR2(4000):=NULL;
where_days VARCHAR2(4000):=NULL;
where_bench_8 VARCHAR2(4000):=NULL;
vSql1 VARCHAR2(4000):=NULL;
vSql2 VARCHAR2(4000):=NULL;
vSql3 VARCHAR2(4000):=NULL;
nIncompTmsWrkDay NUMBER(5) :=0;
nBnchTmWrkDy NUMBER(5) :=0;
nBnchTm8hrsWrkDy NUMBER(5) :=0;

BEGIN

OPEN Cur_Month(periodid);
FETCH Cur_Month INTO crPeriodId,crFirstdayMonthDt ;
CLOSE Cur_Month;

DBMS_OUTPUT.PUT_LINE(crFirstdayMonthDt);

SELECT ''''||TO_CHAR(crFirstdayMonthDt,'MM/DD/RRRR')||'''' INTO crFirstdayMonthChar FROM DUAL;

DBMS_OUTPUT.put_line(crFirstdayMonthChar);

i:=1;
--FOR rec IN Cur_WorkingDays(crFirstdayMonthChar) LOOP
FOR rec IN Cur_WorkingDays('06/01/2009') LOOP

IF(i=1) THEN
column_days:= rec.days;
ELSIF(i>1)THEN
column_days:=column_days||','||rec.days;
END IF;

IF(i=1) THEN
where_days:= rec.days||'<8';
ELSIF(i>1)THEN
where_days:=where_days||' OR '||rec.days||'<8';
END IF;

IF(i=1) THEN
where_bench_8:= rec.days||'=8';
ELSIF(i>1)THEN
where_bench_8:=where_days||' OR '||rec.days||'=8';
END IF;

i:=i+1;
END LOOP;

/*Project Task*/
OPEN rProjTask
FOR SELECT * FROM TBL_TIMESHEET
WHERE projectname NOT LIKE '%Non-Project Time%'
OR projectname NOT LIKE '%Bench Time%';

/*Non Project Task*/
OPEN rNonProjTask
FOR SELECT * FROM TBL_TIMESHEET
WHERE projectname LIKE '%Non-Project Time%'
OR projectname LIKE '%Bench Time%';

/*Bench Time Missing*/
OPEN rBenchTmMiss
FOR SELECT * FROM TBL_TIMESHEET
WHERE name NOT IN (SELECT DISTINCT name FROM TBL_TIMESHEET WHERE projectname LIKE '%Bench Time%');

/*Incomplete Timesheet on Working Days*/
vSql1:= 'SELECT distinct period,name,projectname,'||column_days||' FROM TBL_TIMESHEET '||
' WHERE projectname LIKE '||''''||'SUMMARY'||''''||
' AND period ='||periodid||
' AND ('||where_days||')';
DBMS_OUTPUT.PUT_LINE(vSql1);
EXECUTE IMMEDIATE 'CREATE TABLE incomplete_timesheet AS '||vSql1;
nIncompTmsWrkDay:=0;
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME='||''''||'INCOMPLETE_TIMESHEET'||'''' INTO nIncompTmsWrkDay;
IF(nIncompTmsWrkDay>0)THEN
EXECUTE IMMEDIATE ' DECLARE '||
--' rincomptmswrkday REF CURSOR; '||
' rincomptmswrkday Pkg_Timesheet.refcur;'||
' BEGIN '||
' OPEN rincomptmswrkday '||
' FOR SELECT * FROM incomplete_timesheet; '||
' END;';
EXECUTE IMMEDIATE 'DROP TABLE incomplete_timesheet';
END IF;

/*Bench Time on Working Days*/
vSql2:= 'SELECT distinct period,name,projectname,'||column_days||' FROM TBL_TIMESHEET '||
' WHERE (projectname LIKE '||''''||'%Non-Project Time%'||''''||
' OR projectname LIKE '||''''||'%Bench Time%'||''''||')'||
' AND period ='||periodid;
DBMS_OUTPUT.PUT_LINE(vSql2);
EXECUTE IMMEDIATE 'CREATE TABLE benchtime_workingday AS '||vSql2;
nBnchTmWrkDy:=0;
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME='||''''||'BENCHTIME_WORKINGDAY'||'''' INTO nBnchTmWrkDy;
IF(nBnchTmWrkDy>0)THEN
EXECUTE IMMEDIATE ' DECLARE '||
--' rBnchTmWrkDy REF CURSOR;'||
' rBnchTmWrkDy Pkg_Timesheet.refcur;'||
' BEGIN '||
' OPEN rBnchTmWrkDy '||
' FOR SELECT * FROM benchtime_workingday; '||
' END;';
EXECUTE IMMEDIATE 'DROP TABLE benchtime_workingday';
END IF;

/*Bench Time 8 hours on working days */
vSql3:= 'SELECT distinct period,name,projectname,'||column_days||' FROM TBL_TIMESHEET '||
' WHERE (projectname LIKE '||''''||'%Non-Project Time%'||''''||
' OR projectname LIKE '||''''||'%Bench Time%'||''''||')'||
' AND period ='||periodid||
' AND ('||where_bench_8||')';
DBMS_OUTPUT.PUT_LINE(vSql3);
EXECUTE IMMEDIATE 'CREATE TABLE bench8hrs_wrkday AS '||vSql3;
nBnchTm8hrsWrkDy:=0;
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME='||''''||'BENCH8HRS_WRKDAY'||'''' INTO nBnchTm8hrsWrkDy;
IF(nBnchTm8hrsWrkDy>0)THEN
EXECUTE IMMEDIATE ' DECLARE '||
--' rBnchTm8hrsWrkDy REF CURSOR;'||
' rBnchTm8hrsWrkDy Pkg_Timesheet.refcur;'||
' BEGIN '||
' OPEN rBnchTm8hrsWrkDy '||
' FOR SELECT * FROM bench8hrs_wrkday; '||
' END; ';
EXECUTE IMMEDIATE 'DROP TABLE bench8hrs_wrkday';
END IF;


END Proc_Timesheet;

END Pkg_Timesheet;

When I'm executing the procedure it's being executed.But when am trying to see the output of the last three ref cursors am getting the error, But the first three ref cursor which are not being execute in "EXECUTE IMMEDIATE" is showing proper results
ORA-24338: statement handle not executed

Help required