NATIVE DYNAMIC SQL (NDS) - Execute immediate
DDL's must be executed via dynamic sql within the pl/sql.
Syntax:
EXECUTE IMMEDIATE <SQL_string>
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...];
Note: <SQL_string> can be an SQL statement or PL/SQL block.
If <SQL_string> is a pl/sql block, then it ends with a semi colon(;).
If <SQL_string> is a sql statement(DML or DDL), then don't end with a semi colon(;).
Note: DDL statement via execute immediate will issue an implicit commit.
Method 1:
No queries; just DDL statements and UPDATEs, INSERTs, or DELETEs, which have no bind variables.
Example:
Begin
Execute immediate ('create table test_table(rno number,name varchar2(100))');
End;
/
Method 2: (USING clause)
No queries; just UPDATEs, INSERTs, or DELETEs, with a fixed number of bind variables.
Example:
Begin
Execute immediate 'update scott.emp set sal=sal*:percent where deptno=:deptno' using 0.2,20;
Dbms_output.put_line(sql%rowcount);
End;
/
Method 3:
A. Single row data (INTO & USING clauses)
Queries (SELECT statements) with a fixed numbers of columns and bind variables, retrieving a single row of data
Example:
Declare
p_deptno number;
p_sal number;
p_hike number;
Begin
Execute immediate 'select deptno,max(sal),:hike_pct * 100 from scott.emp
where deptno=:deptno
group by deptno'
into p_deptno,p_sal,p_hike
using 0.2,20;
Dbms_output.put_line('Deptno:'|| p_deptno || ' Max salary:'|| p_sal || ' Hike perct:' || p_hike);
End;
/
B. Multiple row data ( BULK COLLECT INTO & USING clauses) / (OPEN FOR)
Queries (SELECT statements) with a fixed numbers of columns and bind variables, retrieving or more rows of data.
Example:
Declare
Type emp_tab_col is table of scott.emp%rowtype;
my_emp_tab emp_tab_col:=emp_tab_col();
Begin
Execute immediate 'select * from scott.emp
where deptno=:deptno'
Bulk collect into my_emp_tab
using 20;
For i in my_emp_tab.first..my_emp_tab.last
loop
dbms_output.put_line(my_emp_tab(i).empno || ':' || my_emp_tab(i).sal || ':' || my_emp_tab(i).deptno);
end loop;
End;
/
Method 4:
A statement in which the numbers of columns selected (for a query) or the number of bind variables set are not known until runtime.
(will be executing a dynamic PL/SQL block, rather than a SQL string).
Example:
CREATE OR REPLACE PROCEDURE
get_rows (
table_in IN VARCHAR2,
where_in IN VARCHAR2
)
IS
BEGIN
EXECUTE IMMEDIATE
'DECLARE
l_row ' || table_in || '%ROWTYPE;
BEGIN
SELECT * INTO l_row
FROM ' || table_in || ' WHERE ' || where_in || ';
END;';
END get_rows;
BIND VARIABLES:
We can bind into our SQL statement only those expressions (literals, variables, complex expressions) that replace placeholders for data values inside the dynamic string. We cannot bind in the names of schema elements (tables, columns, etc.) or entire chunks of the SQL statement (such as the WHERE clause).
For those parts of our string, we must use concatenation.
IN / OUT / IN OUT : (Argument types)
When we are executing a dynamic query, all bind arguments must be of mode IN,except when we are taking advantage of the RETURNING clause & dynamic PL/SQL.
Example:
PROCEDURE analyze_new_technology (
tech_name IN VARCHAR2,
analysis_year IN INTEGER,
number_of_adherents IN OUT NUMBER,
projected_revenue OUT NUMBER,
) ... ;
DECLARE
devoted_followers NUMBER;
est_revenue NUMBER;
BEGIN
EXECUTE IMMEDIATE
'BEGIN
analyze_new_technology (:p1, :p2, :p3, :p4); END;'
USING 'oracle', 2010, devoted_followers, est_revenue;
END;
NOTE: OUT & IN OUT variable type and procedure parameters must be of the same data type.
Duplicate Placeholders:
When we are executing a dynamic SQL string (DML or DDLin other words, the string does not end in a semicolon), we must supply an argument for each placeholder, even if there are duplicates.
Example:
CREATE OR REPLACE PROCEDURE updnumval (
col_in IN VARCHAR2,
start_in IN DATE, end_in IN DATE,
val_in IN NUMBER)
IS
dml_str VARCHAR2(32767) :=
'UPDATE emp SET ' || col_in || ' = :val
WHERE hiredate BETWEEN :lodate AND :hidate
AND :val IS NOT NULL';
BEGIN
EXECUTE IMMEDIATE dml_str
USING val_in, start_in, end_in, val_in;
END;
When we are executing a dynamic PL/SQL block (the string ends in a semicolon), we must supply an argument for each unique placeholder.
Example:
CREATE OR REPLACE PROCEDURE updnumval (
col_in IN VARCHAR2,
start_in IN DATE, end_in IN DATE,
val_in IN NUMBER)
IS
dml_str VARCHAR2(32767) :=
'BEGIN
UPDATE emp SET ' || col_in || ' = :val
WHERE hiredate BETWEEN :lodate AND :hidate
AND :val IS NOT NULL;
END;';
BEGIN
EXECUTE IMMEDIATE dml_str
USING val_in, start_in, end_in;
END;
Passing NULL Values:
We can't directly pass NULL via USING.
We will encounter an error, if we try to pass NULL directly. Because NULL doesn't have any datatype.
EXECUTE IMMEDIATE
'UPDATE employee SET salary = :newsal
WHERE hire_date IS NULL'
USING NULL;
So we need to pass an uninitialized variable (default null) or to_number(NULL).
BEGIN
EXECUTE IMMEDIATE
'UPDATE employee SET salary = :newsal
WHERE hire_date IS NULL'
USING TO_NUMBER (NULL);
END;
NOTE:
It turns out that a dynamically constructed and executed PL/SQL block is not treated as a nested block;
instead, it is handled as if it were a procedure or function called from within the current block.
So any variables local to the current or enclosing blocks are not recognized in the dynamic PL/SQL block;
we can make references only to globally defined programs and data structures. These PL/SQL elements include standalone functions and procedures and any elements defined in the specification of a package.
