2

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.

  • using execute immediate is only one solution. Can you create the non manager table before running the procedure? Then this becomes a simple insert. – kevinsky Aug 16 '16 at 16:40
  • Also you can use insert/select without a cursor. – vercelli Aug 16 '16 at 16:44
  • You are creating table non_managers but insert into non_manager (without 's') – vercelli Aug 16 '16 at 16:44
  • 1
    thanks Vercelli for catching that. I corrected the table name in the insert statement but got the same error. – puneet Aug 16 '16 at 16:47
  • 1
    @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. – puneet Aug 16 '16 at 18:31
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.