quarta-feira, dezembro 29, 2004

Oracle (PL SQL) – Cursor dinâmico no Oracle

Abaixo está o código de como criar um cursor dinâmico no Oracle:

DECLARE
source_cursor integer;
fNumber_Buffer number(10);
fnumber_buffer2 NUMBER(10);
fnumber_buffer3 VARCHAR2(100);
ssql VARCHAR2(2000);
wrc INTEGER;
BEGIN
ssql := 'SELECT cod_banco, cod_agbco FROM amdsepdbs0100.agencia';
source_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.parse(source_cursor, ssql, DBMS_SQL.v7);
dbms_sql.define_column(source_cursor,1,fNumber_Buffer);
dbms_sql.define_column(source_cursor,2,fnumber_buffer2);
wrc := DBMS_SQL. execute(source_cursor);
LOOP
IF DBMS_SQL.fetch_rows(source_cursor) > 0
THEN
dbms_sql.column_value(source_cursor,1,fNumber_Buffer);
DBMS_SQL.column_value(source_cursor, 2, fnumber_buffer2);
insert into AMDSEPDBS0100.AGENCIA_TMP
values (fNumber_Buffer, fNumber_Buffer2);
ELSE
EXIT;
END IF;
END LOOP;
commit;
END;

Nenhum comentário: