a. Can you delete data from a View.
b. If Yes, can you delete it if there are multiple tables
c. If No, can you delete if there is single source table
which is joining.
Answers were Sorted based on User's Feedback
Answer / haarika valasa
IF view is created based on only one base table we can
perform all DML operations.
If view is created based on multiple table using joins and
goup by functions etc..than we cannot perform directly DML
operations.
Using Instead of Trigger we can perform DML operations on
complex view.
Is This Answer Correct ? | 24 Yes | 0 No |
Answer / pankaj
you can remove the rows from the view if it does not containing any of the following.
1> group function
2> group by clause
3> distinct keyword
4> rownum keyword
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / suman
Yes we can insert records if view having one to one table. but we cannot create insert if we have multiple table join query
create or replace view v_emp
as select * from emp;
insert into v_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(1111,'suman','CLERK',1111,SYSDATE,11,1,20);
create or replace view v_emp
as select empno,ENAME,job,deptno from emp;
insert into v_emp(empno,ename,job,deptno)
values(1112,'suman','CLERK',20);
SELECT * FROM V_EMP WHERE EMPNO=1111;
SELECT * FROM V_EMP WHERE EMPNO=1112;
create or replace view v_emp_dept
as select empno,e.deptno,dname from emp e, dept b where e.deptno=b.deptno;
insert into v_emp_dept(empno,deptno,dname)
values(1112,'suman','CLERK');
select * from v_emp_dept where empno=1111;
01776. 00000 - "cannot modify more than one base table through a join view"
*Cause: Columns belonging to more than one underlying table were either
inserted into or updated.
*Action: Phrase the statement as two or more separate statements.
Is This Answer Correct ? | 0 Yes | 0 No |
Compare sql & pl/sql
What are the steps you take to tune(performance tuning) the code in plsql?
4 Answers Cap Gemini, Infosys, TCS,
What normalization means?
How is a PL/SQL code compiled?
what is sql profiler
what is auto increment? : Sql dba
Explain how you can copy a file to file content and file to pl/sql table in advance pl/sql?
What is raw datatype in sql?
Is there any problem if we use commit repeatedly after each dml statement in a plsq procedure ? (eg. there are 10 update stmt and using 10 commit stmt after each update stmt)
Which one is better sql or oracle?
What is the difference between the implicit and explicit cursors?
How to Execute a Package in PL/SQL.?