Home » SQL & PL/SQL » SQL & PL/SQL » procedure executed successfully but no data stored in table (merged)
procedure executed successfully but no data stored in table (merged) [message #403544] Sat, 16 May 2009 16:07 Go to next message
shaunak.7@gmail.com
Messages: 5
Registered: May 2009
Junior Member
sir i have got a similar error in my procedure. i have created a procedure which calculates the average of employee marks and updates the value in a table. however if i run the procedure in command prompt as well as in toad it shows that the procedure has been executed successfully but when i check the table no data has been input. i am pasting the code of my procedure below.


CREATE OR REPLACE PROCEDURE CAL_PER(TRAIN_ID IN ATTENDANCE.TRAINING_ID%TYPE,EMPLOYEE_ID IN ATTENDANCE.EMPLOYEE_ID%TYPE) IS

CURSOR CS1 IS SELECT QUESTION FROM VT.ATTENDANCE WHERE VT.ATTENDANCE.TRAINING_ID = TRAIN_ID AND VT.ATTENDANCE.EMPLOYEE_ATTENDENCE = 'Y';
CURSOR CS2 IS SELECT EMPLOYEE_MARKS FROM VT.ATTENDANCE WHERE VT.ATTENDANCE.TRAINING_ID =TRAIN_ID AND VT.ATTENDANCE.EMPLOYEE_ATTENDENCE = 'Y';
QUESTION NUMBER;
MARKS NUMBER;

BEGIN
OPEN CS1;
FETCH CS1 INTO QUESTION;
OPEN CS2;
FETCH CS2 INTO QUESTION;

IF MARKS = 0 THEN
UPDATE VT.ATTENDANCE SET EMP_PER = 0 WHERE VT.ATTENDANCE.TRAINING_ID = TRAIN_ID AND VT.ATTENDANCE.EMPLOYEE_ID = EMPLOYEE_ID AND VT.ATTENDANCE.EMPLOYEE_ATTENDENCE = 'Y';
END IF;
IF MARKS != 0 THEN
UPDATE VT.ATTENDANCE SET EMP_PER = (100 * EMPLOYEE_MARKS/QUESTION), VT.ATTENDANCE.EMPLOYEE_MARKS = EMPLOYEE_MARKS WHERE VT.ATTENDANCE.TRAINING_ID = TRAIN_ID AND VT.ATTENDANCE.EMPLOYEE_ID = EMPLOYEE_ID AND VT.ATTENDANCE.EMPLOYEE_ATTENDENCE = 'Y';
END IF;

END CAL_PER;
/

kindly help me asap. urgent requirement. thanking you in advance
procedure executed successfully but no data stored in table [message #403545 is a reply to message #403544] Sat, 16 May 2009 16:08 Go to previous messageGo to next message
shaunak.7@gmail.com
Messages: 5
Registered: May 2009
Junior Member
i have created a procedure which calculates the average of employee marks and updates the value in a table. however if i run the procedure in command prompt as well as in toad it shows that the procedure has been executed successfully but when i check the table no data has been input. i am pasting the code of my procedure below.


CREATE OR REPLACE PROCEDURE CAL_PER(TRAIN_ID IN ATTENDANCE.TRAINING_ID%TYPE,EMPLOYEE_ID IN ATTENDANCE.EMPLOYEE_ID%TYPE) IS

CURSOR CS1 IS SELECT QUESTION FROM VT.ATTENDANCE WHERE VT.ATTENDANCE.TRAINING_ID = TRAIN_ID AND VT.ATTENDANCE.EMPLOYEE_ATTENDENCE = 'Y';
CURSOR CS2 IS SELECT EMPLOYEE_MARKS FROM VT.ATTENDANCE WHERE VT.ATTENDANCE.TRAINING_ID =TRAIN_ID AND VT.ATTENDANCE.EMPLOYEE_ATTENDENCE = 'Y';
QUESTION NUMBER;
MARKS NUMBER;

BEGIN
OPEN CS1;
FETCH CS1 INTO QUESTION;
OPEN CS2;
FETCH CS2 INTO QUESTION;

IF MARKS = 0 THEN
UPDATE VT.ATTENDANCE SET EMP_PER = 0 WHERE VT.ATTENDANCE.TRAINING_ID = TRAIN_ID AND VT.ATTENDANCE.EMPLOYEE_ID = EMPLOYEE_ID AND VT.ATTENDANCE.EMPLOYEE_ATTENDENCE = 'Y';
END IF;
IF MARKS != 0 THEN
UPDATE VT.ATTENDANCE SET EMP_PER = (100 * EMPLOYEE_MARKS/QUESTION), VT.ATTENDANCE.EMPLOYEE_MARKS = EMPLOYEE_MARKS WHERE VT.ATTENDANCE.TRAINING_ID = TRAIN_ID AND VT.ATTENDANCE.EMPLOYEE_ID = EMPLOYEE_ID AND VT.ATTENDANCE.EMPLOYEE_ATTENDENCE = 'Y';
END IF;

END CAL_PER;
/
Re: procedure executed successfully but no data stored in table [message #403546 is a reply to message #403545] Sat, 16 May 2009 16:09 Go to previous messageGo to next message
shaunak.7@gmail.com
Messages: 5
Registered: May 2009
Junior Member
kindly help me. urgent requirement.
Re: toad error [message #403547 is a reply to message #403544] Sat, 16 May 2009 16:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE PROCEDURE Cal_per
     (train_id     IN attendance.training_id%TYPE,
      employee_id  IN attendance.employee_id%TYPE)
IS
  CURSOR cs1 IS
    SELECT question
    FROM   vt.attendance
    WHERE  vt.attendance.training_id = train_id
           AND vt.attendance.employee_attendence = 'Y';
  CURSOR cs2 IS
    SELECT employee_marks
    FROM   vt.attendance
    WHERE  vt.attendance.training_id = train_id
           AND vt.attendance.employee_attendence = 'Y';
  question  NUMBER;
  marks     NUMBER;
BEGIN
  OPEN cs1;
  
  FETCH cs1 INTO question;
  
  OPEN cs2;
  
  FETCH cs2 INTO question;
  
  IF marks = 0 THEN
    UPDATE vt.attendance
    SET    emp_per = 0
    WHERE  vt.attendance.training_id = train_id
           AND vt.attendance.employee_id = employee_id
           AND vt.attendance.employee_attendence = 'Y';
  END IF;
  
  IF marks != 0 THEN
    UPDATE vt.attendance
    SET    emp_per = (100 * employee_marks / question),
           vt.attendance.employee_marks = employee_marks
    WHERE  vt.attendance.training_id = train_id
           AND vt.attendance.employee_id = employee_id
           AND vt.attendance.employee_attendence = 'Y';
  END IF;
END cal_per;
/ 


It might help to include COMMIT as the last statement.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Re: toad error [message #403548 is a reply to message #403547] Sat, 16 May 2009 16:25 Go to previous messageGo to next message
shaunak.7@gmail.com
Messages: 5
Registered: May 2009
Junior Member
THANKS A LOT SIR. IT WORKED!!!
Re: procedure executed successfully but no data stored in table [message #403565 is a reply to message #403546] Sun, 17 May 2009 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question.
Don't hijack others topic.
Format your post.
Post your Oracle version (4 decimals).
Don't post in UPPER case.
Urgent? nothing is urgent in forum, don't use such word.

In short, read and follow OraFAQ Forum Guide

Regards
Michel

[Updated on: Sun, 17 May 2009 05:38]

Report message to a moderator

Re: procedure executed successfully but no data stored in table [message #403588 is a reply to message #403565] Sun, 17 May 2009 05:28 Go to previous messageGo to next message
shaunak.7@gmail.com
Messages: 5
Registered: May 2009
Junior Member
am sorry sir if i have offended you in any case...its just am new to this forum. forgive me for any inconvenience
Re: procedure executed successfully but no data stored in table [message #404018 is a reply to message #403545] Wed, 20 May 2009 01:54 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Is this a PL/SQL code? Shocked

1>Code ends without worrying about closing the cursors.
2>Both the cursors uses same variable to fetch data into.
OPEN CS2;
FETCH CS2 INTO QUESTION;

3>Here it should be MARKS instead of QUESTION.
4>Uninitialized MARKS variable is used in condition.
5>Instead of two IF, IF-ELSE should be used.

regards,
Delna
Previous Topic: Difference between array and varray
Next Topic: Before Insert Trigger
Goto Forum:
  


Current Time: Fri Dec 09 02:24:00 CST 2016

Total time taken to generate the page: 0.10633 seconds