CODEWITHSUNDEEP
databaseoracle-databaseplsqloracle11g
Random guy
Random guy

Reputation: 923

Error in compilation in procedure in oracle

I am new to oracle.So,I am creating a procedure which has all features of insert,update,delete and select method.So when I hit the compilation button then the result is:

Warning: compiled but with compilation errors

But i am unable to see where is the mistake I did.My table is:

enter image description here

Now the procedure I tried is:

CREATE OR REPLACE PROCEDURE OT.ALL_CRUD_PERSON(DATA1 VARCHAR2,
ID PERSON.ID%TYPE,
NAME PERSON.NAME%TYPE,
AGE  PERSON.AGE%TYPE,
R_C OUT SYS_REFCURSOR)
IS 

CURSOR CUR IS
SELECT MAX(ID) FROM OT.PERSON;
BEGIN
IF DATA1='INSERT' THEN
INSERT INTO OT.PERSON(ID,NAME,AGE) VALUES (CUR,NAME,AGE);
END IF;


IF DATA1='UPDATE' THEN
UPDATE OT.PERSON SET NAME=NAME,AGE=AGE WHERE ID=ID;
END IF;

IF DATA1='DELETE' THEN
DELETE FROM OT.PERSON WHERE ID=ID;
END IF;

IF DATA1='SELECT' THEN
OPEN R_C FOR
SELECT * FROM OT.PERSON;
END IF;

END;
/

Also,I want to ask is it the good process to put all the functionality in same procedure?

Upvotes: 0

Views: 503

Answers (1)

thatjeffsmith
thatjeffsmith

Reputation: 22467

Problem 1

INSERT INTO PERSON(ID,NAME,AGE) VALUES (CUR,NAME,AGE);

This will result in a

Error(19,41): PL/SQL: ORA-00904: "CUR": invalid identifier

Perhaps should be

INSERT INTO PERSON(ID,NAME,AGE) VALUES (ID,NAME,AGE);

That at least will compile without errors.

Looks like you're using Toad...I know if you use SQL Developer it will automatically show you the Errors whenever you compile PL/SQL with compiler feedback.

Also, ask yourself this question - do you want due to a bug, for a call to do an UPDATE to accidentally do a DELETE?

I would suggest you break these operations out to individual functions/procedures - and tie them together using a PACKAGE.

Upvotes: 1

Related Questions