CODEWITHSUNDEEP
sqloracle-databasesqlplus
Randy
Randy

Reputation: 803

How to make a view not read only

I created a view that I make read only 

 create view EMP_VU AS
 select ename employee_name, deptno, empno
 from emp
 with READ ONLY CONSTRAINT EMP_VU_Read_ONLY;

How do i make it not read only?

I get this error when I try to insert information into the view so i am assuming thats my problem that is read only. 

 SQL> insert into EMP_VU (employee_n,deptno, empno)
  values (Stutte, 40, 8888);
  values (Stutte, 40, 8888)
          *
  ERROR at line 2:
  ORA-00984: column not allowed here

I made a change heres the new error I get 

  SQL> insert into EMP_VU (employee_name, deptno, empno)
  values ('Stuttle', '40', '8888');
  insert into EMP_VU (employee_name, deptno, empno)
  *
  ERROR at line 1:
  ORA-00001: unique constraint (CIS605.EMP_EMPNO_PK) violated

Heres the View

 SQL> select * from EMP_VU;

 EMPLOYEE_N     DEPTNO      EMPNO
 ---------- ---------- ----------
 KING               10       7839
 BLAKE              30       7698
 CLARK              10       7782
 JONES              20       7566
 MARTIN             30       7654
 ALLEN              30       7499
 TURNER             30       7844
 JAMES              30       7900
 WARD               30       7521
 FORD               20       7902
 SMITH              20       7369

 EMPLOYEE_N     DEPTNO      EMPNO
 ---------- ---------- ----------
 SCOTT              20       7788
 ADAMS              20       7876
 MILLER             10       7934

 14 rows selected.

Upvotes: 0

Views: 2208

Answers (1)

MatBailie
MatBailie

Reputation: 86716

Your error message is a breach of a primary key. You're inserting data with a primary key that already exists. From the name of the key, it's abotu the employee number.

In short, don't insert records with the same employee number as any existing records?


I've also noticed that in one query you don't have quotes aroudn your string for the employee name. And in another you have quotes around numeric values.

You need to identify the data types for each column, and only use quotes where they're needed. (Strings and Dates, but not numbers)

Upvotes: 2

Related Questions