declare
  -- DELETE RETURNs many rows

  -- dynamic SQL
  -- fails at 9.2.0 with PLS-00429:
  -- unsupported feature with RETURNING clause
  --
  -- NO FLAVOR OF EXECUTE IMMEDIATE IS SUPPORTED WITH A RECORD BIND YET

  --
  -- Ideally the SQL syntax should allow not listing the columns explicitly
  v_avg_hire_date employees_0.hire_date%type := '25-JUN-97';
  v_emprecs Emp_Util.emprec_tab_t;
begin
  execute immediate '

    delete from employees_0 where hire_date < = :avg_hire_date
      returning
        employee_id,
        first_name,
        last_name,
        email,
        phone_number,

        hire_date,
        job_id,
        salary,
        commission_pct,
        manager_id,
        department_id
      into
        :employee_id,

        :first_name,
        :last_name,
        :email,
        :phone_number,
        :hire_date,
        :job_id,
        :salary,
        :commission_pct,
        :manager_id,

        :department_id'
    using v_avg_hire_date
    returning bulk collect into v_emprecs;
end;
/
-- ERROR at line N:
rollback /* prepare for repeat or for next sample */;
-- Rollback complete.






declare
  -- WORKAROUND
  -- List all the columns for RETURNING both in the SQL
  -- and in the target out-bind
  -- Doesn't use RECORD bind so works pre 9.2.0
  v_avg_hire_date employees_0.hire_date%type := '25-JUN-97';

  type Employee_Id_Tab_t    is table of employees_0.employee_id    %type index by binary_integer;

  type First_Name_Tab_t     is table of employees_0.first_name     %type index by binary_integer;
  type Last_Name_Tab_t      is table of employees_0.last_name      %type index by binary_integer;
  type Email_Tab_t          is table of employees_0.email          %type index by binary_integer;
  type Phone_Number_Tab_t   is table of employees_0.phone_number   %type index by binary_integer;
  type Hire_Date_Tab_t      is table of employees_0.hire_date      %type index by binary_integer;
  type Job_Id_Tab_t         is table of employees_0.job_id         %type index by binary_integer;
  type Salary_Tab_t         is table of employees_0.salary         %type index by binary_integer;
  type Commission_Pct_Tab_t is table of employees_0.commission_pct %type index by binary_integer;
  type Manager_Id_Tab_t     is table of employees_0.manager_id     %type index by binary_integer;
  type Department_Id_Tab_t  is table of employees_0.department_id  %type index by binary_integer;

  v_employee_ids    Employee_Id_Tab_t;

  v_first_names     First_Name_Tab_t;
  v_last_names      Last_Name_Tab_t;
  v_emails          Email_Tab_t;
  v_phone_numbers   Phone_Number_Tab_t;
  v_hire_dates      Hire_Date_Tab_t;
  v_job_ids         Job_Id_Tab_t;
  v_salaries        Salary_Tab_t;
  v_commission_pcts Commission_Pct_Tab_t;
  v_manager_ids     Manager_Id_Tab_t;
  v_department_ids  Department_Id_Tab_t;

  v_emprecs Emp_Util.emprec_tab_t;
begin

  execute immediate '
    delete from employees_0 where hire_date < = :avg_hire_date
      returning
        employee_id,
        first_name,
        last_name,
        email,
        phone_number,
        hire_date,
        job_id,
        salary,
        commission_pct,
        manager_id,
        department_id

      into
        :employee_id,
        :first_name,
        :last_name,
        :email,
        :phone_number,
        :hire_date,
        :job_id,
        :salary,
        :commission_pct,
        :manager_id,
        :department_id'
    using v_avg_hire_date
    returning bulk collect into
      v_employee_ids,

      v_first_names,
      v_last_names,
      v_emails,
      v_phone_numbers,
      v_hire_dates,
      v_job_ids,
      v_salaries,
      v_commission_pcts,
      v_manager_ids,
      v_department_ids;

  for j in v_last_names.First..v_last_names.Last
  loop
    v_emprecs(j).employee_id     := v_employee_ids(j);
    v_emprecs(j).first_name      := v_first_names(j);
    v_emprecs(j).last_name       := v_last_names(j);

    v_emprecs(j).email           := v_emails(j);
    v_emprecs(j).phone_number    := v_phone_numbers(j);
    v_emprecs(j).hire_date       := v_hire_dates(j);
    v_emprecs(j).job_id          := v_job_ids(j);
    v_emprecs(j).salary          := v_salaries(j);
    v_emprecs(j).commission_pct  := v_commission_pcts(j);
    v_emprecs(j).manager_id      := v_manager_ids(j);
    v_emprecs(j).department_id   := v_department_ids(j);
  end loop;
  Emp_Util.Show_All ( v_emprecs );
end;
/
-- PL/SQL procedure successfully completed.
rollback /* prepare for repeat or for next sample */;
-- Rollback complete.