execute immediate

1.
sql_stmt := ‘insert into t’||tbcounter||’ values (:1, :2)’;
execute immediate sql_stmt using s, s;

2.
sqlstring := q'[insert into x values( ‘ || i || ‘)]’;

execute immediate sqlstring;

3.
begin
execute immediate ‘create table abcd (efgh number)’;
execute immediate ‘drop table abcd’;
execute immediate ‘create table abcd (efgh varchar2(10))’;
end;

4.
begin
execute immediate ‘insert into abcd (efgh) values (:text_string)’
using ‘ijkl’;
execute immediate ‘insert into abcd (efgh) values (:text_string)’
using ‘mnop’;
execute immediate ‘update abcd ‘ ||
‘set efgh = :text_string where efgh = :second_string’
using ‘qrst’, ‘mnop’;
execute immediate ‘delete from abcd ‘ ||
‘where efgh = :text_string ‘
using ‘qrst’;
end;

5.
as useful as ddl and dml are, a database is not very useful if you can’t get your data out. you can also use execute immediate to select your data back out.

declare
v_data abcd.efgh%type;

v_data_row abcd%rowtype;

begin

execute immediate ‘select efgh from abcd where efgh = :text_string’
into v_data
using ‘ijkl’;

dbms_output.put_line( ‘column variable: ‘ || v_data );

execute immediate ‘select * from abcd where efgh = :text_string’
into v_data_row
using ‘ijkl’;

dbms_output.put_line( ‘row variable: ‘ || v_data_row.efgh );

end;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s