oracle: populate a table from another schema using PL/sql procedure

Question

hi i'm newb in pl/sql :), this is for educational pruposes only. the schama Dispatching including a table named Employes.and PRF schema that include a table named ZONE.

Dispatching : Employes(num_emp number,name nvarchar2,design_unit varchar2,design_zone varchar2) and PRF: ZONE(num_zone number,design_zone varchar2,number_of_units number). the problema is writing a pl/sql procedure to populate ZONE table from Employes table. this is my procedure :

create or replace procedure zoneD as

cursor   cur is select design_zone,design_unit from dispatching.employes group by design_zone,design_unit;  
varzone  cur%rowtype;



begin

open cur;

fetch cur into varzone;loop
exit when cur%notfound;

 insert into zone(num_zone,design_zone,nbr_of_unit) values (num_zone.nextval,varzone.design_zone,0);

 update zone set nbr_of_unit =( select count(design_unit) from dispatching.employes);

end loop;


close cur;


end zoneD;

the unit is a town , each zone contains many units. in a simple way the prob the procedure does not insert the data i dont know if it is the right way to do that. (sorry about my english :)).

i was late, i suppose th "e" is an object of employes table ? — khaledz
– khaledz, Commented Jan 3, 2018 at 21:51

Littlefoot · Accepted Answer · 2017-12-31 12:19:18Z

1

It seems that you are connected as PRF and want to fetch values that belong to DISPATCHING user. In order to do that, DISPATCHING has to grant (at least) SELECT on its EMPLOYEES table to PRF:

-- connect as DISPATCHING
grant select on employees to prf;

A procedure (as you're practicing PL/SQL) should utilize a cursor FOR loop as it is much easier to maintain than a loop which uses explicitly declared cursor (as you don't need to declare it as well as variable(s) you need to store its values into), open it, worry when to exit the loop and - finally - close it. Cursor FOR loop does all of that for you (OK, except writing a SELECT statement which is just the same as the one you'd use while declaring an explicit cursor).

-- connect as PRF
create or replace procedure zoned as
begin
  -- cursor FOR loop - you can select both DESIGN_ZONE and count number of
  -- units so that you wouldn't have to update that value separately
  for cur_r in (select e.design_zone, count(*) number_of_units
                from dispatching.employees e  -- naming the owner which granted SELECT on its table to PRF user
                group by e.design_zone
               )
  loop
    insert into zone (num_zone, design_zone, number_of_units)
      values (num_zone.nextval, cur_r.design_zone, cur_r.number_of_units);
  end loop;
end;
/

That should do it (unless I made a typo).

Finally, a suggestion, if I may: do format your code properly. The one you posted is a mess difficult to read - no indentation, too long lines (break them!), and it contains only several lines. Imagine what happens when you have thousands of lines of code - who do you expect to debug it? Just a month or two after you've done with that code, you'll forget what you did and why (so comment it), and - if it is unformatted - you'll get a headache. Today's GUI tools offer automatic formatting, so - use it. Otherwise, there are free online formatters, such as Instant SQL Formatter.

answered Dec 31, 2017 at 12:19

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

khaledz Over a year ago

thank you sir i learn a lot from you , especialy about using control statements.

khaledz Over a year ago

is it the "e" created with an "create type" ?

Bob Jarvis - Слава Україні Over a year ago

In this case e is what's called an "alias" for EMPLOYEES. Inside this query it can be used anywhere you'd normally use EMPLOYEES - so, as you can see, the person writing the query wrote SELECT e.DESIGN_ZONE instead of SELECT EMPLOYEES.DESIGN_ZONE. It's just a short way to refer to EMPLOYEES.

khaledz Over a year ago

i understand , that the alias 'e' just its a pointer to employees table, i can use it without any pre-declaration , thank you Bob.

khaledz Over a year ago

i want to populate PRF : unit_d(num_unit number,design_unit varchar2,nbr_employes number, num_zone) from zone table and employes table , the question is one cursor enugh ? i have to use join or a group by.

|

Collectives™ on Stack Overflow

oracle: populate a table from another schema using PL/sql procedure

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related