CREATE OR REPLACE PACKAGE BODY PACK_SEMSADMIN_OFFEREDJOBS
AS
PROCEDURE Admin_Jobs_Offered_Rtr
(
    ic_status           IN      VARCHAR2,
    or_offered_jobs     OUT     Generic_Cursor_Type,
    or_applicants_list  OUT     Generic_Cursor_Type
)
AS
    vc_query    VARCHAR2(15000)     :=  '';
    vc_query_1  VARCHAR2(15000)     :=  '';
        
    TYPE type_appln_list IS RECORD
    (
        job_no          NUMBER(10), 
        job_title       VARCHAR2(50 BYTE),
        account_no      VARCHAR2(10 BYTE),
        head_fname      VARCHAR2(32 BYTE),
        head_minitial   VARCHAR2(10 BYTE),
        head_lname      VARCHAR2(32 BYTE),
        num             NUMBER
    );
    vn_appln_list type_appln_list;
        
BEGIN

    vc_query := vc_query || ' SELECT jobs.job_no,job_title, account_no, head_fname, head_minitial, head_lname, num';
    vc_query := vc_query || ' FROM jobs, ( ' ;
    vc_query := vc_query || '           SELECT jobs.job_no,count(*) as num' ;
    vc_query := vc_query || '           FROM student_apps ,jobs ' ;
    vc_query := vc_query || '           WHERE jobs.job_no = student_apps.job_no' ;
    vc_query := vc_query || '           AND (student_apps.status in ' || ic_status || ')' ;
    vc_query := vc_query || '           AND jobs.status not in (''z'', ''Z'')' ;
    vc_query := vc_query || '           GROUP BY jobs.job_no' ;
    vc_query := vc_query || '           ) no_apps_off' ;
    vc_query := vc_query || ' WHERE jobs.job_no = no_apps_off.job_no' ;
    
    OPEN or_offered_jobs FOR vc_query;
    
    LOOP
        FETCH or_offered_jobs INTO vn_appln_list;
        EXIT WHEN or_offered_jobs%NOTFOUND;
        
        vc_query_1 := '';
            
        vc_query_1 := vc_query_1 || ' INSERT INTO TEMP_JOB_DETAILS (JOB_NO, APPL_NO, S_FNAME, S_MI, ';
        vc_query_1 := vc_query_1 || ' S_LNAME, APPL_DATE, DESCRIPTION, S_UCID) ';
        vc_query_1 := vc_query_1 || ' SELECT jbs.job_no, stud_apps.appl_no, stud_apps.s_fname, ';
        vc_query_1 := vc_query_1 || ' stud_apps.s_mi, stud_apps.s_lname, '; 
        vc_query_1 := vc_query_1 || ' stud_apps.appl_date, look_up.description, ';
        vc_query_1 := vc_query_1 || ' stud_apps.s_ucid ';
        vc_query_1 := vc_query_1 || ' FROM student_apps stud_apps,jobs jbs,lookup look_up ';
        vc_query_1 := vc_query_1 || ' WHERE stud_apps.status in (''o'',''t'') '; --(' || ic_status || ') ' ;
        vc_query_1 := vc_query_1 || ' AND jbs.job_no = stud_apps.job_no ';
        vc_query_1 := vc_query_1 || ' AND jbs.status not in (''z '', ''Z'') ';
        vc_query_1 := vc_query_1 || ' AND stud_apps.status = look_up.code ';
        vc_query_1 := vc_query_1 || ' AND look_up.type = ''st'' ';
        vc_query_1 := vc_query_1 || ' AND stud_apps.job_no = ''' || vn_appln_list.job_no || ''' ';
            
        EXECUTE IMMEDIATE vc_query_1;
                       
    END LOOP;
    
   OPEN or_applicants_list FOR        
        SELECT JOB_NO, APPL_NO, S_FNAME, S_MI, S_LNAME, APPL_DATE, DESCRIPTION, S_UCID
        FROM TEMP_JOB_DETAILS
        ORDER BY JOB_NO, APPL_DATE;
        
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_JOB_DETAILS';
    commit;
    
END Admin_Jobs_Offered_Rtr;

java.sql.SQLException: ORA-01002: fetch out of sequence

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
        at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1198)
        at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:497)
        at oracle.jdbc.driver.OracleStatement.getCursorValue(OracleStatement.java:3684)
        at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5792)
        at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5622)
        at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:698)

01002, 00000, "fetch out of sequence"
// *Cause: This error means that a fetch has been attempted from a cursor
//         which is no longer valid.  Note that a PL/SQL cursor loop
//         implicitly does fetches, and thus may also cause this error.
//         There are a number of possible causes for this error, including:
//         1) Fetching from a cursor after the last row has been retrieved
//            and the ORA-1403 error returned.
//         2) If the cursor has been opened with the FOR UPDATE clause,
//            fetching after a COMMIT has been issued will return the error.
//         3) Rebinding any placeholders in the SQL statement, then issuing
//            a fetch before reexecuting the statement.
// *Action: 1) Do not issue a fetch statement after the last row has been
//             retrieved - there are no more rows to fetch.
//          2) Do not issue a COMMIT inside a fetch loop for a cursor
//             that has been opened FOR UPDATE.
//          3) Reexecute the statement after rebinding, then attempt to
//             fetch again.

SCOTT@orcl_11g> CREATE TABLE temp_job_details AS SELECT * FROM dept WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE PACK_SEMSADMIN_OFFEREDJOBS
  2  AS
  3    TYPE Generic_Cursor_Type IS REF CURSOR;
  4  PROCEDURE Admin_Jobs_Offered_Rtr
  5  (
  6  	 ic_status	     IN      VARCHAR2,
  7  	 or_offered_jobs     OUT     Generic_Cursor_Type,
  8  	 or_applicants_list  OUT     Generic_Cursor_Type
  9  );
 10  END PACK_SEMSADMIN_OFFEREDJOBS;
 11  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY PACK_SEMSADMIN_OFFEREDJOBS
  2  AS
  3  PROCEDURE Admin_Jobs_Offered_Rtr
  4  (
  5  	 ic_status	     IN      VARCHAR2,
  6  	 or_offered_jobs     OUT     Generic_Cursor_Type,
  7  	 or_applicants_list  OUT     Generic_Cursor_Type
  8  )
  9  AS
 10  	 vc_query    VARCHAR2(15000)	 :=  '';
 11  	 vc_query_1  VARCHAR2(15000)	 :=  '';
 12  
 13  	 TYPE type_appln_list IS RECORD
 14  	 (
 15  	    DEPTNO  NUMBER(2),
 16  	    DNAME   VARCHAR2(14),
 17  	    LOC     VARCHAR2(13)
 18  	 );
 19  	 vn_appln_list type_appln_list;
 20  
 21  BEGIN
 22  
 23  	 vc_query := vc_query || ' SELECT * FROM dept';
 24  
 25  	 OPEN or_offered_jobs FOR vc_query;
 26  
 27  	 LOOP
 28  	     FETCH or_offered_jobs INTO vn_appln_list;
 29  	     EXIT WHEN or_offered_jobs%NOTFOUND;
 30  
 31  	     vc_query_1 := '';
 32  
 33  	     vc_query_1 := vc_query_1 || ' INSERT INTO TEMP_JOB_DETAILS (deptno, dname, loc) ';
 34  	     vc_query_1 := vc_query_1 || ' SELECT * FROM dept';
 35  	     vc_query_1 := vc_query_1 || ' WHERE dept.dname = ''' || vn_appln_list.dname || ''' ';
 36  
 37  	     EXECUTE IMMEDIATE vc_query_1;
 38  
 39  	 END LOOP;
 40  
 41  	 OPEN or_applicants_list FOR
 42  	     SELECT * FROM TEMP_JOB_DETAILS;
 43  
 44  	 EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_JOB_DETAILS';
 45  	 commit;
 46  
 47    END Admin_Jobs_Offered_Rtr;
 48  END PACK_SEMSADMIN_OFFEREDJOBS;
 49  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref1 REFCURSOR
SCOTT@orcl_11g> VARIABLE g_ref2 REFCURSOR
SCOTT@orcl_11g> SET AUTOPRINT ON
SCOTT@orcl_11g> EXEC pack_semsadmin_offeredjobs.admin_jobs_offered_rtr (1, :g_ref1, :g_ref2)

PL/SQL procedure successfully completed.

ERROR:
ORA-08103: object no longer exists



no rows selected

ERROR:
ORA-01002: fetch out of sequence



no rows selected

SCOTT@orcl_11g> 

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE PACK_SEMSADMIN_OFFEREDJOBS
  2  AS
  3    TYPE Generic_Cursor_Type IS REF CURSOR;
  4  PROCEDURE Admin_Jobs_Offered_Rtr
  5  (
  6  	 ic_status	     IN      VARCHAR2,
  7  	 or_offered_jobs     OUT     Generic_Cursor_Type,
  8  	 or_applicants_list  OUT     Generic_Cursor_Type
  9  );
 10  END PACK_SEMSADMIN_OFFEREDJOBS;
 11  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY PACK_SEMSADMIN_OFFEREDJOBS
  2  AS
  3  PROCEDURE Admin_Jobs_Offered_Rtr
  4  (
  5  	 ic_status	     IN      VARCHAR2,
  6  	 or_offered_jobs     OUT     Generic_Cursor_Type,
  7  	 or_applicants_list  OUT     Generic_Cursor_Type
  8  )
  9  AS
 10  	 vc_query    VARCHAR2(15000)	 :=  '';
 11  	 vc_query_1  VARCHAR2(15000)	 :=  '';
 12  
 13  	 TYPE type_appln_list IS RECORD
 14  	 (
 15  	    DEPTNO  NUMBER(2),
 16  	    DNAME   VARCHAR2(14),
 17  	    LOC     VARCHAR2(13)
 18  	 );
 19  	 vn_appln_list type_appln_list;
 20  
 21  	 -- additional ref cursor:
 22  	 or_offered_jobs2 Generic_Cursor_Type;
 23  BEGIN
 24  
 25  	 vc_query := vc_query || ' SELECT * FROM dept';
 26  
 27  	 OPEN or_offered_jobs FOR vc_query;
 28  
 29  	 -- open additional ref cursor:
 30  	 OPEN or_offered_jobs2 FOR vc_query;
 31  
 32  	 LOOP
 33  	     -- fetch from the additional ref cursor:
 34  	     FETCH or_offered_jobs2 INTO vn_appln_list;
 35  	     EXIT WHEN or_offered_jobs2%NOTFOUND;
 36  
 37  	     vc_query_1 := '';
 38  
 39  	     vc_query_1 := vc_query_1 || ' INSERT INTO TEMP_JOB_DETAILS (deptno, dname, loc) ';
 40  	     vc_query_1 := vc_query_1 || ' SELECT * FROM dept';
 41  	     vc_query_1 := vc_query_1 || ' WHERE dept.dname = ''' || vn_appln_list.dname || ''' ';
 42  
 43  	     EXECUTE IMMEDIATE vc_query_1;
 44  
 45  	 END LOOP;
 46  
 47  
 48  	 OPEN or_applicants_list FOR
 49  	     SELECT * FROM temp_job_details;
 50  
 51  	 DELETE FROM temp_job_details;
 52  	 commit;
 53  
 54    END Admin_Jobs_Offered_Rtr;
 55  END PACK_SEMSADMIN_OFFEREDJOBS;
 56  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref1 REFCURSOR
SCOTT@orcl_11g> VARIABLE g_ref2 REFCURSOR
SCOTT@orcl_11g> SET AUTOPRINT ON
SCOTT@orcl_11g> EXEC pack_semsadmin_offeredjobs.admin_jobs_offered_rtr (1, :g_ref1, :g_ref2)

PL/SQL procedure successfully completed.


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g>