Home » SQL & PL/SQL » SQL & PL/SQL » invalid rowid (oracle 10g, windows 7)
invalid rowid [message #589603] Tue, 09 July 2013 02:31 Go to next message
gowthamsanj
Messages: 18
Registered: June 2013
Location: tirupur
Junior Member
I CREATE A COLUMN AS 'STATUS VARCHAR2(40)' IN EMP1 TABLE IN SCOTT USER

EMP1 TABLE IS A DUPLICATE TABLE OF EMP


CREATE OR REPLACE PACKAGE SAL_STATUS IS
PROCEDURE SAL_STATUS;
END SAL_STATUS;

CREATE OR REPLACE PACKAGE BODY SAL_STATUS IS
FUNCTION CHK_SAL(SL NUMBER) RETURN BOOLEAN IS
A_SAL NUMBER;
BEGIN
SELECT AVG(SAL) INTO A_SAL FROM EMP1;
RETURN (SL>A_SAL);
END CHK_SAL;
PROCEDURE LOAN_STATUS IS
CURSOR C1 IS SELECT EMPNO,SAL FROM EMP1 FOR UPDATE OF STATUS;
SALARY NUMBER;
ID NUMBER;
BEGIN
IF NOT C1%ISOPEN THEN
OPEN C1;
END IF;
LOOP
FETCH C1 INTO ID,SALARY;
IF CHK_SAL(SALARY) THEN
UPDATE EMP1 SET STATUS='ELIGIBLE FOR LOAN' WHERE CURRENT OF C1;
ELSE
UPDATE EMP1 SET STATUS='NOT ELIGIBLE FOR LOAN' WHERE CURRENT OF C1;
END IF;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
COMMIT;
END LOAN_STATUS;
END;
/

PACKAGE CREATED SUCCESSFULLY...


WHEN I EXECUTE THIS PACKAGE]
BEGIN
SAL_STATUS.LOAN_STATUS;
END;


IT THROW ERROR AS
BEGIN
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at "SCOTT.SAL_STATUS", line 21
ORA-06512: at line 2

HOW CAN I OVERCOME THIS ERROR...
THANK YOU...

[Updated on: Tue, 09 July 2013 02:37]

Report message to a moderator

Re: invalid rowid [message #589604 is a reply to message #589603] Tue, 09 July 2013 02:43 Go to previous messageGo to next message
Littlefoot
Messages: 19608
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you move EXIT statement right behind FETCH?
fetch c1 into id, salary;
exit when c1%notfound;

if chk_sal(salary) then ...
Re: invalid rowid [message #589607 is a reply to message #589604] Tue, 09 July 2013 02:50 Go to previous messageGo to next message
gowthamsanj
Messages: 18
Registered: June 2013
Location: tirupur
Junior Member
wait let me to check that
Re: invalid rowid [message #589609 is a reply to message #589607] Tue, 09 July 2013 02:53 Go to previous messageGo to next message
gowthamsanj
Messages: 18
Registered: June 2013
Location: tirupur
Junior Member
wow!! superb! littlefoot!! its perfectly working!! i need to know the reason why it is not working when the c1%notfound is come after the if condition
Re: invalid rowid [message #589611 is a reply to message #589603] Tue, 09 July 2013 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DO NOT POST IN UPPER RED SIZE 3 CHARACTERS.

It is very rude as you can see.

Michel Cadot wrote on Sat, 29 June 2013 07:30
...
So post the CREATE TABLE statement for your table so we can reproduce what you have.
Use SQL*Plus and copy and paste your session, the WHOLE session.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

And do NOT post in UPPER case.



Simply do that.

Regards
Michel
Re: invalid rowid [message #589614 is a reply to message #589603] Tue, 09 July 2013 03:09 Go to previous messageGo to next message
gowthamsanj
Messages: 18
Registered: June 2013
Location: tirupur
Junior Member
ok michel!! i wont do this again
Re: invalid rowid [message #589615 is a reply to message #589609] Tue, 09 July 2013 03:11 Go to previous messageGo to next message
Littlefoot
Messages: 19608
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
gowthamsanj
i need to know the reason why it is not working when the c1%notfound is come after the if condition

Because when there's more to fetch, you are still trying to update "current of" that nothing which is recognized as an invalid ROWID.
Re: invalid rowid [message #589620 is a reply to message #589615] Tue, 09 July 2013 03:20 Go to previous message
gowthamsanj
Messages: 18
Registered: June 2013
Location: tirupur
Junior Member
thank you littlefoot
Previous Topic: Find shortest path with visits
Next Topic: PlSql Procedure Parameter
Goto Forum:
  


Current Time: Wed Sep 17 15:37:08 CDT 2014

Total time taken to generate the page: 0.06258 seconds