Subject Cursor FETCH in PSQL and ROW_COUNT/'fetch past last record' error
Author Martijn Tonies (Upscene Productions)
Post date 2017-03-27T08:23:36Z
Hi all,
 
I’m unsure if this is an error or the intended behaviour.
 
Got a simply table “dummy_rows_10”, single column, integer values 1..10
 
Next, this procedure:
 
SET TERM ^^ ;
CREATE OR ALTER PROCEDURE DEBUG_CURSOR_TESTS returns (
  P_OUT1 TYPE OF COLUMN DUMMY_ROWS_10.V,
  RC Integer)
AS
declare c  cursor for (select v from dummy_rows_10 order by 1);
begin
  for select v from dummy_rows_10 order by 1
  into p_out1
  do suspend;
  open c;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
  fetch c into :p_out1;
  rc = row_count;
  suspend;
 
  /*fetch prior from c into :p_out1;
  suspend; 
  /*suspend;
  fetch prior from c into :p_out1;
  suspend;
  fetch last from c into :p_out1;
  suspend;
  fetch prior from c into :p_out1;
  suspend; 
  fetch prior from c into :p_out1; 
  /*fetch relative p_out1 - 1 from c into :p_out1;
  suspend; */ 
end ^^
SET TERM ; ^^
 
 
The FOR SELECT loop returns 1..10 as the values.
 
The fetch/suspend lines return 1..10, with a row count of 1, and another ‘10’ with a row count of 0.
 
If I add another FETCH, I get this error while executing the routine:
>>attempt to fetch past the last record in a record stream At
procedure 'DEBUG_CURSOR_TESTS' line: 42, col: 3
 
And here’s my question: the last fetch has a row_count of 0, another fetch returns an error. However, if I make this cursor a scrollable cursor, and my first ‘fetch’ is a ‘fetch prior’, I immediately get this error:
>>attempt to fetch before the first record in a record stream At
procedure 'DEBUG_CURSOR_TESTS' line: 9, col: 3
 
I get the ‘past last record’ error when I use:
fetch absolute 11 from c into :p_out1;
 
 
I wonder, should the first fetch next that returns no data, return row_count=0, or also the ‘fetch past record stream’ error?
 
With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.