Updating a view in plsql
24-Jan-2020 15:19
Once we open a cursor having a FOR UPDATE clause, all the rows returned by the SELECT statement are locked for our changes until a commit or a rollback is placed to release the lock.
After a TCL operation is performed, the cursor pointer gets reset and the cursor will be no longer accessible, thus results in an error when fetched further as shown below.
In the below example, the cursor CUR is associated with a SELECT statement having the tables EMPLOYEES and joined in its FROM clause. EMPLOYEE_ID clause in the below snippet locks up only rows of the EMPLOYEES table, thus making the DEPARTMENTS table available for modification to the other session users. For this, the cursor CUR is associated with a SELECT statement joining the EMPLOYEES and the DEPARTMENTS tables with a FOR UPDATE OF clause on the employee ID.
When we try to update the salary column of the EMPLOYEES table using the WHERE CURRENT OF clause, the block fails with an ORA-01410: invalid ROWID error as there is no way to specify the This scenario can be manhandled by fetching the rowid of the intended table in the cursor associated SELECT statement and using it in the WHERE clause of the DELETE or the UPDATE statement instead of the WHERE CURRENT OF clause as shown in the below listing. For complete notes on programming in PL/SQL, we recommend the book Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.
Hence updating a view also results in update of main table.
In this table when data inserted, profit calculated with a stored procedure.
You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.
All Data Manipulation Language (DML) changes are done as part of a transaction.
Hello, so far in my OAF programming, I have always selected one particular row from a table and saved from it.
1840 1929 dating old photograph
Now I have a requirement where in my table, fields from more than one row shall have to be updated.If you decide you don't want to keep some uncommitted changes, you can throw them away using using the It's possible to update the base table associated with a view.
