EXECUTE IMMEDIATE is simple option to execute a Dynamic SQL.
Here is an example
TableName = 'CUSTOMER';
SQLString = 'DELETE FROM '+ %TRIM(TableName) ;
EXECUTE IMMEDIATE : SQLString
This is a very simple example.
- Program gets a TableName as input parameter and program logic delete all records from that table.
- Based on TableName variable a SQL DELETE statement is assembled and saved in SQLString variable.
- In final step SQLString variable is passed to EXECUTE IMMEDIATE as host variable and all done.
So what does actually EXECUTE IMMEDIATE do ?
- Create a access plan based on SQL Statement provided.A new “access plan” is created each time any statement is executed.
- If every thing is fine run the SQL Statement.
- Delete the access plan created for this SQL Statement.
So EXECUTE IMMEDIATE perform both PREPARE (the access plan) and EXECUTE (the SQL statement) in a single step. You can run almost any DML (except SELECT statement) or DDL SQL statement using EXECUTE IMMEDIATE.
You can not use EXECUTE IMMEDIATE for SELECT statement so you can not use EXECUTE IMMEDIATE to declare a CURSOR.
Here is list of SQL statement allowed with EXECUTE IMMEDIATE:
DECLARE GLOBAL TEMPORARY
SET CURRENT DEGREE
SET CURRENT DECFLOAT ROUNDING MODE
SET CURRENT DEBUG MODE
SET CURRENT LOCALE LC_CTYPE
SET CURRENT MAINTAINED TABLE TYPES
SET CURRENT OPTIMIZATION HINT
SET CURRENT PRECISION
SET CURRENT QUERY ACCELERATION
SET CURRENT REFRESH AGE
SET CURRENT ROUTINE VERSION
SET CURRENT RULES
SET CURRENT SQLID
SET ENCRYPTION PASSWORD
EXECUTE IMMEDIATE should used when the SQL statement only needs to be executed once or infrequently as cost of creating a new access plan every time to execute the SQL statement is a little bit on higher side.