Stuck at Dynamic SQL execution in a procedure

Question

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

1. 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.
2. Write an anonymous block to run the above procedure.

Approach 2: Works Absolutely Fine

1. Create the table to hold the result of the procedure.
2. 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
3. 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.

• kevinskio: using execute immediate is only one solution. Can you create the non manager table before running the procedure? Then this becomes a simple insert.
• vercelli: Also you can use insert/select without a cursor.
• vercelli: You are creating table non_managers but insert into non_manager (without 's')
• puneet: thanks Vercelli for catching that. I corrected the table name in the insert statement but got the same error.
• puneet: @vercelli thanks a lot for replying. It is working now. The issue was with commission_pct and department_id being NULL for some of the records. Used NVL to handle NULL for these two columns in Insert clause and that populated the non_managers table.

Answer 1

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.