Home » SQL & PL/SQL » SQL & PL/SQL » Problem with updating
Problem with updating [message #263924] Fri, 31 August 2007 05:17 Go to next message
jerabekm
Messages: 5
Registered: August 2005
Location: Czech Republic
Junior Member
Hi,
I have "enigma" with updating.
When I run next code, all values of column "SALARY" of table "EMPLOYEE" are updated (I disable CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0)). OK - that is what I need.
DECLARE      
      TYPE EmployeeCurTyp IS REF CURSOR;
      employee_id_variable  HR.EMPLOYEES.EMPLOYEE_ID%type;
      salary_variable       HR.EMPLOYEES.SALARY%type;
     
      new_salary             HR.EMPLOYEES.SALARY%type;
     
      cursor_employee 	EmployeeCurTyp;     
      query_employee	VARCHAR2(100); 
      row_count  NUMBER :=0;
BEGIN          
      DBMS_OUTPUT.PUT_LINE('START - '|| TO_CHAR(SYSTIMESTAMP,'DD.MM.YYYY HH24:MI:SS'));
  
      query_Employee := 'SELECT employee_id, salary FROM hr.employees';
      OPEN cursor_Employee FOR query_employee;   
      LOOP     
	 BEGIN
	    FETCH cursor_employee INTO employee_id_variable,salary_variable;
	    EXIT WHEN cursor_employee%NOTFOUND;      
            
            row_count := row_count+1;
            
            IF (salary_variable IS NULL) THEN
                new_salary := 0;
            ELSE
                new_salary := -1*salary_variable;
            END IF;
            
            UPDATE hr.employees SET salary = new_salary
                                  WHERE employee_id = employee_id_variable;
            DBMS_OUTPUT.PUT_LINE('Updated: ' || TO_CHAR(SQL%ROWCOUNT) ||'; new salary: '|| new_salary);
            
	 EXCEPTION       
	    WHEN OTHERS THEN       
	       DBMS_OUTPUT.PUT_LINE('EXCEPTION - code:'|| SQLCODE || '; message:' || SUBSTR(SQLERRM, 1 , 64));
         END;       
      END LOOP;
      CLOSE cursor_employee;
      
      COMMIT;
      
      DBMS_OUTPUT.PUT_LINE('END - '|| TO_CHAR(SYSTIMESTAMP,'DD.MM.YYYY HH24:MI:SS')||' - '|| row_count);
EXCEPTION       
    WHEN OTHERS THEN       
          DBMS_OUTPUT.PUT_LINE('EXCEPTION - code:'|| SQLCODE || '; message:' || SUBSTR(SQLERRM, 1 , 64));
END;


BUT when I run next code (same code - only different table), no value is updated.

DECLARE      
      TYPE SubjectCurTyp IS REF CURSOR;
      subject_id_variable          SUBJECTS.SUBJECT_ID%type;
      reason_to_delete_variable    SUBJECTS.REASON_TO_DELETE%type;
     
      why_delete_variable          SUBJECTS.REASON_TO_DELETE%type;
     
      cursor_subject 	SubjectCurTyp;     
      query_subject	VARCHAR2(100); 
      row_count  NUMBER :=0;
BEGIN          
      DBMS_OUTPUT.PUT_LINE('START - '|| TO_CHAR(SYSTIMESTAMP,'DD.MM.YYYY HH24:MI:SS'));
  
      query_subject := 'SELECT subject_id, reason_to_delete FROM subjects';
      OPEN cursor_subject FOR query_subject;   
      LOOP     
	 BEGIN 
	    FETCH cursor_subject INTO subject_id_variable,reason_to_delete_variable;
	    EXIT WHEN cursor_subject%NOTFOUND;      
            
            row_count := row_count+1;
            
            IF (reason_to_delete_variable IS NULL) THEN
                why_delete_variable := 0;
            ELSE
                why_delete_variable := -1*reason_to_delete_variable;
            END IF;
            
            UPDATE subjects SET reason_to_delete = reason_to_delete_variable, 
                                        date_of_change = SYSTIMESTAMP 
                                  WHERE subject_id = subject_id_variable;
            DBMS_OUTPUT.PUT_LINE('Updated: ' || TO_CHAR(SQL%ROWCOUNT) ||'; why delete: '|| why_delete_variable);
            
	 EXCEPTION       
	    WHEN OTHERS THEN       
	       DBMS_OUTPUT.PUT_LINE('EXCEPTION - code:'|| SQLCODE || '; message:' || SUBSTR(SQLERRM, 1 , 64));
         END;       
      END LOOP;
      CLOSE cursor_subject;
      
      COMMIT;
      
      DBMS_OUTPUT.PUT_LINE('END - '|| TO_CHAR(SYSTIMESTAMP,'DD.MM.YYYY HH24:MI:SS')||' - '|| row_count);
EXCEPTION       
    WHEN OTHERS THEN       
          DBMS_OUTPUT.PUT_LINE('EXCEPTION - code:'|| SQLCODE || '; message:' || SUBSTR(SQLERRM, 1 , 64));
END;


Please, can anybody tell me WHERE IS PROBLEM?

DBMS_OUTPUT.PUT_LINE('Updated: ' || TO_CHAR(SQL%ROWCOUNT) ||'; why delete: '|| why_delete_variable);
show me that every value is updated, but after ending code NO value is changed....
Script with code of "CREATE TABLE SUBJECTS.... " and some INSERT statements is in attachment.

Thank for your help.

Michal

(Please don't ask "why you use REF CURSOR?" and so on. This is only "sample".)
  • Attachment: SUBJECTS.sql
    (Size: 8.22KB, Downloaded 167 times)
Re: Problem with updating [message #264087 is a reply to message #263924] Fri, 31 August 2007 13:35 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
First of all thank you very much for provding the insert script.
IF (reason_to_delete_variable IS NULL) THEN
   why_delete_variable := 0;
ELSE
   why_delete_variable := -1*reason_to_delete_variable;
END IF;
            
  UPDATE subjects 
      SET reason_to_delete = reason_to_delete_variable, 
          date_of_change = SYSTIMESTAMP 
  WHERE subject_id = subject_id_variable;

From the above update statement, date_of_change column will be updated with the current system timestamp but the reason_to_delete will not be updated, in the sense, you set it with the same value you have retrieved from the table. I hope you are complaining why reason_to_delete column is not getting updated in your table. Otherwise could you be please be little more clear like which columns are getting updated correctly.

Regards

Raj
Re: Problem with updating [message #264088 is a reply to message #263924] Fri, 31 August 2007 13:36 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Quote:
UPDATE subjects SET reason_to_delete = reason_to_delete_variable,
date_of_change = SYSTIMESTAMP
WHERE subject_id = subject_id_variable;

Shouldn't the variable used in the UPDATE be WHY_DELETE_VARIABLE?
Previous Topic: oracle sql short
Next Topic: output blob to HTML page
Goto Forum:
  


Current Time: Tue Dec 06 08:14:11 CST 2016

Total time taken to generate the page: 0.05667 seconds