PLSQL error PLS-00103: Encountered the symbol “CREATE” when expecting one of the following

Question

Hy, I'm trying to solve some task and I wrote this code:

DECLARE
previse EXCEPTION;
nema EXCEPTION;
CREATE OR REPLACE PROCEDURE POVECANJE_CIJENE(NOVA_SIFRA PO_PLOCICE.SIFRA_DOB%TYPE) IS 
BEGIN
    IF NOVA_SIFRA NOT IN (SELECT SIFRA_DOB FROM PO_DOBAVLJAC) THEN
        RAISE nema;
    ELSIF (CIJENA+30)>600 THEN
        RAISE previse;
    ELSE
        UPDATE PO_PLOCICE SET CIJENA=CIJENA+30 WHERE SIFRA_DOB=NOVA_SIFRA;

EXCEPTION
    WHEN nema THEN
        DBMS_OUTPUT.PUT_LINE('Nema te sifre');
    WHEN previse THEN
        DBMS_OUTPUT.PUT_LINE('Cijena je prešla 600 kn');
END POVECANJE_CIJENE;

BEGIN
    EXECUTE POVECANJE_CIJENE(2245);
END;

But it keep saying this error:

ORA-06550: line 4, column 1: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior

Does anybody know what is the problem?

Answer 1

You can't have a CREATE OR REPLACE PROCEDURE statement inside an anonymous PL/SQL block. If you want to create the procedure as a stored procedure you can create the procedure first:

CREATE OR REPLACE PROCEDURE POVECANJE_CIJENE(NOVA_SIFRA PO_PLOCICE.SIFRA_DOB%TYPE) IS 
  previse EXCEPTION;
  nema EXCEPTION;
BEGIN
    IF NOVA_SIFRA NOT IN (SELECT SIFRA_DOB FROM PO_DOBAVLJAC) THEN
        RAISE nema;
    ELSIF (CIJENA+30)>600 THEN
        RAISE previse;
    ELSE
        UPDATE PO_PLOCICE SET CIJENA=CIJENA+30 WHERE SIFRA_DOB=NOVA_SIFRA;

EXCEPTION
    WHEN nema THEN
        DBMS_OUTPUT.PUT_LINE('Nema te sifre');
    WHEN previse THEN
        DBMS_OUTPUT.PUT_LINE('Cijena je prešla 600 kn');
END POVECANJE_CIJENE;

and then call it from an anonymous block

BEGIN
  POVECANJE_CIJENE(2245);
END;

Or else you can make the procedure local to the PL/SQL block:

DECLARE
  previse EXCEPTION;
  nema EXCEPTION;

  PROCEDURE POVECANJE_CIJENE(NOVA_SIFRA PO_PLOCICE.SIFRA_DOB%TYPE) IS 
  BEGIN
    IF NOVA_SIFRA NOT IN (SELECT SIFRA_DOB FROM PO_DOBAVLJAC) THEN
        RAISE nema;
    ELSIF (CIJENA+30)>600 THEN
        RAISE previse;
    ELSE
        UPDATE PO_PLOCICE SET CIJENA=CIJENA+30 WHERE SIFRA_DOB=NOVA_SIFRA;

  EXCEPTION
    WHEN nema THEN
        DBMS_OUTPUT.PUT_LINE('Nema te sifre');
    WHEN previse THEN
        DBMS_OUTPUT.PUT_LINE('Cijena je prešla 600 kn');
  END POVECANJE_CIJENE;

BEGIN
  POVECANJE_CIJENE(2245);
END;

Note also that in a PL/SQL block (DECLARE...BEGIN...END) you don't use the EXECUTE statement, which is an SQL*Plus command.

Best of luck.

Answer 2

You can do this (probaj ovako pa ako ti ne radi javi da rješavamo dalje :) )

CREATE OR REPLACE PROCEDURE POVECANJE_CIJENE(NOVA_SIFRA PO_PLOCICE.SIFRA_DOB%TYPE) IS 
  previse EXCEPTION; 
  nema EXCEPTION;
  l_broj_cijena number;
  l_cijena number;
BEGIN 

  select count(1)
  into l_broj_cijena
  from PO_DOBAVLJAC
  where SIFRA_DOB = NOVA_SIFRA;

  select max(cijena)
  into l_cijena
  from PO_PLOCICE
  where SIFRA_DOB=NOVA_SIFRA;

  IF l_broj_cijena = 0 
  THEN 
    RAISE nema; 
  ELSIF (l_cijena+30)>600 THEN 
    RAISE previse; 
  ELSE 
    UPDATE PO_PLOCICE SET CIJENA=CIJENA+30 
    WHERE SIFRA_DOB=NOVA_SIFRA;
  END IF;

EXCEPTION 
  WHEN nema THEN DBMS_OUTPUT.PUT_LINE('Nema te sifre'); 
  WHEN previse THEN DBMS_OUTPUT.PUT_LINE('Cijena je prešla 600 kn'); 

END POVECANJE_CIJENE;