Re: ORA-01002 :Fetch out of sequence

As this is an ordinary select, there should be no begin end block wrapping it.
Also, the into shouldn't be there.
You need to issue a dbms_sql.define_column before the fetch and retrieve the data with a call to dbms_sql.column_value. Don't ask me the difference between column_value and variable_value, and I have been using bind_variable only in where clauses. I know this setup is working.

Hth,

--
Sybrand Bakker, Oracle DBA
M.Huybrechts <mhuybrechts_at_interact-automation.nl> wrote in message news:81dqp2$boaj$1_at_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 - 13:10:08 CST