Oracle PL/SQL/View/Alter View

Alter view to recompile

   <source lang="sql">
 

SQL> SQL> SQL> create table t(

 2      id number,
 3      data varchar2(200) );

Table created. SQL> SQL> SQL> create or replace view view_t as

 2      select id view_id, data view_data
 3        from t;

View created. SQL> SQL> alter view view_t compile; View altered. SQL> SQL> SQL> drop table t; Table dropped.

 </source>
   
  

The OR REPLACE clause is used to modify a view; basically, you are re-creating the view.

   <source lang="sql">
  

SQL> SQL> SQL> CREATE TABLE EMP(

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      HIREDATE DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

Table created.

SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> SQL> SQL> CREATE OR REPLACE VIEW TOP_EMP

 2    (emp_id, ename, SALARY) AS
 3    SELECT empno, ename, sal
 4    FROM   emp
 5    WHERE  sal > 2999;

View created. SQL> SQL> SQL> drop table emp; Table dropped.

 </source>