Oracle ORA:01031 When Opening RefCursor

Question

Upon Executing the Bellow Procedure The Error ORA : 01031 is Thrown.

if you closely look at this code at the end of the procedure

which is

"v_strSQL := v_strSQL1||' UNION ALL '||v_strSQL2||' UNION ALL '||v_strSQL3||' 
UNION ALL '||v_strSQL4||' UNION ALL '||v_strSQL5||' UNION ALL '||v_strSQL6;"

when i remove union all it works fine but i have to use the union all in my case which is causing problem (Thanks in Advance)

create or replace PROCEDURE  "PR_COUNTS" 
(
  v_CabID1 INT,
  v_CabID2 INT,
  v_ASSIGNTO_DEPT_NO VARCHAR2,
  v_EMP_NO VARCHAR2,
  v_USER_ID VARCHAR2,
  v_HRDB VARCHAR2,
  v_SQL INT,
  tbl_cur out sys_refcursor
) AS

v_MainTable VARCHAR2(20);
v_ActionHistory VARCHAR2(30);
v_Assignment VARCHAR2(30);
v_strSQL1 LONG;
v_strSQL2 LONG;
v_strSQL3 LONG;
v_strSQL4 LONG;
v_strSQL5 LONG;
v_strSQL6 LONG;
v_strSQL LONG;
-- SELECT FN_INLOP_SUBJECTS(I.DOCNO) INTO MAIN_SUBJECT FROM DUAL
BEGin
SELECT   MAIN_TABLE INTO V_MainTable FROM OAEFILE_CABINET WHERE CABINETID =     V_CabID1;    
SELECT  ACTION_HST_TABLE INTO V_ActionHistory FROM OAEFILE_CABINET WHERE     CABINETID=V_CabID1;
SELECT  ASSIGNMENT_TABLE INTO V_Assignment FROM OAEFILE_CABINET WHERE     CABINETID=V_CabID1;


v_strSQL1   :=
'
select  1 COL1,
        1 COL2,
        ''Not Yet Accepted'' COL3,''قيود غير مستلمة'' COL4,
        count(1) COL5,
        ''5503'' COL6
from    '||V_MainTable||' 
WHERE   INDOCNO > ''0'' AND
        BAYAN_FLAG = 1 AND 
        FCNTCT_ID IS NOT NULL AND 
        ASSGNCNTCT_ID = '''||V_EMP_NO||''' AND 
        ASSIGNTO_DEPT_NO = '''||V_ASSIGNTO_DEPT_NO||''' AND
        STATUS_ID IN (2) AND
        DOCDATE BETWEEN 
         CASE 
                WHEN '||v_SQL||' = 1 THEN SYSDATE-30
                WHEN '||v_SQL||' = 2 THEN SYSDATE-90
                WHEN '||v_SQL||' = 3 THEN SYSDATE-365
          END
         AND 
        SYSDATE';

v_strSQL2   :=
'
SELECT  1 COL1,
        2 COL2,
        ''Assigned to Dept'' COL3,''إدارة الوثائق'' COL4,
        COUNT(1) COL5,
        ''309'' COL6
FROM    '||V_MainTable||' 
WHERE   INDOCNO > ''0'' AND
        NUM_FIELD1 IN (0,1) AND 
        BAYAN_FLAG = ''0'' AND
        ASSIGNTO_DEPT_NO = '''||V_ASSIGNTO_DEPT_NO||''' AND 
        ASSGNCNTCT_ID IN (SELECT EMP_NO FROM HR.EMPLS WHERE DEPT_NO =     '''||V_ASSIGNTO_DEPT_NO||''') AND
        STATUS_ID IN (2)  AND
        DOCDATE BETWEEN 
         CASE 
                WHEN '||v_SQL||' = 1 THEN SYSDATE-30
                WHEN '||v_SQL||' = 2 THEN SYSDATE-90
                WHEN '||v_SQL||' = 3 THEN SYSDATE-365
          END
         AND 
        SYSDATE'
;

v_strSQL3   :=
'        
SELECT  1 COL1,
        3 COL2,
        ''Assigned to employees'' COL3,''وثائق تعيين موظف'' COL4,
        COUNT(1) COL5,
        ''309'' COL6
FROM    '||V_MainTable||' 
WHERE   INDOCNO > ''0'' AND
        NUM_FIELD1 IN (0,1) AND 
        BAYAN_FLAG = ''0'' AND
        ASSIGNTO_DEPT_NO = '''||V_ASSIGNTO_DEPT_NO||''' AND 
        ASSGNCNTCT_ID = '''||V_EMP_NO||''' AND
        STATUS_ID IN (2)  AND
        DOCDATE BETWEEN 
         CASE 
                WHEN '||v_SQL||' = 1 THEN SYSDATE-30
                WHEN '||v_SQL||' = 2 THEN SYSDATE-90
                WHEN '||v_SQL||' = 3 THEN SYSDATE-365
          END
         AND 
        SYSDATE'
;

v_strSQL4   :=
'
SELECT  1 COL1,
        4 COL2,
        ''Rejected Docs'' COL3,''قيود مرفوضة'' COL4,
        COUNT(1) COL5,
        ''5504'' COL6
FROM    '||V_MainTable||' A
JOIN    OAEFILE_PROCESS_TYPE B ON A.PROCESS_ID = B.PROCESS_ID AND     USERDEFINED01 = 7
WHERE   INDOCNO > ''0'' AND
        BAYAN_FLAG = ''0'' AND 
        STATUS_ID IN (2) AND 
        FCNTCT_ID IS NOT NULL AND
        A.ASSIGNTO_DEPT_NO = '||V_ASSIGNTO_DEPT_NO||' AND
        A.ASSGNCNTCT_ID = '||V_EMP_NO||' AND 
        DOCDATE BETWEEN 
        CASE 
               WHEN '||v_SQL||' = 1 THEN SYSDATE-30
               WHEN '||v_SQL||' = 2 THEN SYSDATE-90
               WHEN '||v_SQL||' = 3 THEN SYSDATE-365
        END
        AND 
        SYSDATE';

v_strSQL5   :=
'
SELECT  1 COL1,        
        5 COL2,        
        ''Late Documents'' COL3,''Late Documents'' COL4,         
        COUNT(1) COL5,
        ''314'' COL6  
FROM   '||V_MainTable||' A 
LEFT OUTER JOIN OAEFILE_DOC_SOURCE_TYPE G
  ON  A.DOC_ORIGN_SOURCE   = G.DOC_SOURCE_ID AND 
      G.CABINETID         = '||v_CabID1||'
WHERE   INDOCNO > ''0'' AND
        TO_CHAR(A.REMIND_DATE,''YYYY.MM.DD'')<=     TO_CHAR(SYSDATE,''YYYY.MM.DD'') AND 
        A.STATUS_ID IN (2) AND 
        A.OUTDOCNO = ''0'' AND
        A.ASSIGNTO_DEPT_NO = '||V_ASSIGNTO_DEPT_NO||' AND
        A.ASSGNCNTCT_ID = '||V_EMP_NO||' AND 
        A.NUM_FIELD1 IN(0) AND 
        FCNTCT_ID IS NOT NULL AND
        DOCDATE BETWEEN 
         CASE 
                WHEN '||v_SQL||' = 1 THEN SYSDATE-30
                WHEN '||v_SQL||' = 2 THEN SYSDATE-90
                WHEN '||v_SQL||' = 3 THEN SYSDATE-365
          END
         AND 
        SYSDATE'
;

v_strSQL6   :=
'
SELECT  1 COL1,        
        6 COL2,        
        ''About to get Late Documents'' COL3,
        ''About to get Late Documents'' COL4,         
        COUNT(1) COL5,
        ''314'' COL6  
FROM   '||V_MainTable||' A 
LEFT OUTER JOIN OAEFILE_DOC_SOURCE_TYPE G
  ON  A.DOC_ORIGN_SOURCE   = G.DOC_SOURCE_ID AND 
      G.CABINETID         = '||v_CabID1||'
WHERE   INDOCNO > ''0'' AND
        trunc(SYSDATE) - A.REMIND_DATE = 1 AND 
        A.STATUS_ID IN (2) AND 
        A.OUTDOCNO = ''0'' AND
        A.ASSIGNTO_DEPT_NO = '||V_ASSIGNTO_DEPT_NO||' AND
        A.ASSGNCNTCT_ID = '||V_EMP_NO||' AND 
        A.NUM_FIELD1 IN (0) AND 
        FCNTCT_ID IS NOT NULL AND DOCDATE BETWEEN 
         CASE 
                WHEN '||v_SQL||' = 1 THEN SYSDATE-30
                WHEN '||v_SQL||' = 2 THEN SYSDATE-90
                WHEN '||v_SQL||' = 3 THEN SYSDATE-365
          END
         AND 
        SYSDATE'
;

v_strSQL := v_strSQL1||' UNION ALL '||v_strSQL2||' UNION ALL '||v_strSQL3||'     UNION ALL '||v_strSQL4||' UNION ALL '||v_strSQL5||' UNION ALL '||v_strSQL6;

open tbl_cur for v_strSQL;

END;