Using Dynamic SQL in PL/SQL

PL/SQL supports both dynamic and static SQL. Dynamic SQL enables you to build SQL statements dynamically at run time while static SQL statements are known in advance. You can create more general-purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation time.

To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. Dynamic SQL is especially useful for executing SQL statements to create database objects, such as CREATE TABLE.

Example: Using Dynamic SQL to Manipulate Data in PL/SQL shows an example of the use of dynamic SQL to manipulate data in a table.

Using Dynamic SQL to Manipulate Data in PL/SQL

DECLARE
   sql_stmt          VARCHAR2(200); -- variable to hold SQL statement
   column_name       VARCHAR2(30);  -- variable for column name
   dept_id           NUMBER(4);
   dept_name         VARCHAR2(30);
   mgr_id            NUMBER(6);
   loc_id            NUMBER(4);
BEGIN
-- create a SQL statement (sql_stmt) to execute with EXECUTE IMMEDIATE
-- the statement INSERTs a row into the departments table using bind variables
-- note that there is no semi-colon (;) inside the quotation marks '...'
  sql_stmt := 'INSERT INTO departments VALUES (:dptid, :dptname, :mgrid, :locid)';
  dept_id := 46; 
  dept_name := 'Special Projects'; 
  mgr_id := 200; 
  loc_id := 1700;
-- execute the sql_stmt using the values of the variables in the USING clause 
-- for the bind variables
  EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;

-- use EXECUTE IMMEDIATE to delete the row that was previously inserted,
-- substituting for the column name and using a bind variable
  column_name := 'DEPARTMENT_ID';
  EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || column_name  || ' = :num'
      USING dept_id;
END;
/

Example: Using Dynamic SQL to Create a Table in PL/SQL is an example of the use of dynamic SQL to create a table. For a more complete example, see Example: Creating a Stored Procedure With the AUTHID Clause.

Using Dynamic SQL to Create a Table in PL/SQL

DECLARE
  tabname       VARCHAR2(30); -- variable for table name
  current_date  VARCHAR2(8);  -- varible for current date
BEGIN
-- extract, format, and insert the year, month, and day from SYSDATE into 
-- the current_date variable
  SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) || 
     TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') || 
     TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') INTO current_date FROM DUAL;
-- construct the table name with the current date as a suffix
  tabname := 'log_table_' || current_date;
-- use EXECUTE IMMEDIATE to create a table with tabname as the table name
  EXECUTE IMMEDIATE 'CREATE TABLE ' || tabname || 
                    '(op_time VARCHAR2(10), operation VARCHAR2(50))' ;
  DBMS_OUTPUT.PUT_LINE(tabname || ' has been created');
-- now drop the table
  EXECUTE IMMEDIATE 'DROP TABLE ' || tabname;
END;
/