ORA-01002 :Fetch out of sequence

From: M.Huybrechts <mhuybrechts_at_interact-automation.nl>
Date: Tue, 23 Nov 1999 11:37:08 +0100
Message-ID: <81dqp2$boaj$1@reader3.wxs.nl>

Hello everyone,

Please have a look at the following function:

 FUNCTION RETURNNUMBERVARSQL(Sqlstatement varchar2) return number IS   dummy integer;
  sqlCursor integer;
  strSql varchar2(32767);
  retval number;
 BEGIN

     strSql:= 'BEGIN ' || upper(sqlstatement) || ' END;';
     strSql:= REPLACE(strSql,'FROM','INTO :bnd1 FROM');
  sqlCursor:=dbms_sql.open_cursor;
  dbms_output.put_line(strsql);

  dbms_sql.parse(sqlCursor,strSql,dbms_sql.native);   dbms_sql.bind_variable(sqlCursor,'bnd1',retval);   dummy:=dbms_sql.execute(sqlCursor);
  if (dbms_sql.fetch_rows(sqlCursor) <= 0) then    retval:=null;
  else
   dbms_sql.variable_value(sqlCursor,'bnd1',retval);   end if;
  dbms_sql.close_cursor(sqlCursor);
  return retval;
 END; The perpose of this function is be able to return a number value from a select statement, ie 'SELECT number_column_name FROM table_name WHERE other_column_name=value;'
When I trie to execute it I get error 1002:Fetch out of sequence. The position of this error is in the line: dummy:=dbms_sql.execute(sqlCursor); What am I doing wrong here? Maybe there is another way, if so please tell me... The select statement is ok, so that isn't the problem, but what is?

Thanks in advise,

Micha Huybrechts
Software Engineer Received on Tue Nov 23 1999 - 04:37:08 CST