EXEC_SQL

[yassinemannai]

hi,
I want to extract data from a n MS ACCESS table (test )

I write this code but the system generate error ORA-01403

///////////////////////

declare
type tab is TABLE of varchar2(256) index by binary_integer;
t tab;
i number;
j number;
connection_id EXEC_SQL.CONNTYPE;
cursor_number EXEC_SQL.CURSTYPE;

sql_str VARCHAR2(256);

nIgnore PLS_INTEGER;
n number;
nErrPos PLS_INTEGER ;
errmesg VARCHAR2(256);
begin
connection_id := EXEC_SQL.OPEN_CONNECTION('system/manager@odbc:testgct');
cursor_number := EXEC_SQL.OPEN_CURSOR(connection_id);
sql_str:='select * from test';
EXEC_SQL.PARSE(connection_id, cursor_number, sql_str, exec_sql.V7);
for i in 1..3 loop
i:=1;
EXEC_SQL.Define_Column(connection_id, cursor_number,i,t(i),256);

end loop;
nIgnore :=EXEC_SQL.EXECUTE(connection_id, cursor_number);

loop


n:=EXEC_SQL.FETCH_ROWS(connection_id, cursor_number);
exit when n=0;
for j in 1..3 loop

EXEC_SQL.COLUMN_VALUE(connection_id, cursor_number,j,t(j));
end loop;
message(t(1))|| t(2) || t(3) );


end loop;
EXEC_SQL.CLOSE_CURSOR(connection_id, cursor_number);
EXEC_SQL.CLOSE_CONNECTION(connection_id);

exception
when EXEC_SQL.PACKAGE_ERROR then
message('Erreur de connexion à la base de données');
nErrPos := EXEC_SQL.LAST_ERROR_POSITION(connection_id);
message(' position dans le texte où se trouve lerreur '|| nErrPos);
errmesg := EXEC_SQL.LAST_ERROR_MESG(connection_id);
message(' message derreur ' || errmesg);

end;
/////////////////////

please help me

 

[stevecal]

error 1403 indicates that all records have been fetched, or no data found. Looks to me like your exec_sql.parse statement may be misplaced: not only does it parse your statement but also it executes it... so your call subsequent to EXECUTE is redundant.