EXECUTE IMMEDIATE USING IN : Execute immediate « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Execute immediate

SQL>
SQL> CREATE TABLE employee(
  2       employee_id VARCHAR2(3) PRIMARY KEY,
  3       first_name  VARCHAR2(15),
  4       last_name   VARCHAR2(20),
  5       hire_date   DATE
  6       );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE run_9am_procedure (
  2     id_in     IN   employee.employee_id%TYPE,
  3     hour_in   IN   INTEGER
  4  )
  5  IS
  6     v_apptcount   INTEGER;
  7     v_name        VARCHAR2 (100);
  8  BEGIN
  9     EXECUTE IMMEDIATE 'BEGIN '
 10                       || TO_CHAR (SYSDATE, 'DAY')
 11                       || '_set_schedule (:id, :hour, :name, :appts); END;'
 12        USING IN id_in, IN hour_in, OUT v_name, OUT v_apptcount;
 13
 14     DBMS_OUTPUT.put_line (v_name|| ' has '|| v_apptcount|| ' appointments on '|| TO_CHAR (SYSDATE));
 15  END;
 16  /

Procedure created.

SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL>