Home » SQL & PL/SQL » SQL & PL/SQL » DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE
DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210587] Thu, 21 December 2006 07:32 Go to next message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
HI!!I AM WRITING A CURSOR WHEREIN I HAVE TO ACCEPT AND EMPLOYEE NUMBER(ENO) FROM USER AND A CHOICE U=UPDATE ,D=DELETE
UPDATE GETS EXECUTED PROPERLY FOR GIVEN ENO DELETE ALL EXECUTES BUT IN EMP TABLE THE RECORD NOT DELETED:



DECLARE
CURSOR UPD_DEL
IS SELECT * FROM EMP WHERE EMPNO=&ENO FOR UPDATE;
EMP_REC UPD_DEL%ROWTYPE;
ENO EMP.EMPNO%TYPE;
NAME EMP.ENAME%TYPE;
CHOICE CHAR(1);
BEGIN
OPEN UPD_DEL;
LOOP
FETCH UPD_DEL INTO EMP_REC;
EXIT WHEN UPD_DEL%NOTFOUND;
CHOICE:='&CHOICE';
IF CHOICE='U'THEN
UPDATE EMP SET ENAME='&NAME' WHERE CURRENT OF UPD_DEL;
ELSE
IF CHOICE='D' THEN
DELETE EMP WHERE EMP.EMPNO=ENO;
DBMS_OUTPUT.PUT_LINE('RECORD DELETED');
END IF;
END IF;
END LOOP;
CLOSE UPD_DEL;
END;
/

Enter value for eno: 7566
old 3: IS SELECT * FROM EMP WHERE EMPNO=&ENO FOR UPDATE;
new 3: IS SELECT * FROM EMP WHERE EMPNO=7566 FOR UPDATE;
Enter value for choice: D
old 13: CHOICE:='&CHOICE';
new 13: CHOICE:='D';
Enter value for name: SSD
old 15: UPDATE EMP SET ENAME='&NAME' WHERE CURRENT OF UPD_DEL;
new 15: UPDATE EMP SET ENAME='SSD' WHERE CURRENT OF UPD_DEL;
RECORD DELETED

PL/SQL procedure successfully completed.
Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210589 is a reply to message #210587] Thu, 21 December 2006 07:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You don't initialize eno, but you use it in your delete statement:
"delete emp where empno = eno;"

Please turn caps-lock off!

[Updated on: Thu, 21 December 2006 07:52]

Report message to a moderator

Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210708 is a reply to message #210589] Thu, 21 December 2006 22:42 Go to previous messageGo to next message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
i am aceepting it from user see the line
IS SELECT * FROM EMP WHERE EMPNO=&ENO FOR UPDATE
so in the query i am MAKing it equal to accepted value.
Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210709 is a reply to message #210708] Thu, 21 December 2006 22:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But you also defined a local variable called eno.
This variable never gets a value, but it is THIS variable you use in your delete statement.
Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210713 is a reply to message #210709] Fri, 22 December 2006 00:05 Go to previous messageGo to next message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
SO NOW EMPNO HAS MY USER INPUT VALUE THEREFORE I CANT USE ENO IN UPDATE .
THEN HOW SHUD I FORM QUERY ON UPDATE
Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210721 is a reply to message #210713] Fri, 22 December 2006 00:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hit Caps-Lock once again please.
Typing in capitals is regarded as shouting and is seen as impolite.

Why don't you use 'where current of' in your delete statement?
Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210752 is a reply to message #210721] Fri, 22 December 2006 03:40 Go to previous messageGo to next message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
SORRY!!BUT I AM USED TO CAPSLOCK!!!!PLZ DONT MIND!!I WILL TRY THAT
Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210756 is a reply to message #210752] Fri, 22 December 2006 03:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, we're used to people not using Caps Lock, so aseeing as you're here to ask us to help you, I think a bit of compromise on your part is called for.
Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210760 is a reply to message #210756] Fri, 22 December 2006 03:58 Go to previous messageGo to next message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
ok will not write in caps!!!never thought would be such a issue!!!sorry !!will follow the rules from now onwards
Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210761 is a reply to message #210721] Fri, 22 December 2006 03:59 Go to previous messageGo to next message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
current of dosent work
Re: DELETE SUCCESFUL BUT DOESNT GET IMPLEMENTED IN TABLE [message #210780 is a reply to message #210761] Fri, 22 December 2006 05:30 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Can you please post your script once again using code tags?
I mean new code (with where current of)

By
Vamsi
Previous Topic: How to view List of Files under a Directory path
Next Topic: ABOUT ROWNUM
Goto Forum:
  


Current Time: Fri Dec 09 05:42:48 CST 2016

Total time taken to generate the page: 0.07161 seconds