Oracle Database Dynamic SQL
Introduction
Dynamic SQL allows you to assemble an SQL query code in the runtime. This technique has some disadvantages and have to be used very carefully. At the same time, it allows you to implement more complex logic. PL/SQL requires that all objects, used in the code, have to exist and to be valid at compilation time. That's why you can't execute DDL statements in PL/SQL directly, but dynamic SQL allows you to do that.
Remarks
Some important remarks:
-
Never use string concatenation to add values to query, use parameters instead. This is wrong:
execute immediate 'select value from my_table where id = ' || id_valiable into result_variable;And this is right:
execute immediate 'select value from my_table where id = :P ' using id_valiable into result_variable;There are two reasons for this. The first is the security. String concatenation allows to make SQL injection. In the query below, if a variable will contain value
1 or 1 = 1, theUPDATEstatement will update all lines in the table:execute immediate 'update my_table set value = ''I have bad news for you'' where id = ' || id;The second reason is performance. Oracle will parse query without parameters every time when it executes, while query with parameter will be parsed only once in the session.
-
Note, that when the database engine executes a DDL statement, it executes implicit commit before.
