Tutorial: Changing Views with the Edit View and Rename Tools

To change the query in a view, use either the SQL Developer tool Edit View or the DDL statement CREATE VIEW with the OR REPLACE clause. To change the name of a view, use either the Rename tool or the RENAME statement.

This tutorial shows how to use the Edit View tool to add the employees of the Marketing department to the SALESFORCE view (created in "Tutorial: Creating a View with the Create View Tool") and the and Rename tool to change its name to SALES_MARKETING. The equivalent DDL statements are:

CREATE OR REPLACE VIEW SALESFORCE AS
  SELECT FIRST_NAME || ' ' || LAST_NAME "Name",
  SALARY*12 "Annual Salary"
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 80 OR DEPARTMENT_ID = 20;

RENAME SALESFORCE to SALES_MARKETING;

To change the SALESFORCE view using the Edit View and Rename tools:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Views.

   A list of views appears.

3. Right-click SALESFORCE.

   A list of choices appears.

4. Select Edit.

   The Edit View window opens. Its SQL Query field contains the query to be changed.

5. Add this to the end of the query:

   OR DEPARTMENT_ID = 20
   

6. Click Test Syntax.

   A message appears in the field SQL Parse Results.

7. If the message is not "No errors found in SQL", return to step 5 and correct the syntax errors in the query.

8. Click OK.

   The employees of the Marketing department are added to the SALESFORCE view. Now change the name of the view to SALES_MARKETING.

9. Right-click SALESFORCE.

   A list of choices appears.

10. Select Rename.

    The Rename window opens. It has a New View Name field.

11. In the New View Name field, type SALES_MARKETING.

12. Click Apply.

    The Confirmation window opens.

13. Click OK.