DYNAMIC SQLThey can be entered interactive or read from a file. The native dynamic SQL is introduced in 8i made it much easier to execute in 10g. In 11g Oracle uses Dynamic SQL implementation. Advantages of Dynamic SQL : Text of the SQL statement (commands, clauses, etc). The Number of host variablesThe data types of host variables References to database objects such as columns, tables, indexes, views, sequences and username.
declare
user_sdbt varchar2(100);
pri_sdbt varchar2(100);
begin
user_sdbt :='create user &name identified by &passwd';
execute immediate user_sdbt;
pri_sdbt :='grant connect to &name';
execute immediate pri_sdbt;
end;
SQL> /
Enter value for name: raj
Enter value for passwd: raj
old 5: user_sdbt :='create user &name identified by &passwd';
new 5: user_sdbt :='create user raj identified by raj';
Enter value for name: raj
old 7: pri_sdbt :='grant connect to &name';
new 7: pri_sdbt :='grant connect to raj';
PL/SQL procedure successfully completed.
Table creation in dynamic SQL
create table t1(id int,name varchar2(10));
Table created.
SQL> insert into t1 values(&id,'&name');
Enter value for id: 11
Enter value for name: raji
old 1: insert into t1 values(&id,'&name')
new 1: insert into t1 values(11,'raji')
1 row created.
SQL> /
Enter value for id: 12
Enter value for name: vino
old 1: insert into t1 values(&id,'&name')
new 1: insert into t1 values(12,'vino')
1 row created.
SQL> /
Enter value for id: 13
Enter value for name: priya
old 1: insert into t1 values(&id,'&name')
new 1: insert into t1 values(13,'priya')
1 row created.
declare
v_stmt varchar2(100);
begin
execute immediate 'drop table t1 purge';
v_stmt :='create table t2(id number)';
execute immediate v_stmt;
dbms_output.put_line(v_stmt);
end;
SQL> /
PL/SQL procedure successfully completed.
SQL> desc t2
Name nulltype
ID NUMBER
« Previous (PL/SQL - Using DDLS in PLSQL) |