Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ora-01002 fetch out of sequence

 
sridhar somisetty
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi friends,
I have stuck up with a problem. The problem is that i am getting fetch out of sequence(ORA-01002) sql exception.
This error is occurring when i am trying to get the resultset object from the ref cursor. By using the following code

stmt = conn.prepareCall(procedure(?,?));
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.setString(1,ttid);
stmt.execute();
rs=(ResultSet) stmt.getObject(2);

Actually in the procedure i am just opening the cursor and using select statement. I am not using any update statements or

commit statements.

May i know how to escape from this.
thanks in advance.

Regards
Sridhar Somisetty
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It unlikely to be caused by teh JDBC code you show, this is more probably caused by your procedure. If you are using a cursor, make sure it is valid when you use it.
 
sridhar somisetty
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
may i know how to check if it is valid or not.If it is not valid then what is procedure i have to follow to follow in order to escape from the fetch out of sequence.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does your procedure run when called directly from SQLPlus? You get this error if you try to fetch from a cursor when there is nothing more to fetch.
 
sridhar somisetty
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes the procedure can be run directly from SQLPLUS.may i know how to catch this fetch out of sequence exception.generally this exception is comming in rare cases.So what are all the precations i need to take to prevent this exception in future.
thanks in advance
[ November 07, 2006: Message edited by: sridhar somisetty ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could you post your procedure?
 
sridhar somisetty
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi this is my procedure where in i have removed the select statement
PROCEDURE get_ticket_detail(p_problem_id IN VARCHAR2,p_return_cur OUT t_ref_cursor)
IS
v_main_sql varchar2(20000);
BEGIN

v_main_sql := 'SELECT statement
open p_return_cur for v_main_sql;

Exception
When others THEN
null;
END get_ticket_detail;
 
sridhar somisetty
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi friends
Actually this 'fetch out of sequence' is occuring when a fetch is attempted against a cursor that is no longer valid. As our code is ignoring any and all exceptions. And our calling code still attempts to fetch from the cursor.

Previous code for handling exceptionException
When others THEN
null;
END

So if we rewrite the code for handling the exception like the below means will it solve the problem or not.
EXCEPTION
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
INSERT INTO tt_audit_table (procedure_name, load_date, error_number, error_message)
VALUES ('get_dispatch_tasks', sysdate, err_code, err_msg);
END;

If you think this is not the right approach means can you please suggest the right approach.So that i can escape this exception in future.By handling it in the pl/sql package level.
thanks in advance.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic