I am trying to build a stored subprogram of type procedure that should include 5 tables. I am getting the errors "14/2 PL/SQL: SQL Statement ignored" "17/7 PL/SQL: ORA-00920: invalid relational operator". Please could you help with this. Thank you!

CREATE OR REPLACE PROCEDURE medicamente_queries
(nnume Functionar.Nume%TYPE)
IS 
tel Telefon_functionar.Numar%TYPE; 
nr number(1):=0;
BEGIN
SELECT t.Numar INTO tel
FROM Telefon_functionar t
LEFT OUTER JOIN Functionar f
ON t.CNP= f.CNP
WHERE f.Nume= nname;
DBMS_OUTPUT.PUT_LINE('Functionarul '||nname||' avand telefonul '||tel||' a emis 
cererile:');
FOR i IN (
SELECT id_vanzari, CodProdus, Stoc  
FROM ProduseVandute p
LEFT OUTER JOIN Functionar f
ON p p.codprodus = f.codprodus
LEFT OUTER JOIN Vanzari vanz
ON p.id_vanzari =vanz.id_vanzari
LEFT OUTER JOIN Furnizor fur
ON vanz.CUI=fur.CUI
WHERE f.Nume = nnume) LOOP
DBMS_OUTPUT.PUT_LINE(i.CodProdus||' pentru functionarul '||i.Stoc||' cu vanzarile 
'||i.ID_Vanzari);
nr:=nr 1;
END LOOP;
IF nr=0
THEN DBMS_OUTPUT.PUT_LINE('Nu au fost emise cereri');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20101, 'Nu exista functionari cu numele '||nnume);
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR(-20102, 'Exista mai multi angajati cu numele '||nnume);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20103,'Alta eroare!');
END; 

EXECUTE medicamente_queries;

CodePudding user response：

You've got p where you don't need it, here in cursor FOR loop:

SELECT id_vanzari, CodProdus, Stoc  
FROM ProduseVandute p
LEFT OUTER JOIN Functionar f
ON p p.codprodus = f.codprodus
   -
   ^
   here
LEFT OUTER JOIN Vanzari vanz
ON p.id_vanzari =vanz.id_vanzari
LEFT OUTER JOIN Furnizor fur
ON vanz.CUI=fur.CUI
WHERE f.Nume = nnume

Apart from that, always specify table alias in select column list. The way you have it now, query will raise ORA-00918: column ambiguously defined because it is unclear which table id_vanzari belongs to.