Dynamic SQL statements are
not embedded in your source program and checked at compile time.
Instead, they are
stored in character strings that are input to, or built by, the program at runtime.
(i.e.
dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.)
Programming methodology for generating and running SQL statements at run time. Good for:
- Writing general-purpose and flexible programs like ad hoc query systems
- Writing programs that must run DDL statements
- When number and datatypes of IN/OUT variables are unknown at compilation time
- When the full text of a SQL stmt needs to be obtained at run-time only.
- i.e. a SELECT stmt that includes an identifier (a table name) that is unknown at compile time.
- i.e. a WHERE clause in which the number of subclauses is unknown at compile time.
How to write dynamic SQL ? (two ways)
- (1) Native dynamic SQL: PL/SQL language feature for building and running dynamic SQL statements
- (2)
DBMS_SQL package: , an API for building, running, and describing dynamic SQL statements
Native dynamic SQL x DBMS_SQL package:
- native dynamic SQL: Faster, and more readable (even more when optimized by compiler)
- Native dynamic SQL: requires complete specification of IN/OUT vars (number and datatype) at compile time.
- Switch between native dynamic SQL and DBMS_SQL package using
DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER.
Using EXECUTE IMMEDIATE (Advantages over DBMS_SQL)
- DBMS_SQL: Oracle 8 and earlier
- EXECUTE IMMEDIATE: Oracle 8i and later
- Native dynamic SQL:
- Integral part of the SQL language that is built into Pl/SQL, thus making it simpler to use and faster than the DBMS_SQL package.
- Native dynamic SQL also supports the usage of object types and collections not supported by the DBMS_SQL package
- Faster than DBMS_SQL because it parses, binds, and executes the statements immediately.
- DBMS_SQL requires calls to procedures and functions.
Question: Can I use TCL or DDL within a PL/SQL Block?
A: No. You HAVE to use DYNAMIC SQL for that. (Native Dyn SQL or DBMS_SQL).
Static SQL is limited to DML (SELECT, INSERT, UPDATE, DELETE, MERGE), TCL (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION) and LOCK TABLE.
|
| Native Dynamic SQL: EXECUTE IMMEDIATE |
EXECUTE IMMEDIATE: Builds and runs a dynamic SQL statement in a single operation.- If dynamic_sql_stmt has a
RETURNING INTO clause, using_clause can contain only IN bind arguments. The bind arguments in the RETURNING INTO clause are OUT bind arguments by definition.
- When using dynamic SQL, beware of SQL injection, a security risk.
- The value of bind_argument cannot be TRUE, FALSE, or NULL
| Type of dynamic SQL statement | USE of EXECUTE IMMEDIATE |
| SELECT returns at most one row | OUT-bind arguments (defines): in the INTO clause.
IN-bind arguments: in the USING clause.
EXECUTE IMMEDIATE
SELECT ... INTO <OUT-vars>
USING <bind_arg>; |
| SELECT returns multiple rows | OUT-bind arguments: in the BULK COLLECT INTO clause. IN-bind arguments: in the USING clause.
EXECUTE IMMEDIATE
SELECT ... BULK COLLECT INTO <OUT-vars>
USING <bind_arg>; |
DML with RETURNING INTO clause | IN-bind arguments: in the USING clause.
OUT-bind arguments: in the RETURNING INTO clause. |
DMLw/o RETURNING INTO clause | all bind arguments in the USING clause |
Using dynamic SQL with EXECUTE IMMEDIATE:
-- create procedure to insert new record on departments table
create or replace procedure new_dept
(deptid in out number,
dname in varchar2,
mgrid in varchar2,
locid in varchar2) as
begin
deptid:= departments_seq.nextval;
insert into departments (
department_id,
department_name,
manager_id,
location_id)
values (deptid, dname, mgrid, locid);
end;
-- Use dynamic SQL to call the procedure new_dept
set serveroutput on
declare
plsql_block varchar2(500);
new_deptid number(4);
new_dname varchar2(30) := 'Advertising';
new_mgrid number(6) := 200;
new_locid number(4) := 1700;
numdep number;
begin
select count(*) into numdep from departments;
dbms_output.put_line('# depts: ' || numdep);
-- enter the dynamic SQL statement
-- note that the statement is complete. Only dynamic binding of the
-- procedure parameters occur.
-- note that the SQL stmt has placeholders for bind arguments.
plsql_block := 'begin new_dept(:a, :b, :c, :d); end;';
-- use EXECUTE IMMEDIATE to run the SQL stmt and specify the
-- binding variables.
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
select count(*) into numdep
from departments;
dbms_output.put_line('# depts: ' || numdep);
end;
anonymous block completed
# depts: 27
# depts: 28
| Using SELECT, DDL and DML with Execute Immediate
Using Conditional Compilation
|
What the example below does (3 procedures):
(a) Check whether a table exists in the database (SELECT)
(b) DROP and Recreate or CREATE table test and GRANT access on the table to another user (DDL)
(c) Insert data on the created table (DML)
(1)
Procedure check_object:
(SELECT returns one row only): Check whether a table named TESTE already exists in the database- procedure call: check_object('TESTE', 'TABLE');
CREATE OR REPLACE Procedure check_object (p_objname in varchar2, p_objtype in varchar2,
p_created OUT boolean) IS
query varchar2(200);
vcount varchar2(40);
BEGIN
-- using SELECT with EXECUTE IMMEDIATE
query := 'select count(*) from user_objects '||
'where object_type = :b '||
'and object_name = :c ';
-- If the dynamic SQL statement is a SELECT statement that can return
-- at most one row, put out-bind arguments (defines) in the INTO clause
-- and in-bind arguments in the USING clause.
EXECUTE IMMEDIATE query into vcount USING IN p_objtype, p_objname;
IF vcount = 0 THEN
$IF $$my_debug $THEN
dbms_output.put_line(p_objtype || ' ' || p_objname || ' does not yet exist on the database.');
$END
p_created := FALSE;
ELSE
$IF $$my_debug $THEN
dbms_output.put_line(p_objtype || ' ' || p_objname || ' already exist on the database.');
$END
p_created := TRUE;
END IF;
EXCEPTION
When others then
dbms_output.put_line('Error Procedure check_object');
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace());
END;
alter session set plsql_ccflags = 'my_debug:TRUE';
set serveroutput on
declare
v_created boolean;
begin
check_object('TESTE', 'TABLE',v_created);
end;
/
Anonymous block completed
TABLE TESTE already exist on the database.
(2)
Procedure create_table:
(DDL): If table exists, drop table and recreate it.(DDL): Grant SELECT on table TESTE to userA.- procedure call: cr_tbl('TRUE', 'create table TESTE (name varchar2(20), salary number)';');
- For the procedure to succeed, the procedure owner MUST have been granted the CREATE TABLE privilege directly, not via a role.
- Role privileges are not applied in procedures
CREATE OR REPLACE procedure create_table (p_created in boolean, p_stmt in varchar2) IS
e_leave_drop exception;
e_leave_create exception;
stmt varchar2(200);
v_created boolean;
BEGIN
-- USING DDL with EXECUTE IMMEDIATE
IF p_created then
-- table exist. Drop before creating.
stmt := 'drop table teste';
begin
execute immediate stmt;
exception
when others then
dbms_output.put_line('Error dropping table');
dbms_output.put_line(sqlerrm);
raise e_leave_drop;
end;
dbms_output.put_line('Table dropped.');
END IF;
stmt := p_stmt;
begin
execute immediate stmt;
exception
when others then
dbms_output.put_line('Error creating table');
dbms_output.put_line(sqlerrm);
raise e_leave_create;
end;
dbms_output.put_line('Table teste created.');
v_created := TRUE;
dbms_output.put_line('Calling check_object..');
check_object('TESTE', 'TABLE', v_created);
stmt := 'grant select on teste to userb';
execute immediate stmt;
dbms_output.put_line('grant select completed');
EXCEPTION
when e_leave_drop then
dbms_output.put_line('leaving procedure...');
when e_leave_create then
dbms_output.put_line('leaving procedure...');
END;
alter procedure check_object compile;
alter session set PLSQL_CCFLAGS = 'my_debug:TRUE';
set serveroutput on
declare
stmt varchar2(200);
vcreated boolean default TRUE;
begin
stmt := 'create table UserA.teste (name varchar2(20), salary number)';
create_table(vcreated, stmt);
end;
/
Anonymous block completed
Table dropped.
Table teste created.
Calling check_object..
TABLE TESTE already exist on the database.
grant select completed
(3)
Procedure insert_tbl:
(DML): Insert values on table TESTE- If the dynamic SQL statement is a DML statement WITHOUT a RETURNING INTO clause, other than SELECT, put all bind arguments in the USING clause.
CREATE OR REPLACE procedure insert_tbl (p_name in varchar2, n in integer,
p_salinit number) as
vsal number;
stmt varchar2(200);
vname varchar2(20);
BEGIN
stmt := 'INSERT into TESTE values (:b, :c)';
vsal := p_salinit;
vname := p_name;
for i in 1..n loop
vname := vname || to_char(i);
vsal := 10000 + i;
execute immediate stmt USING vname, vsal;
dbms_output.put_line('Inserted row '|| i);
end loop;
EXCEPTION
when others then
dbms_output.put_line('Insert error...');
dbms_output.put_line(sqlerrm);
END;
set serveroutput on
begin
insert_tbl('Mary', 4, 5000);
end;
/
anonymous block completed
Inserted row 1
Inserted row 2
Inserted row 3
Inserted row 4
SQL> Select * from teste;
NAME SALARY
-------------------- ----------------------
Mary1 10001
Mary12 10002
Mary123 10003
Mary1234 10004
What the example below does:
(a) Check the columns of a given table in the data dictionary (Mulitple-row SELECT: BULK COLLECT INTO)
(b) Delete a row from the table. Returns data from the deleted row using RETURNIG clause (DML)
(1)
Procedure list_tbl
(SELECT): Get column name list with BULK COLLECT INTO.- If the dynamic SQL statement is a SELECT statement that can return MULTIPLE rows,
put out-bind arguments (defines) in the BULK COLLECT INTO clause and in-bind arguments in the USING clause.
- You CANNOT use bind values to pass in the names of schema objects, such as table names or column names.
- SO, names of schema objects have to be added to the dynamic sql statement, instead of passed as bind vars.
CREATE OR REPLACE procedure list_tbl (p_tblname in varchar2) as
type t_recdata is RECORD (nm varchar2(20), salary number);
type t_aadata is TABLE of t_recdata index by pls_integer;
vdata t_aadata;
type t_colnm is table of varchar2(20) index by pls_integer;
vcolnm t_colnm;
vb varchar2(30);
stmt varchar2(200);
BEGIN
stmt := 'SELECT column_name FROM user_tab_columns '||
'WHERE table_name = :b';
vb := p_tblname;
EXECUTE IMMEDIATE stmt BULK COLLECT INTO vcolnm USING p_tblname;
for i IN vcolnm.first..vcolnm.last loop
if i = 1 then
vb := vcolnm(i);
else
vb := vb || ', '|| vcolnm(i);
end if;
end loop;
dbms_output.put_line('columns are: '|| vb);
-- You CANNOT use bind values to pass in the names of schema objects,
-- such as table names or column names.
-- SO, names of schema objects have to be added to the dynamic sql
-- statement, instead of passed as bind vars.
stmt := 'select '|| vb || ' from teste';
BEGIN
EXECUTE IMMEDIATE stmt BULK COLLECT INTO vdata;
EXCEPTION
when others then
dbms_output.put_line('Execute immediate: error SELECT');
dbms_output.put_line(sqlerrm);
END;
for i IN vcolnm.first..vcolnm.last loop
dbms_output.put(vcolnm(i) || ' -- ');
end loop;
dbms_output.new_line();
for i in vdata.first..vdata.last loop
dbms_output.put_line(vdata(i).nm || ' '|| vdata(i).salary);
end loop;
END list_tbl;
set serveroutput on
begin
list_tbl('TESTE');
end;
/
columns are: NAME, SALARY
NAME -- SALARY --
Ximenes12 10002
Ximenes123 10003
(2)
Procedure delete_table:
- procedure call: delete_table('Mary');
- If the dynamic SQL statement is a DML statement with a RETURNING INTO clause,
put in-bind arguments in the USING clause and out-bind arguments in the RETURNING INTO clause.
set serveroutput on
CREATE OR REPLACE procedure del_tbl(p_name in varchar2) as
type t_recdata is RECORD (nm varchar2(20), salary number);
vrec t_recdata;
stmt varchar2(200);
BEGIN
stmt := 'DELETE FROM teste '||
'WHERE name = :b ' ||
'RETURNING name, salary INTO :c, :d';
execute immediate stmt USING IN p_name returning into vrec;
dbms_output.put_line('Deleted rec: ' || vrec.nm || ' ' || vrec.salary);
dbms_output.new_line;
dbms_output.put_line('Listing table...');
list_tbl('TESTE');
EXCEPTION
when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace());
END;
set serveroutput on
begin
del_tbl('Mary123');
end;
/
anonymous block completed
Deleted rec: Mary123 10003
Listing table...
columns are: NAME, SALARY
NAME -- SALARY --
Mary1 10001
Mary12 10002
Mary1234 10004
Running all together:
set serveroutput on
declare
vcreated boolean default TRUE;
begin
check_object('TESTE', 'TABLE', vcreated);
create_table(vcreated, 'create table teste (name varchar2(20), salary number)');
insert_table('Ximenes', 3, 9700);
list_tbl('TESTE');
delete_table('Ximenes1');
end;
nonymous block completed
TABLE TESTE already exist on the database.
Table dropped.
Table teste created.
Calling check_object..
TABLE TESTE already exist on the database.
grant select completed
Inserted row 1
Inserted row 2
Inserted row 3
columns are: NAME, SALARY
NAME -- SALARY --
Ximenes1 10001
Ximenes12 10002
Ximenes123 10003
Deleted rec: Ximenes1 10001
Listing table...
columns are: NAME, SALARY
NAME -- SALARY --
Ximenes12 10002
Ximenes123 10003
