EXEC_SQL.DEFINE_COLUMN and PL/SQL TABLE

[yassinemannai]

declare

connection_id EXEC_SQL.CONNTYPE;
cursor_number EXEC_SQL.CURSTYPE;

sql_str VARCHAR2(256);

nIgnore PLS_INTEGER;
n number;
ligne types.tab; -- table PL/SQL pour stocker le contenu d'une ligne
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);

EXEC_SQL.Define_Column(connection_id, cursor_number,1,ligne(1),50);
EXEC_SQL.Define_Column(connection_id, cursor_number,2,ligne(2),50);
EXEC_SQL.Define_Column(connection_id, cursor_number,3,ligne(3),50);

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

loop
n:=EXEC_SQL.FETCH_ROWS(connection_id, cursor_number);
exit when n=0;
EXEC_SQL.COLUMN_VALUE(connection_id, cursor_number,1,ligne(1));
EXEC_SQL.COLUMN_VALUE(connection_id, cursor_number,2,ligne(2));
EXEC_SQL.COLUMN_VALUE(connection_id, cursor_number,3,ligne(3));

insert into t values(ligne(1),ligne(2),ligne(3));
end loop;
EXEC_SQL.CLOSE_CURSOR(connection_id, cursor_number);
EXEC_SQL.CLOSE_CONNECTION(connection_id);


end;