Example: Select statement
declare
cur number;
obj_nam varchar2(128);
last_ddl date;
obj_id number;
dummy integer;
begin
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, q'{
select
object_name,
last_ddl_time,
object_id
from
all_objects
where
object_name like :nam and
object_type = :typ
}',
dbms_sql.native
);
dbms_sql.bind_variable(cur, ':nam', '%TQ84%');
dbms_sql.bind_variable(cur, ':typ', 'TABLE' );
dbms_sql.define_column(cur, 1, obj_nam , 128);
dbms_sql.define_column(cur, 2, last_ddl );
dbms_sql.define_column(cur, 3, obj_id );
--
-- The return value of dbms_sql.execute is undefined for
-- select statements. Therefor, we assign it to a variable
-- named dummy:
--
dummy := dbms_sql.execute(cur);
while dbms_sql.fetch_rows(cur) > 0 loop
dbms_sql.column_value(cur, 1, obj_nam );
dbms_sql.column_value(cur, 2, last_ddl);
dbms_sql.column_value(cur, 3, obj_id );
dbms_output.put_line(
rpad(obj_nam, 50) ||
to_char(last_ddl, 'yyyy-mm-dd') || ' ' ||
to_char(obj_id , '9999999999')
);
end loop;
dbms_sql.close_cursor (cur);
end;
/
TODO
parse_gt_32k.plsql
--
-- http://stackoverflow.com/a/20554246/180275
--
create table tq84_gt32_k_dest (
col_1 number,
col_2 varchar2(10),
col_3 date
);
create table tq84_gt32_k_src (
col_1 number,
col_2 varchar2(10),
col_3 date
);
insert into tq84_gt32_k_src values (1, 'one' , sysdate );
insert into tq84_gt32_k_src values (2, 'two' , sysdate+1);
insert into tq84_gt32_k_src values (3, 'three', sysdate-1);
declare
stmt dbms_sql.varchar2a;
c number;
res number;
begin
stmt( 1) := 'insert into tq84_gt32_k_dest (';
stmt( 2) := ' col_1,';
stmt( 3) := ' col_2,';
stmt( 4) := ' col_3)';
stmt( 5) := 'select ';
stmt( 6) := ' col_1,';
stmt( 7) := ' col_2,';
stmt( 8) := ' col_3 ';
stmt( 9) := 'from tq84_gt32_k_src ';
stmt(10) := 'where';
stmt(11) := ' col_1 != 1';
-- each element can have up to 32K characters, number of elements is (almost) unlimited
c := dbms_sql.open_cursor;
dbms_sql.parse(c, stmt, 1, 11, true, dbms_sql.native);
res := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
end;
/
select * from tq84_gt32_k_dest;
drop table tq84_gt32_k_dest;
drop table tq84_gt32_k_src;
plsql_block_with_out_parameters.plsql
declare
block varchar2(32000) := 'begin :out_1 := :in_1 * :in_2; :out_2 := :in_1 + :in_3; end;';
v_dummy number;
c number;
out_1 number;
out_2 number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, block, dbms_sql.native);
dbms_sql.bind_variable(c, 'in_1', 9);
dbms_sql.bind_variable(c, 'in_2', 4);
dbms_sql.bind_variable(c, 'in_3', 7);
dbms_sql.bind_variable(c, 'out_1', cast (null as number));
dbms_sql.bind_variable(c, 'out_2', cast (null as number));
v_dummy := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'out_1', out_1);
dbms_sql.variable_value(c, 'out_2', out_2);
dbms_sql.close_cursor(c);
dbms_output.new_line;
dbms_output.put_line('out_1: ' || out_1);
dbms_output.put_line('out_2: ' || out_2);
dbms_output.new_line;
end;
/
return_result.sql
create table tq84_tab (
a number,
b varchar2(10)
);
insert into tq84_tab values (1, 'foo');
insert into tq84_tab values (1, 'bar');
insert into tq84_tab values (1, 'baz');
insert into tq84_tab values (2, 'abc' );
insert into tq84_tab values (2, 'def ghi');
insert into tq84_tab values (2, 'jkl mno');
insert into tq84_tab values (2, 'pqrst' );
create or replace procedure tq84_result(i number) as
cur sys_refcursor;
begin
open cur for select * from tq84_tab where a = i;
-- After returning the result with return_result, it
-- can be retrieved with dbms_sql.get_next_result()
-- In SQL*Plus (12) and sqlcl, this seems to be automatically
-- done.
dbms_sql.return_result(cur);
end tq84_result;
/
exec tq84_result(1)
exec tq84_result(2)
drop procedure tq84_result;
drop table tq84_tab purge;
select_from_table.sql
create table tq84_select_from_table (
a number,
b varchar2(42),
c date
);
insert into tq84_select_from_table values ( 42, 'fourty-two', sysdate );
insert into tq84_select_from_table values ( 5, 'minus 5' ,'2005-05-05');
insert into tq84_select_from_table values ( null, '#' , null );
insert into tq84_select_from_table values ( 10, null , sysdate + 1);
declare
c number;
a_ number;
b_ varchar2(42);
c_ date;
dummy number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'select a,b,c from tq84_select_from_table where a >= :a', dbms_sql.native);
dbms_sql.bind_variable(c, ':a', 7);
dbms_sql.define_column(c, 1, a_ );
dbms_sql.define_column(c, 2, b_, 42);
dbms_sql.define_column(c, 3, c_ );
dummy := dbms_sql.execute(c);
while dbms_sql.fetch_rows(c) > 0 loop
dbms_sql.column_value(c, 1, a_);
dbms_sql.column_value(c, 2, b_);
dbms_sql.column_value(c, 3, c_);
dbms_output.new_line;
dbms_output.put_line('a: ' || a_);
dbms_output.put_line('b: ' || b_);
dbms_output.put_line('c: ' || c_);
end loop;
dbms_sql.close_cursor(c);
dbms_output.new_line;
end;
/
drop table tq84_select_from_table purge;