Stuck at Dynamic SQL execution in a procedure I'm learning PL/SQL and using Oracle 11g as my Database and HR as my schema for practice. There's one scenario where I'm stuck and not sure why it's not working. The requirement is:

"Create a procedure to extract all employees from employees table that are not managers and put them into new table non_managers".

NOTE: It could be done my many ways and one of the ways is by using composite data structures but I haven't reached that chapter so my solution listed below is based on what I've read till now and would like solutions based on these approaches first. But if there's a better way of doing this please do let me know. Probably I'll cover those topics later on during my reading.

As solution I came up with two approaches, approach 1 doesn't work for me but approach 2 works absolutely fine. I need your help on approach 1:

Approach 1: Doesn't work


Write a procedure


The procedure will have a cursor to hold all employees that are not managers
Create a table NON_MANAGERS to hold the data
INSERT every row of the cursor into the NON_MANAGERS table.

Write an anonymous block to run the above procedure.


Approach 2: Works Absolutely Fine


Create the table to hold the result of the procedure.
Write the procedure with below requirements:


create a cursor to hold all employees that are not managers
INSERT each row of the cursor in the NON_MANAGERS table created in step 1

Write an anonymous block to run the above procedure.


Here's my code for approach 1 that's not working:

-- Step 1
CREATE OR REPLACE PROCEDURE not_manager_procedure
IS
  v_SQL VARCHAR2(3000);
  v_insert_data VARCHAR2(3000);
  CURSOR emp_cur
  IS
    SELECT *
    FROM employees
    WHERE employee_id NOT IN
      (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
      );
BEGIN
  v_SQL := 'CREATE TABLE non_managers AS (SELECT * FROM employees WHERE 1=2)';
  EXECUTE IMMEDIATE v_SQL;
  --DBMS_OUTPUT.PUT_LINE();
  FOR emp_row IN emp_cur
  LOOP
    v_insert_data := 'INSERT INTO non_manager VALUES    
      (
      '||emp_row.employee_id||',
      '''||emp_row.fiRst_name||''',
      '''||emp_row.last_name||''',
      '''||emp_row.email||''',
      '''||emp_row.phone_number||''',
      '||emp_row.hire_date||',
      '''||emp_row.job_id||''',
      '||emp_row.commission_pct||',
      '||emp_row.manager_id||',
      '||emp_row.department_id||'
      )'; 
    EXECUTE IMMEDIATE v_insert_data;
    DBMS_OUTPUT.PUT_LINE(v_insert_data);
  END LOOP;
  COMMIT;
END not_manager_procedure;
/

-- Step 2
BEGIN
  not_manager_procedure;
END;
/


The procedure gets complied properly and if I comment the "Execute Immediate v_insert_data;" statement and run the anonymous block in step 2, I can see 89 records in the "DBMS Output" pane in SQL Developer. But as soon as I uncomment that statement and try to run it, it gives me the below error:

Error starting at line : 40 in command -

    BEGIN
      not_manager_procedure;
    END;
    Error report -
    ORA-00936: missing expression
    ORA-06512: at "HR.NOT_MANAGER_PROCEDURE", line 31
    ORA-06512: at line 3
    00936. 00000 -  "missing expression"
    *Cause:    
    *Action:


Any help would be good.
There were couple of issues that @vercelli pointed out in the comments. The main issue why the code was not working was that two columns COMMISSION_PCT and DEPARTMENT_ID had NULL values for many records in the cursor. In order to handle those NULL values, I had to use NVL function on these two columns within the INSERT clause as listed below. The code looks like below now:

-- Step 1
CREATE OR REPLACE PROCEDURE not_manager_procedure
IS
  v_SQL VARCHAR2(3000);
  v_insert_data VARCHAR2(3000);
  CURSOR emp_cur
  IS
    SELECT *
    FROM employees
    WHERE employee_id NOT IN
      (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
      );
BEGIN
  v_SQL := 'CREATE TABLE non_managers AS (SELECT * FROM employees    WHERE     1=2)';
  EXECUTE IMMEDIATE v_SQL;
  --DBMS_OUTPUT.PUT_LINE();
  FOR emp_row IN emp_cur
  LOOP
    v_insert_data := 'INSERT INTO non_managers VALUES    
      (
      '||emp_row.employee_id||',
      '''||emp_row.first_name||''',
      '''||emp_row.last_name||''',
      '''||emp_row.email||''',
      '''||emp_row.phone_number||''',
      '''||emp_row.hire_date||''',
      '''||emp_row.job_id||''',
      '||emp_row.salary||',
      '||NVL(emp_row.commission_pct, 0)||',
      '||emp_row.manager_id||',
      '||NVL(emp_row.department_id, 0)||'
      )'; 
    DBMS_OUTPUT.PUT_LINE(v_insert_data);
    EXECUTE IMMEDIATE v_insert_data;
    --DBMS_OUTPUT.PUT_LINE(v_insert_data);
  END LOOP;
  COMMIT;
END not_manager_procedure;
/

-- Step 2
BEGIN
  not_manager_procedure;
END;
/


I'll try to write this code again without using cursor and dynamic SQL as suggested by Vercelli but this requirement was to understand cursor, procedure and dynamic SQL. 

Thanks everyone.