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.
Best Answer
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:
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.