Home » SQL & PL/SQL » SQL & PL/SQL » THE PL/SQL BLOCK
icon5.gif  THE PL/SQL BLOCK [message #205232] Fri, 24 November 2006 00:37 Go to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
THE SQL BLOCK

DECLARE
CURSOR EMP_CUR IS SELECT EMP_ID,EMP_NAME,EMP_PH FROM EMP_DET FOR UPDATE OF EMP_PH;

EMP_REC EMP_CUR%ROWTYPE;
BEGIN
OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO EMP_REC;
UPDATE EMP_DET SET EMP_PH=234 WHERE CURRENT OF EMP_CUR;
EXIT WHEN EMP_CUR%NOTFOUND;
END LOOP;
END;


WHAT IS THE ERROR IN THE ABOVE CODE. IM GETTING ERROR


CHECK IT OUT..

THANKS IN ADVANCE
Re: THE PL/SQL BLOCK [message #205235 is a reply to message #205232] Fri, 24 November 2006 00:46 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

 can you post the error messge.


regards,
Re: THE PL/SQL BLOCK [message #205242 is a reply to message #205235] Fri, 24 November 2006 00:55 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
hi this is the error message i got



DECLARE
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 10

line 1 is declare
and line 10 is the update query
Re: THE PL/SQL BLOCK [message #205245 is a reply to message #205242] Fri, 24 November 2006 00:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Move your EXIT WHEN above the UPDATE statement.

Ross Leishman
Re: THE PL/SQL BLOCK [message #205246 is a reply to message #205242] Fri, 24 November 2006 00:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Swap these two statements.
UPDATE EMP_DET SET EMP_PH=234 WHERE CURRENT OF EMP_CUR;
EXIT WHEN EMP_CUR%NOTFOUND;

By
Vamsi
Re: THE PL/SQL BLOCK [message #205255 is a reply to message #205246] Fri, 24 November 2006 01:18 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
ya thanks, i got the output.
wat was the significance in tat.


also is it possible for me to get the ph no from the user
each time for a row. if so how is it possible




plz help me out..
Re: THE PL/SQL BLOCK [message #205256 is a reply to message #205246] Fri, 24 November 2006 01:20 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You've also forgotten to CLOSE the cursor.

Significance? Is it a question?

What is a "ph no from the user"?

[Updated on: Fri, 24 November 2006 01:22]

Report message to a moderator

Re: THE PL/SQL BLOCK [message #205270 is a reply to message #205256] Fri, 24 November 2006 02:08 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
phone no from the user.... it means


update emp_Det set emp_ph=&phone where current of emp_cur;


how do i get the value of the phone for each row when thr cursor is compiled
Re: THE PL/SQL BLOCK [message #205282 is a reply to message #205270] Fri, 24 November 2006 02:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As I spent some time explaining to you yesterday, you will have to either:
1) Gather all the phone numbers up front and update them all in one go, or
2) Gather one phone number at a time from the front end and update one Emp_Det record at a time.

There is no way in Pl/Sql to interrupt the processing, return control to the front end to get more information, and then continue processing.
Previous Topic: Difference between IN and EXISTS
Next Topic: getting ORA-00900 invalid SQL statement in a Procedure
Goto Forum:
  


Current Time: Sat Dec 03 14:14:59 CST 2016

Total time taken to generate the page: 0.13099 seconds