Delete an SQL row with PL/SQL

Question

I want to delete rows by entering the ID hospital but for some reason I get an error when executing the procedure.

CREATE OR REPLACE PROCEDURE BorrarHospital (codihospital IN OUT NUMBER)
IS
idhospital NUMBER;
CURSOR C1 (codihospital NUMBER) IS SELECT HOSPITAL_CODI FROM HOSPITAL WHERE HOSPITAL_CODI = codihospital;

BEGIN

idhospital := codihospital;
OPEN C1 (codihospital);
FETCH C1 INTO codihospital;
IF (C1%FOUND) THEN
DELETE FROM HOSPITAL WHERE HOSPITAL_CODI = idhospital;
ELSE
DBMS_OUTPUT.PUT_LINE('No hi ha cap hospital amb aquest codi');
END IF;
COMMIT;
CLOSE C1;
END;

-

DECLARE
codihospital NUMBER;

BEGIN 
BorrarHospital(99);
END;

I get this error

ORA-06550: line 6, column 16:
PLS-00363: expression '99' cannot be used as an assignment target
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored

Can someone tell me what I'm doing wrong? I just started learning PL/SQL and I didn't find how I can solve this problem.

APC · Accepted Answer · 2014-05-25 08:45:15Z

1

You declare your parameter as codihospital IN OUT NUMBER. That means it takes input and output. So you need to pass a variable, something which can take a value. 99 is a constant, hence cannot be assigned to. Which is what the error message says.

Fortunately it only takes a small change to the code to correct it: simply pass the variable you've declared but aren't using at the moment. (I suspect you think you are using it but that's not how things work in PL/SQL).

DECLARE
  codihospital NUMBER;

BEGIN 
  codihospital := 99;
  BorrarHospital(codihospital);
END;

edited May 25, 2014 at 8:45

answered May 24, 2014 at 21:53

APC

147k20 gold badges180 silver badges289 bronze badges

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

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2014-05-25 14:05:15Z

1

There's no need to open a cursor and fetch it to determine if the hospital exists - just go ahead and delete it, then check SQL%ROWCOUNT to determine if any rows were actually deleted:

CREATE OR REPLACE PROCEDURE BorrarHospital (codihospital IN NUMBER)
IS
BEGIN
  DELETE FROM HOSPITAL WHERE HOSPITAL_CODI = codihospital ;

  IF SQL%ROWCOUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No hi ha cap hospital amb aquest codi');
  END IF;

  COMMIT;
END BorrarHospital;

Share and enjoy.

answered May 25, 2014 at 14:05

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

Delete an SQL row with PL/SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related