Updating an oracle view dating af

The fields in a view are fields from one or more real tables in the database.

updating an oracle view-74

INSERT INTO vehicle_mfg_company_details VALUES (101,'Ford'); -- error INSERT INTO TABLE ( SELECT vw.vehiclelist FROM company_vehicles_view vw WHERE company_id = 101 ) VALUES (1, 'Eco Sport'); -- output SQL Error: ORA-25015: cannot perform DML on this nested table view column 25015.

00000 - "cannot perform DML on this nested table view column" *Cause: DML cannot be performed on a nested table view column except through an INSTEAD OF trigger *Action: Create an INSTEAD OF trigger over the nested table view column and then perform the DML.

Consequently, to prevent this, you might do this: One potential issue may arise when the subquery actually finds more than one matching row in tbl B.

If this happens, the UPDATE will terminate with an error ("subquery may return only one row").

For a view to be inherently updatable, the following conditions must be met: CREATE TABLE employee ( employee_no VARCHAR2(8), last_name VARCHAR2(25) NOT NULL, first_name VARCHAR2(10) NOT NULL, dept_code VARCHAR2(3) NOT NULL, active_flag VARCHAR2(1) DEFAULT 'Y', mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE permission_code ( pcode VARCHAR2(2), pcode_description VARCHAR2(40) NOT NULL, mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE user_role ( dept_code VARCHAR2(3), pcode VARCHAR2(2), access_level VARCHAR2(1) DEFAULT 'R', mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE user_permission ( employee_no VARCHAR2(8), pcode VARCHAR2(2), access_level VARCHAR2(1) DEFAULT 'R', mod_user_id VARCHAR2(30) DEFAULT USER, mod_user_date DATE DEFAULT SYSDATE); CREATE TABLE dept_code ( INSERT INTO employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('5001', 'Mark', 'Townsend', 'LCR', 'Y'); INSERT INTO employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('3996', 'Dacko', 'Carol', 'ESR', 'Y'); INSERT INTO employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('6842', 'Morgan', 'Daniel', 'ADM', 'Y'); INSERT INTO permission_code VALUES ('BO', 'BILLING OPTIONS', USER, SYSDATE); INSERT INTO permission_code VALUES ('CL', 'CLASS CODES', USER, SYSDATE); INSERT INTO permission_code VALUES ('CR', 'CREWS', USER, SYSDATE); INSERT INTO permission_code VALUES ('CT', 'CREW TYPES', USER, SYSDATE); INSERT INTO permission_code VALUES ('CU', 'CUSTOMER TYPES', USER, SYSDATE); INSERT INTO permission_code VALUES ('DH', 'WORKORDER DASH NUMBERS', USER, SYSDATE); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ADM', 'ADMINISTRATION'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('COO', 'COORDINATOR'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ESE', 'ELECTRICAL SERVICE'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ESR', 'ELECTRICAL SERVICE REP'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('ENG', 'ENGINEER'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('LCR', 'LINE CREW'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('MCR', 'METER CREW'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('NWE', 'NETWORK ENGINEER'); INSERT INTO dept_code (dept_code, dept_name) VALUES ('SKA', 'SKETCH ARTIST'); INSERT INTO user_role (dept_code, pcode, access_level) SELECT r.dept_code, p.pcode, 'R' FROM dept_code r, permission_code p; INSERT INTO user_permission (employee_no, pcode, access_level) SELECT e.employee_no, r.pcode, r.access_level FROM employee e, user_role r WHERE e.dept_code = r.dept_code; COMMIT; CREATE OR REPLACE VIEW role_permission_view AS SELECT r.dept_code, r.pcode, p.pcode_description, r.access_level FROM user_role r, permission_code p WHERE r.pcode = p.pcode; desc role_permission_view col data_type format a15 SELECT column_name, data_type, data_length FROM user_tab_cols WHERE table_name = 'ROLE_PERMISSION_VIEW'; col type format a30 SELECT column_name, , e.dept_code, r.pcode, r.access_level DEFACCLVL, u.access_level, p.pcode_description FROM employee e, user_role r, user_permission u, permission_code p WHERE e.dept_code = r.dept_code AND e.employee_no = u.employee_no AND r.pcode = u.pcode AND r.pcode = p.pcode ORDER BY 1,3; desc employee_permission_view SELECT column_name, ON role_permission_view FOR EACH ROW DECLARE x INTEGER; BEGIN SELECT COUNT(*) INTO x FROM permission_code WHERE pcode = : NEW.pcode; IF x = 0 THEN INSERT INTO permission_code (pcode, pcode_description, mod_user_id, mod_user_date) VALUES (: NEW.pcode, 'New Code', USER, SYSDATE); END IF; SELECT COUNT(*) INTO x FROM dept_code WHERE dept_code = : NEW.dept_code; IF x = 0 THEN INSERT INTO dept_code (dept_code, dept_name) VALUES (: NEW.dept_code, 'New Dept'); END IF; INSERT INTO user_role (dept_code, pcode, mod_user_id) VALUES (: NEW.dept_code, : NEW.pcode, 'Morgan'); INSERT INTO test (test) VALUES ('Z'); END ioft_insert_role_perm; / SELECT * FROM permission_code WHERE pcode = 'DM'; SELECT * FROM dept_code WHERE dept_code = 'DAN'; SELECT * FROM user_role WHERE dept_code = 'DAN'; SELECT * FROM test; ON role_permission_view FOR EACH ROW BEGIN UPDATE user_role SET access_level = : NEW.access_level, mod_user_id = USER, mod_user_date = SYSDATE WHERE dept_code = : OLD.dept_code AND permission_code = : OLD.permission_code; END ioft_role_perm; / SELECT trigger_name, trigger_type, action_type, description FROM user_triggers; SELECT * FROM employee_permission_view; UPDATE role_permission_view SET access_level = 'W' WHERE dept_code = 'SKA'; SELECT * FROM employee_permission_view; UPDATE employee_permission SET access_level = 'Z';/* what does it mean to delete LCR from employee_permission_view?

Does it mean delete the LCR department from the dept_code table?

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view.

The information displayed by this view is meaningful only for inherently updatable views.

works fine when I try to update all the records in tbl A, however, in this case I only have missing data which I have identified and populated in tbl B.

Tags: , ,