Cursor for updating in oracle Shy chat tube
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".
The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
say if am fetching from the cursor treccy=USD and rate1=154.95 and rate2=98 where fxfrccy=b.treccy=USD Now update eur=b.treamt*rate/rate2.
All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.
When ISO syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.
By using the same mechanics, a SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.
A cursor can be viewed as a pointer to one row in a set of rows.See Chapter 7, "Performing SQL Operations with Native Dynamic SQL".