CODEWITHSUNDEEP
plsqloracle-apex
vv ss
vv ss

Reputation: 3

Can someone, please, tell me what is wrong? PLSQL in ORACLE APEX

```
create or replace procedure cauta_magazinee(supraf NUMBER) is
begin
 c_idd MAGAZINE.ID%TYPE;
 c_numee MAGAZINE.NUME%TYPE;
 c_nr_angg MAGAZINE.NR_ANGAJATI%TYPE;
 idcentru MAGAZIN_CENTRU.ID_CENTRU%TYPE;
 numecentru CENTRU_COMERCIAL.NUME%TYPE;


--dbms_output.put_line('Magazinele cu o supratata mai mica decat '||supraf||' m^3 sunt:');
declare cursor magazine_c is
  select ID, NUME, NR_ANGAJATI form MAGAZINE where SUPRAFATA<supraf;

  begin
    open magazine_c;
    loop
     fetch  magazine_c into c_idd, c_numee, c_nr_angg;
     exit when  magazine_c%NOTFOUND;
     select ID_CENTRU into idcentru from MAGAZIN_CENTRU where c_idd=ID_MAGAZIN;
     select NUME into numecentru from CENTRU_COMERCIAL where idcentru=ID;
     dbms_output.put_line(c_idd||' ' ||c_numee||' '||c_nr_angg|| ' din centrul comercial'||numecentru);
    end loop;
    close magazine_c;
  end;

end cauta_magazinee;
/

```

I really don't know what is wrong... It is written in PLSQL-oracle apex I get that error but the code is correctly written

Upvotes: 0

Views: 190

Answers (1)

Belayer
Belayer

Reputation: 14861

Your statement "but the code is correctly written" is incorrect. The variables declaration for c_idd through numecentru is incorrect as they appear in the execution section of the outer block. The following is your code with annotations indicated by '<<'. 

create or replace procedure cauta_magazinee(supraf NUMBER) is
begin                                    << initiates Execution Section of procedure 
 c_idd MAGAZINE.ID%TYPE;                 << 
 c_numee MAGAZINE.NUME%TYPE;             <<  Defined in the 
 c_nr_angg MAGAZINE.NR_ANGAJATI%TYPE;    <<  execution section of procedure
 idcentru MAGAZIN_CENTRU.ID_CENTRU%TYPE; << 
 numecentru CENTRU_COMERCIAL.NUME%TYPE;  << 


--dbms_output.put_line('Magazinele cu o supratata mai mica decat '||supraf||' m^3 sunt:');
declare cursor magazine_c is             << declare opens inner block
  select ID, NUME, NR_ANGAJATI form MAGAZINE where SUPRAFATA<supraf;

  begin                                  << Executation section of inner block
    open magazine_c;
    loop
     fetch  magazine_c into c_idd, c_numee, c_nr_angg;
     exit when  magazine_c%NOTFOUND;
     select ID_CENTRU into idcentru from MAGAZIN_CENTRU where c_idd=ID_MAGAZIN;
     select NUME into numecentru from CENTRU_COMERCIAL where idcentru=ID;
     dbms_output.put_line(c_idd||' ' ||c_numee||' '||c_nr_angg|| ' din centrul comercial'||numecentru);
    end loop;
    close magazine_c;
  end;                                   << closes inner block 

end cauta_magazinee;                     << closes outer block

One thing I have discovered over the years is: when you receive a compile error your code is NOT correct. You may not see the error but it's there. Trust the compiler it's going to win the battle every time.

To correct just elminate the unnecessary inner block:

create or replace procedure cauta_magazinee(supraf NUMBER) is

 c_idd MAGAZINE.ID%TYPE;                 
 c_numee MAGAZINE.NUME%TYPE;             
 c_nr_angg MAGAZINE.NR_ANGAJATI%TYPE;    
 idcentru MAGAZIN_CENTRU.ID_CENTRU%TYPE; 
 numecentru CENTRU_COMERCIAL.NUME%TYPE;  

 cursor magazine_c is              
  select ID, NUME, NR_ANGAJATI form MAGAZINE where SUPRAFATA<supraf;

 begin                                  
    open magazine_c;
    loop
     fetch  magazine_c into c_idd, c_numee, c_nr_angg;
     exit when  magazine_c%NOTFOUND;
     select ID_CENTRU into idcentru from MAGAZIN_CENTRU where c_idd=ID_MAGAZIN;
     select NUME into numecentru from CENTRU_COMERCIAL where idcentru=ID;
     dbms_output.put_line(c_idd||' ' ||c_numee||' '||c_nr_angg|| ' din centrul comercial'||numecentru);
    end loop;
    close magazine_c;
 end cauta_magazinee;

Not sure how the Apex editor works but can try 

show errors 
or
select * from user_errors;

To see actual pl/sql error messages.

Upvotes: 2

Related Questions