1

**Here i firstly create the bonuses table when i call execute immediate create_table and fill_table and then the cursor i to fetch data from it, but it gives me an error of table or view does not exist **.

declare

   --My table
   create_table VARCHAR2( 100 ) :=  '
   create table bonuses
  (
    employee_id NUMBER(6), bonus NUMBER DEFAULT 100
  )';

---String query that fetch data from hr and oe tables to fill bonus table
  fill_table VARCHAR2( 200 ) :=   'INSERT INTO bonuses(employee_id)
   (SELECT employee_id FROM hr.employees e, oe.orders o
   WHERE e.employee_id = o.sales_rep_id
   GROUP BY e.employee_id)  ';

  CURSOR foo is 
  select A.employee_id, A.salary 
  FROM HR.employees A
  JOIN HR.DEPARTMENTS B
  ON A.department_id = B.DEPARTMENT_ID
  JOIN HR.LOCATIONS C
  ON B.location_id = C.location_id
  JOIN  HR.COUNTRIES D
  ON C.COUNTRY_ID = D.COUNTRY_ID
  JOIN HR.REGIONS E 
  ON D.REGION_ID = E.REGION_ID
  WHERE E.REGION_NAME = 'Europe';

begin

execute immediate create_table;
execute immediate fill_table;
commit;

---here happens the error
FOR i IN ( SELECT employee_id FROM bonuses) LOOP

    FOR j in foo LOOP

      IF i.employee_id = j.employee_id THEN
          dbms_output.put_line( 1 );
--        UPDATE bonuses set bonus = bonus + j.salary * 0.01;
      ELSE
          dbms_output.put_line( 2 );
--        UPDATE bonuses set bonus =  j.salary * 0.01;
      END IF; 
    END LOOP;
END LOOP;

end;

[and this is the error that a get after executing. I am using a sys session by the way.][1]

[1]: https://i.stack.imgur.com/mMtWV.png

Orges_Kreka
  • 41
  • 1
  • 5
  • Let's see it from the Oracle point of view: how can Oracle know that your string will create a table `bonuses`? Oracle knows that at the time you run the script this table does not exist, so you can not use it. If you need to create a table with dynamic sql ( and this is not a great idea if you don't striclty need it) you only can use it in dynamic sql. – Aleksej May 23 '17 at 14:24

0 Answers0