Home » SQL & PL/SQL » SQL & PL/SQL » java.sql.SQLException: ORA-01002: fetch out of sequence (XP, 9i)
java.sql.SQLException: ORA-01002: fetch out of sequence [message #306010] Wed, 12 March 2008 09:58 Go to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Hi,

This is a very simple proc. I am calling this from Java and retrieving the results.

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;



The proc compiles fine. But when I call it from Java it gives me the following error Sad

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)


I have set Connection.setAutoCommit(false);
Plzzz Help !

Thanks !
Re: java.sql.SQLException: ORA-01002: fetch out of sequence [message #306017 is a reply to message #306010] Wed, 12 March 2008 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
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.
Re: java.sql.SQLException: ORA-01002: fetch out of sequence [message #306020 is a reply to message #306017] Wed, 12 March 2008 10:11 Go to previous messageGo to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Hi,

I saw this list..
But I think I am taking care of all the actions they have mentioned. Thts wat puzzles me !
Re: java.sql.SQLException: ORA-01002: fetch out of sequence [message #306033 is a reply to message #306020] Wed, 12 March 2008 11:04 Go to previous messageGo to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Hi,

I have realized that the error is because of the fetch statement.
If i comment the FETCH part and try to return 2 cursors to my java code things work perfectly fine.

But when i uncomment the FETCH, then it shows the error I mentioned. The 2nd query is executed, the insert happens but then cursors are not returned to java and throws the error.

I think something needs to be corrected in the FETCH LOOP part but I dont know wat that is !

PLzzzzzzzzzzzzzzzzzzzzz Help !

Thanks
Re: java.sql.SQLException: ORA-01002: fetch out of sequence [message #306081 is a reply to message #306033] Wed, 12 March 2008 14:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Instead of looping through a ref cursor that is used as an out parameter, open 2 separate cursors, one for usage as an out parameter and one to loop through. Also, instead of using execute immediate with truncate on a table that you have opened a ref cursor for, just use delete. Please see the simplified demonstration below, using a simple select from the dept table, in which I have first recreated the problem, then corrected it.

-- reproduction of error:
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> 


-- correction:
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> 

Re: java.sql.SQLException: ORA-01002: fetch out of sequence [message #306102 is a reply to message #306081] Wed, 12 March 2008 16:27 Go to previous messageGo to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Hi Barbara,

Thanks a lot !
It did work ! But I wanted to know why the first out ref cursor cannot be used?
Is it because its an OUT param?

Also, why does it give the error msg "fetch out of sequence".

Many thanks for helping me with this.
Re: java.sql.SQLException: ORA-01002: fetch out of sequence [message #306110 is a reply to message #306102] Wed, 12 March 2008 18:57 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
It can't be used because you have already walked through it to insert into temp_job_details. The pointer is at the end of result set.

On a separate note, you do realize that you are making this much much more difficult than it needs to be. Unless you have greatly reduced what you are actually returning, you don't need to insert into the temp table and you don't need so many joins to the jobs table.
Previous Topic: varchar2(1), char(1)
Next Topic: CASE
Goto Forum:
  


Current Time: Fri Dec 09 19:28:38 CST 2016

Total time taken to generate the page: 0.26594 seconds