Updating a view in plsql

This is a complete book on PL/SQL with everything you need to know to write efficient and complex PL/SQL code.

In this table when data inserted, profit calculated with a stored procedure.

SQL SET SERVEROUTPUT ON BEGIN UPDATE employees SET salary = 10000 WHERE employee_id = 9999; IF SQL%ROWCOUNT = 0 THEN -- Manually raise the NO_DATA_FOUND exception.

RAISE NO_DATA_FOUND; END IF; DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised'); END; / NO_DATA_FOUND Raised PL/SQL procedure successfully completed.

--DROP TABLE employees PURGE; --DROP TABLE departments PURGE; CREATE TABLE departments ( department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY, department_name VARCHAR2(14), location VARCHAR2(13) ); INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO departments VALUES (20,'RESEARCH','DALLAS'); INSERT INTO departments VALUES (30,'SALES','CHICAGO'); INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON'); COMMIT; CREATE TABLE employees ( employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY, employee_name VARCHAR2(10), job VARCHAR2(9), manager_id NUMBER(4), hiredate DATE, salary NUMBER(7,2), commission NUMBER(7,2), department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id) ); INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('','dd-mm-yyyy'),800, NULL,20); INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975, NULL,20); INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850, NULL,30); INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450, NULL,10); INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000, NULL,20); INSERT INTO employees VALUES (7839,'KING','PRESIDENT', NULL,to_date('','dd-mm-yyyy'),5000, NULL,10); INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100, NULL,20); INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950, NULL,30); INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000, NULL,20); INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300, NULL,10); COMMIT; These tables are a variant of the EMP and DEPT tables from the SCOTT schema.

You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema.

In this scenario, the cursor result set can be limited using the traditional When we associate a SELECT statement with more than one table joined together to a cursor with a FOR UPDATE clause, we end up locking all the tables in the FROM clause of the SELECT statement, where we just need to lock a single table for our purpose.

The FOR UPDATE OF clause helps us in locking up the intended table rather all available tables.

updating a view in plsql-57updating a view in plsql-38updating a view in plsql-2

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.

In the following example, we create a simple view on the EMPLOYEES table, then update via it.

SET SERVEROUTPUT ON BEGIN UPDATE employees SET salary = 10000 WHERE employee_id = 9999; DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised'); END; / NO_DATA_FOUND Not Raised PL/SQL procedure successfully completed.

SALES is mutating, trigger/function may not see it ORA-06512: at "SALES. UPDATE_PROFIT_TRIGGER", line 6 ORA-04088: error during execution of trigger 'SALES.

And yes the main table also reflects the changed value for LOC.

Leave a Reply

  1. on line dating men seeking women 10-Apr-2020 16:14

    Their eyes will light up when you enter their live room and that’s truly an exciting thing to experience.

  2. 2016 online dating lovers site it 26-Sep-2020 12:41

    There are several good dating sites for people who reside in Ottawa. People can easily find local dating sites by searching through websites such as Google, Bing, and Yahoo.

  3. polyamoury dating 06-Oct-2020 06:41

    This is exactly what you need – a girl who will be there by your side and making sure you have the time of your life.

  4. lead uranium dating 25-Jun-2020 18:47

    Shagle provides a free online chat app which will allow you to talk to strangers around the world instantly.