Here are some examles of dynamic sql:
CREATE OR REPLACE PROCEDURE example1 (p_str in varchar2,p_cantidad in out number) is
type my_curs_type is REF CURSOR;
curs my_curs_type;
v_cantidad number;
BEGIN
begin
OPEN curs FOR p_str;
-- for example p_str could be
-- select count(*) from mytable;
loop
FETCH curs INTO v_cantidad;
exit when curs%notfound;
p_cantidad := v_cantidad;
end loop;
CLOSE curs;
exception when others then close curs;
p_cantidad:= null;
end;
END;
CREATE OR REPLACE PROCEDURE example2 is
str varchar2(20000):='select ''ALERTA 1'', ''01/01/2008'' from dual';
type my_curs_type is REF CURSOR;
nov my_curs_type;
TYPE registro IS RECORD(
TIPO_ALERTA varchar2(100)
, FECHA varchar2(100)
);
n registro;
BEGIN
begin
OPEN nov FOR str;
loop
FETCH nov INTO n;
exit when nov%notfound;
dbms_output.put_line(n.tipo_alerta
||' '||n.fecha);
end loop;
close nov;
exception when others then
begin
close nov;
exception when others then null;
end;
end;
END;
CREATE OR REPLACE PROCEDURE dynamic_update (p_tabla in varchar2, p_campo1 in varchar2, p_campo2 in varchar2, p_rowcount in out number, p_error in out varchar2) IS
v_sentencia varchar2(20000):=null;
cid int;
sqlstat int;
BEGIN
p_rowcount := 0;
p_error := null;
v_sentencia := 'update '||p_tabla||' nn '||
'set nn.campo1 = :vcampo1 '||
'where nn.campo2 = :vcampo2';
begin
cid := dbms_sql.open_cursor;
dbms_sql.parse(cid, v_sentencia,
dbms_sql.native);
dbms_sql.bind_variable(cid, 'vcampo1',
p_campo1);
dbms_sql.bind_variable(cid, 'vcampo2',
p_campo2);
sqlstat := dbms_sql.execute(cid);
dbms_sql.close_cursor(cid);
p_rowcount := sql%rowcount;
exception when others then
p_error := sqlerrm;
dbms_sql.close_cursor(cid);
end;
END;
CREATE OR REPLACE procedure bullk_collect_example1 is
str varchar2(200);
type my_curs_type is REF CURSOR;
-- must be weakly typed --
curs my_curs_type;
-- Use a nested table to fetch into.
--This could equally be
-- a VARRAY or index by table.
type string_tab is table of varchar2(20);
ret_tab string_tab;
-- don't need to initialise, fetching
-- will do this automatically
v_table varchar2(30):='myTable';
begin
str := 'select campo1 from '||v_table||
' where rownum < 1000';
OPEN curs FOR str;
begin
FETCH curs BULK COLLECT INTO ret_tab;
for i in
1..curs%rowcount loop
dbms_output.put_line(ret_tab(i));
end loop;
close curs;
exception when others then close curs;
end;
END;
CREATE OR REPLACE procedure bullk_collect_example2 is
v_cantidad number:=0;
TYPE registro IS RECORD
( campo1 myTable.campo1%TYPE
, campo2 myTable. campo2 %TYPE
, campo3 myTable.campo3%TYPE
, campo4 myTable.campo4%TYPE
, campo5 myTable.campo5%TYPE
, campo6 myTable.campo6%TYPE
, campo7 myTable.campo7%TYPE);
TYPE type_cur_datos IS TABLE OF
registro INDEX BY PLS_INTEGER;
rows_cur_datos type_cur_datos;
CURSOR cur_datos IS
SELECT campo1, campo2, campo3, campo4,
campo5, campo6, campo7
FROM myTable
where rownum < 2000010;
limit_in number := 500000;
l_start PLS_INTEGER;
l_end PLS_INTEGER;
begin
begin
DBMS_SESSION.free_unused_user_memory;
show_pga_memory (limit_in || ' - BEFORE');
-- Finds out the current time
-- in 100th's of a second--
l_start := DBMS_UTILITY.get_time;
OPEN cur_datos;
LOOP
FETCH cur_datos BULK COLLECT INTO
rows_cur_datos LIMIT limit_in;
EXIT WHEN rows_cur_datos.count=0;
v_cantidad := rows_cur_datos.count;
forall i in 1..rows_cur_datos.count
-- rows_cur_datos(i).deno > -- insert /* +APPEND */ into myTable2
values
rows_cur_datos(i);
commit;
END LOOP;
CLOSE cur_datos;
l_end := DBMS_UTILITY.get_time;
DBMS_OUTPUT.put_line
( 'Elapsed CPU time for limit of '
|| limit_in
|| ' = '
|| TO_CHAR (round(((l_end - l_start)/100),2))
|| ' seconds '
);
show_pga_memory (limit_in || ' - AFTER');
commit;
exception when others then
begin
CLOSE cur_datos;
exception when others then null;
end;
dbms_output.put_Line
('error exception general: '||sqlerrm);
end;
END;
