Updating table using cursor in oracle
Whenever you alter the value of a column referenced by the -- All the employees except the manager of -- department (WORKDEPT) 'E21' have been temporarily reassigned.
-- Indicate this by changing their job (JOB) to NULL and their pay -- (SALARY, BONUS, COMM) values to zero in the EMPLOYEE table.
This was a totally new paradigm for the application, and one that saved the entire mission-critical application.
The in-place updates would not have worked with the terrabytes of data that we have in our database.
When i run the below SQL i am getting the following errordeclarecursor c1 is SELECT emp_num,retention_id FROM tarner.nexros b;beginfor i in c1 loop UPDATE en.agt_name a SET a.emp_num =b.emp_numwhere a.userid = b.retention_id;commit;end loop;end; Error: ORA-06550: line 3, column 13: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 2, column 14: PL/SQL: SQL Statement ignored ORA-06550: line 9, column 11: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 9, column 1: Please help me out!!
Thanks,reacha Sorry this was the error that i am getting ORA-06550: line 2, column 29: PL/SQL: ORA-00904: "RETENTION_ID": invalid identifier ORA-06550: line 2, column 14: PL/SQL: SQL Statement ignored ORA-06550: line 11, column 18: PL/SQL: ORA-00904: "B"."RETENTION_ID": invalid identifier ORA-06550: line 9, column 1: in the update statement inside the for loop can't find the table b (alias)the update statement should read like this UPDATE en.agt_name a SET a.emp_num = i.emp_numwhere a.userid = i.retention_id; Ensure retention_id column is exist on the table, as per your DESC result retention_id is does not exist. In your code, you must refer to tarner.nexros by the name you declared for the cursor in your for loop, "i".
Specifying DEFAULT for the update value sets the value of the column to the default defined for that table.
There is a chance that INSERT /* append */ select Tom, Recently I had conducted a interview in which one the dba mentioned that they had a table that might conatin 10 million records or might be 1 million.Your status said that you had a large backlog, so I decided not to wait for your response and tried myself using dbms_job.submit() calls.At first, it did not work (job_queue_processes was 0), but after I set it to 12 it started working. rows table and rebuild 5 non-partitioned indexes with 'compute statistics parallel nologging' clause.We institued the Insert into a dummy table append with nologging, and were able to complete the "update" in under 30 minutes.With nologging, if the system aborts, you simply re-run the 'update' again, as you have the original data in the main table.A searched update statement depends on the table being updated, all of its conglomerates (units of storage such as heaps or indexes), all of its constraints, and any other table named in the WHERE clause or SET expressions.A CREATE or DROP INDEX statement or an ALTER TABLE statement for the target table of a prepared searched update statement invalidates the prepared searched update statement. One of our apps updates a table of several hundred million records. For loop approach for the update was calculated to take 53.7 years to complete! I dont want to do in one stroke as I may end up in Rollback segment issue(s). This is absolutely a viable approach, and one we have used repeatedly.If so -- sure, cause we could just drop partitions (fast) instead of deleting the data. We also have an absolutely incredible stored procedure that rebuilds all of our indexes concurrently after the load, using the Oracle job scheduler as the mechanism of allowing separate threads in pl/sql.I work with John Bittner, one of the previous reviewers. This process was introduced to our environment by a master tuner and personal friend, Larry Elkins.