Previous chapters described the most frequently used database objects: tables and their columns. This chapter examines three other database objects that you can use in Personal Oracle7:
If you're serious about building an application based on an Oracle database, you need to understand how these objects work and when you should use them.
A view is a query of one or more tables that provides another way of presenting information. A view does not actually contain or store its own data; in fact, you can think of a view as a virtual table. The only storage that a view actually requires is the SELECT statement that defines it.
You can use a view to perform the following tasks:
The syntax for creating a view is really quite simple. The syntax to define a view is
CREATE VIEW view-name (column1,...,columnN) AS select-statement
where view-name is the name of the view (subject to the same requirements as other Oracle object names); column1 through columnN are the column names of the view that correspond to the columns referenced in select-statement; and select-statement is a valid Oracle SELECT statement.
The following simple view is based on a single table. Several users of your Frayed Wires application have requested a very simple way to retrieve the depreciated amount for various products. Even though you've shown them how to construct a simple query, they want you to streamline the process. To please the users, you define the following view:
SQL> create view Product_Depreciation 2 (Product_ID, Manufacturer_ID, Depreciated_Amount) 3 as 4 select Product_ID, Manufacturer_ID, 5 Initial_Retail_Value - Current_Used_Value 6 from Product; View created.
Here is a description of each line in the SQL statement that creates the view. Line 1 specifies the name of the view--;Product_Depreciation. Line 2 lists the three columns that compose the view: Product_ID, Manufacturer_ID, and Depreciated_Amount. Line 3 indicates that the view is to be created from a query. Line 4 specifies the first and second columns to be returned by the view: Product_ID and Manufacturer_ID. Line 5 indicates that the third column in the view--;Depreciated_Amount--;is calculated by subtracting Current_Used_Value from Initial_Retail_Value as retrieved from the Product table (line 6).
You then show the users how to query the view.
SQL> select Product_ID, Depreciated_Amount 2 from Product_Depreciation 3 order by Product_ID; PRODUCT_ DEPRECIATED_AMOUNT -------- ------------------ T1000 135 T1012 75 T1020 160 T1105 85 T1800 210
Oracle provides several mechanisms for restricting data access: views, database triggers, and table and column privileges that can be granted to database roles and individual users. You'll need to assess the security requirements of your application to determine which method is most appropriate.
If you use a view to restrict data access, several methods are available, each of which offers a different level of control over data access--;from coarse to very fine. A coarse control would define a view that is a subset of its base table. For example, an Employee table has a column named Salary, which you don't want to share with all users who need access to the table. Here is a view that doesn't include the Salary column:
SQL> create view Employee_no_salary 2 (Employee_ID, Last_Name, First_Name, Middle_Initial, 3 Hire_Date, Termination_Date, Date_of_Birth) 4 as 5 select Employee_ID, Last_Name, First_Name, Middle_Initial, 6 Hire_Date, Termination_Date, Date_of_Birth 7 from Employee; View created.
After the view is created, you can use the SQL*Plus DESCRIBE command to display the view's definition.
SQL> desc Employee_no_salary Name Null? Type ------------------------------- -------- ---- EMPLOYEE_ID NOT NULL NUMBER(4) LAST_NAME NOT NULL VARCHAR2(30) FIRST_NAME NOT NULL VARCHAR2(20) MIDDLE_INITIAL CHAR(1) HIRE_DATE NOT NULL DATE TERMINATION_DATE DATE DATE_OF_BIRTH DATE
As you see, the DESCRIBE command doesn't indicate whether the object description belongs to a table or view. Even if the user knows that the salary information is stored in the Employee table, the view can't return the column because it isn't contained in the view definition.
SQL> select Monthly_Salary
2 from Employee_no_salary;
select Monthly_Salary
*
ERROR at line 1:
ORA-00904: invalid column name
Sometimes limiting the columns retrieved by a view is too restrictive. Your application might require a security scheme that is data driven--;that is, based on a column value in a row. As you'll see in Chapter 27, "Managing Users and Roles," you cannot enforce this type of security scheme through the use of database roles. For a finer level of security, you might need to limit access to salary data to those users who are managers. To achieve this security level, you can add a column to the Employee table to indicate whether an employee is a manager. You can also add a CHECK constraint so that the Manager column is equal to either Y or N.
SQL> alter table Employee
2 add
3 (Manager char(1) check (Manager in ('Y','N')));
Table altered.
SQL> update Employee
2 set Manager = 'N'
3 where
4 Employee_ID != 1001;
8 rows updated.
SQL> update Employee
2 set Manager = 'Y'
3 where
4 Employee_ID = 1001;
1 row updated.
Now that all rows in the Employee table have a value for the Manager column, you can again alter the table so that the Manager column is mandatory.
SQL> alter table Employee 2 modify 3 (Manager not null); Table altered.
The next step is to add a column for storing the employee's Oracle username. The Oracle username will be set to the first letter of the employee's first name concatenated with the last name.
SQL> alter table Employee 2 add 3 (username varchar2(31)); Table altered. SQL> update Employee 2 set username = substr(first_name,1,1) || last_name; 9 rows updated.
Now you can create a view named Restrict_Salary that performs a combination self-join and outer-join of the Employee table.
SQL> create view Restrict_Salary as 2 select e.employee_id, e.last_name, e.first_name, 3 decode(s.manager,'Y',e.monthly_salary,null) monthly_salary 4 from employee e, employee s 5 where 6 user = s.username(+); View created.
An explanation is in order. First of all, one instance of the Employee table is identified with the alias e. The second instance of the Employee table is identified with the alias s. The pseudocolumn USER has the value of the Oracle user who is currently connected to the system--;so its value depends on who queries the view. The outer-join condition is User equal to the value for the Username column in the Employee table. This condition is used to retrieve the value of the Manager column for the user querying the view. The DECODE function looks at the value of Manager. If it is Y, the user is a manager and the monthly salary information is returned; if it isn't Y, the DECODE function returns a NULL.
Because Jean Smith is a manager, this is what happens when she queries the view:
SQL> select *
2 from Restrict_Salary;
EMPLOYEE_ID LAST_NAME FIRST_NAME MONTHLY_SALARY
----------- ------------------------------ -------------------- --------------
1001 SMITH JEAN 1500
1002 HERNANDEZ RANDY 3000
1003 GLEASON PAUL 1500
1004 BARRETT SARAH 1500
1005 HIGGINS BEN 3000
1006 YEN CINDY 1500
1007 GILROY MAX 1500
1008 CARSON BETH 3000
1009 SWANSON HARRY 2500
9 rows selected.
However, when Max Gilroy queries the view, he doesn't retrieve Monthly_Salary. (Max is not a manager.)
SQL> select *
2 from Restrict_Salary;
EMPLOYEE_ID LAST_NAME FIRST_NAME MONTHLY_SALARY
----------- ------------------------------ -------------------- --------------
1001 SMITH JEAN
1002 HERNANDEZ RANDY
1003 GLEASON PAUL
1004 BARRETT SARAH
1005 HIGGINS BEN
1006 YEN CINDY
1007 GILROY MAX
1008 CARSON BETH
1009 SWANSON HARRY
9 rows selected.
However, the preceding view might still miss the mark: you might have a requirement that gives some managers more privileges than other managers. In this case, you can assign a security level to each user and to each record. You can define a view that enables a user to access only the records that have a security level less than or equal to his or her own security level.
During application development, you'll typically be dealing with developers and users who have differing organizational perspectives and a broad range of technical sophistication. As a result, you'll want to use the mechanisms that Oracle offers to customize the environment for developers and users.
I include developers in this category because, like users, their knowledge of SQL in general and Oracle in particular will vary. For example, many forms and reports require the joining of several tables. The use of views can simplify this process because, as the application architect, you can define the views that developers need to be concerned with.
Views are also an excellent way to customize the database environment for end users. This is especially true for large organizations that access the same information for different purposes. Typically, each group has its own name for referring to the same piece of information. Because of the widespread use of third-party ad hoc query tools such as Andyne's GQL and Oracle's Browser, a column name should accurately describe the information it contains to help an end user determine which columns to query. By creating a view, you can customize the column names that a group of users will see.
A view can hide the complexity that exists in a multiple-table join. When you define a view, the users don't have to learn the idiosyncrasies of the SELECT statement. For example, the following SELECT statement joins the Report_ Header, Report_Item, and Product tables to return a product description for each repair item:
SQL> select H.Repair_ID, I.Item_Number, P.Product_ID, P.Manufacturer_ID,
P.Description
2 from Repair_Header H, Repair_Item I, Product P
3 where
4 H.Repair_ID = I.Repair_ID and
5 I.Product_ID = P.Product_ID and
6 I.Manufacturer_ID = P.Manufacturer_ID
7 order by H.Repair_ID, I.Item_Number;
REPAIR_ID ITEM_NUMBER PRODUCT_ID MANUFA DESCRIPTION
--------- ----------- ------------ ------ ------------------------------------
501 1 C2002 MIT501 50-CD Jukebox
502 1 D301 SEN101 Digital rangefinder
503 1 B311 TES801 Binary quadraphonic converter, 2000
504 1 B801 SEN101 Binary quadraphonic filter, 20 - 200
505 1 A903 TES801 Analog circuit transformer
506 1 TR901 TES801 AC/DC Transformer, 300W
506 2 B384 TES801 Bipolar resonator, digital
7 rows selected.
You can't realistically expect a casual user to be able to construct a statement of this complexity. But you can define a view that eliminates the complexity of the join operation for the user.
SQL> create view Repair_Product_View as
2 select H.Repair_ID, I.Item_Number, P.Product_ID, P.Manufacturer_ID,
P.Description
3 from Repair_Header H, Repair_Item I, Product P
4 where
5 H.Repair_ID = I.Repair_ID and
6 I.Product_ID = P.Product_ID and
7 I.Manufacturer_ID = P.Manufacturer_ID;
View created.
SQL> desc Repair_Product_View
Name Null? Type
------------------------------- -------- ----
REPAIR_ID NOT NULL NUMBER(6)
ITEM_NUMBER NOT NULL NUMBER
PRODUCT_ID NOT NULL VARCHAR2(12)
MANUFACTURER_ID NOT NULL VARCHAR2(6)
DESCRIPTION VARCHAR2(2000)
Using the Repair_Product_View, the user can simply select the desired columns without specifying any join conditions.
SQL> create view Repair_Product_View as
2 select H.Repair_ID, I.Item_Number, P.Product_ID, P.Manufacturer_ID,
P.Description
3 from Repair_Header H, Repair_Item I, Product P
4 where
5 H.Repair_ID = I.Repair_ID and
6 I.Product_ID = P.Product_ID and
7 I.Manufacturer_ID = P.Manufacturer_ID;
View created.
SQL> desc Repair_Product_View
Name Null? Type
------------------------------- -------- ----
REPAIR_ID NOT NULL NUMBER(6)
ITEM_NUMBER NOT NULL NUMBER
PRODUCT_ID NOT NULL VARCHAR2(12)
MANUFACTURER_ID NOT NULL VARCHAR2(6)
DESCRIPTION VARCHAR2(2000)
The capability to modify the contents of a base table by referencing a view is called view updatability.
If a view is based on a single base table, Oracle updates the base table for an INSERT, UPDATE, or DELETE statement that references the view. Of course, if the user does not have the appropriate privileges on the base table, Oracle returns an error message.
If a view is based on multiple base tables, you cannot use an UPDATE, INSERT, or DELETE statement to modify the view.
SQL> create view nonupdatable_view
2 (repair_id, item_number, depot_name)
3 as
4 select repair_id, item_number, company_name
5 from Depot_Estimate, Depot
6 where
7 Depot_Estimate.Depot_ID = Depot.Depot_ID;
View created.
SQL> delete from nonupdatable_view;
delete from nonupdatable_view
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
As you might expect, the number of columns specified for a view must be equal to the number of columns contained in the SELECT statement that defines the view. If the values aren't equal, the view won't be created and Oracle will return an error message.
SQL> create view Product_View
2 (Product_ID, Manufacturer_ID)
3 as
4 select Product_ID, Manufacturer_ID,
Initial_Retail_Value - Current_Used_Value
5 from Product;
(Product_ID, Manufacturer_ID)
*
ERROR at line 2:
ORA-01730: invalid number of column names specified
A view, like a table, can have a maximum of 254 columns, even if the view is defined by several base tables. If one of a view's base tables is renamed or dropped, the view becomes invalid. Here's an example:
SQL> create view Product_Depreciation
2 (Product_ID, Manufacturer_ID, Depreciated_Amount)
3 as
4 select Product_ID, Manufacturer_ID,
Initial_Retail_Value - Current_Used_Value
5 from Product;
View created.
SQL> rename Product to New_Product;
Table renamed.
SQL> select Product_ID, Depreciated_Amount
2 from Product_Depreciation;
from Product_Depreciation
*
ERROR at line 2:
ORA-04063: view "FRAYED_WIRES.PRODUCT_DEPRECIATION" has errors
Although Oracle provides an ALTER VIEW statement, you cannot use it to modify the definition of the view. To change a view, you have two choices:
Is it possible to define a view that is based on another view? Yes, it certainly is. The following CREATE VIEW statements create two views--;one of the Employee table and one of the Dependent table. Employee_View doesn't include salary information, and Dependent_View doesn't include the dependent's date of birth.
SQL> create view Employee_View 2 as 3 select Employee_ID, Last_Name, First_Name, Middle_Initial, Hire_Date, 4 Termination_Date 5 from Employee; View created. SQL> create view Dependent_View 2 as 3 select Employee_ID, Last_Name, First_Name 4 from Dependent; View created.
The next example creates a view that is a join of Employee_View and Dependent_View.
SQL> create view Employee_Dependent_View
2 (Employee_ID, Employee_Last_Name, Employee_First_Name,
Dependent_Last_Name, Dependent_First_Name)
3 as
4 select E.Employee_ID, E.Last_Name, E.First_Name, D.Last_Name, D.First_Name
5 from Employee_View E, Dependent_View D
6 where
7 E.Employee_ID = D.Employee_ID(+);
View created.
As the followingSELECT statement illustrates, you can create and use a view that is actually a join of two other views.
SQL> select Employee_ID, Employee_Last_Name, Employee_First_Name,
Dependent_Last_Name, Dependent_First_Name
2 from Employee_Dependent_View
3 order by Employee_ID;
EMPLOYEE_ID EMPLOYEE_LAST_NAME EMPLOYEE_FIRST_NAME DEPENDENT_LAST_NAME DEPENDE
----------- ------------------ ------------------- ------------------- -------
1001 SMITH JEAN SMITH BECKY
1001 SMITH JEAN SMITH RICHARD
1002 HERNANDEZ RANDY GREEN MARTIN
1003 GLEASON PAUL SYLVESTER DORIS
1004 BARRETT SARAH ROBERTS MAX
1005 HIGGINS BEN
1006 YEN CINDY
1007 GILROY MAX
1008 CARSON BETH
1009 SWANSON HARRY
10 rows selected.
In fact, Oracle imposes no limit on the number of view layers that you can define. However, you should avoid building views of views because of the performance penalty that can result. Let me explain why.
You can look at views from two perspectives--;conceptual and physical. (I use the word perspective because it would be far too confusing at this point to call them views.) From a conceptual perspective, a view is a convenient device with no apparent overhead. From a physical perspective, each time a query is performed on a view, Oracle must create a temporary table that contains the query results. If you query a view that is based on other views, Oracle must create several temporary tables. As you can envision, Oracle might need to perform a substantial amount of work in order to produce a result. I'm not suggesting that you never define a view that is based on another view; however, you need to understand the ramifications of your design and build a prototype of the performance before you make any design commitments.
Personal Oracle7 for Windows 95 allows you to manipulate views with the Navigator. To look at a list of the existing views in your local database, double-click the View folder. A list of all views appears on the right side of the main window. (See Figure 16.1.)
Using the Navigator, you can do the following:
To see the records provided by a view, select the view and right-click Open. The Navigator displays the view columns and the records returned by the view. (See Figure 16.2.) *
Depending on whether the view is updatable (please refer to the previous section, titled "Modifying Data Through a View," for further information), you can insert a new record, delete an existing record, or modify a record by right-clicking the appropriate menu item.
If you want to see the SQL statement that defines the view, select the view and right-click Properties. A window containing two tab folders will appear. From the General tab, you can scroll through the view definition contained in the Query field. (See Figure 16.3.) However, you cannot modify the view definition; SQL requires that you recreate the view if you want to change the view definition.
If you select the Object/Privilege tab, the Navigator prompts you for the password of the Oracle user that owns the view. (See Figure 16.4.)
Once you supply the correct password, you may grant any of the object privileges--;select, insert, update, and delete--;to a user or a role. The example in Figure 16.5 is granting four object privileges for a view to the TECHNICIAN role.
You can create a view based on a single table, a multitable join, or even other views. As an example, you will create a view by joining two tables. To create a view, select the VIEW folder and right-click New. In the Create View window, enter a name for the view; select the owner of the view (you may use the drop-down list); and select Personal Oracle7 as the database (for this example), as shown in Figure 16.6.
The Navigator will prompt you for the password for the view owner. Enter the password and click OK. (See Figure 16.7.)
You will then see two folders in the window--;Table and View. (See Figure 16.8.) Because the view that you are creating is based on a join of two tables, click the Table folder. You will see a list of the tables that are accessible to the view owner. (See Figure 16.9.) ***16DPO08***
Select the first table that you want by clicking it; a list of the table's columns appears. Select each column that you want to include in the view; click Add to add the column to the Selected Columns list. (See Figure 16.10.)
When you have finished selecting the columns from the first table in the join, select the next table by clicking it and add its columns to the view. (See Figure 16.11.)
The next step is a little tricky. You need to specify a join condition for the two tables. For this example, you should select the Patient_ID column and type Patient.Patient=patient insurance.patient id. (See Figure 16.12.) However, if you make a mistake, the Navigator won't let you edit the condition--;you'll have to reenter it.
Click OK to create the view. You can look at the records returned from the view by selecting the view and right-clicking Open. (See Figure 16.13.)
To delete a view, simply select it and right-click Delete. The Navigator asks you to confirm that you really want to delete the view. (See Figure 16.14.)
Organizations and individuals rely on unique, incrementing numbers to identify information. Check numbers, purchase order numbers, and customer numbers are all good examples of this concept. Because of this ubiquitous need, Oracle developed another database object: the sequence.
A sequence is a set of numbers that increase or decrease at a specified increment. A good paradigm for a sequence is the paper number dispenser found in a bakery. As you pull the paper number from the dispenser, the next customer's number is guaranteed to be larger than yours. The advantage of using an Oracle sequence is that it frees you from the headache of having to generate your own unique numbers.
A sequence is created with the CREATE SEQUENCE statement. The statement contains several default values that work for most situations. The default values are
Most of these default values are acceptable for most applications. At a minimum, you can create a sequence with this statement:
CREATE SEQUENCE sequence-name
sequence-name is the sequence name subject to the same naming restrictions as other Oracle database objects.
In the following section you create a sequence for assigning new Customer IDs.
You create a sequence in this fashion:
SQL> create sequence Customer_ID_Seq;
Sequence created.
SQL> select Customer_ID_Seq.nextval from dual;
NEXTVAL
---------
1
SQL> select Customer_ID_Seq.nextval from dual;
NEXTVAL
---------
2
NoteThe dual table is a special Oracle table that serves several purposes. It contains a single column, DUMMY, which is really never selected at all. The dual table contains a single row--;which is critical. Having only one row, you can select pseudocolumns, such as SYSDATE and USER, and be guaranteed that only one row will be returned. As shown, you can also use dual to inspect the current and next values of a sequence. Never insert another row into dual. If you do, many bad things will happen; for instance, Oracle data dictionary views will no longer work, and applications that rely on a single row in dual will fail.
You can also reference the next sequence value when inserting a new row or updating an existing row.
SQL> insert into Customer
2 (Customer_ID, Last_Name, First_Name)
3 values
4 (Customer_ID_Seq.nextval, 'Curry', 'Ronald');
1 row created.
SQL> select Customer_ID, Last_Name, First_Name
2 from Customer
3 where
4 Last_Name = 'Curry';
CUSTOMER_ID LAST_NAME FIRST_NAME
----------- ------------------------------ ------------------------------
3 Curry Ronald
As you saw in the previous example, a sequence will, by default, start with 1 and increment by 1. However, you might need a sequence that starts with some other number. You also might want to specify an increment other than 1. You can use the following CREATE SEQUENCE syntax:
CREATE SEQUENCE sequence-name INCREMENT BY increment-value START WITH starting-value
sequence-name is the sequence name subject to the same naming restrictions as other Oracle database objects; increment-value is the value to use when incrementing the sequence; and starting-value is the first value used by the sequence.
For instance, you can define a sequence that begins at 1000 and increments by 5.
SQL> create sequence Repair_ID_Seq
2 increment by 5
3 start with 1000;
Sequence created.
SQL> select Repair_ID_Seq.nextval from dual;
NEXTVAL
---------
1000
SQL> select Repair_ID_Seq.nextval from dual;
NEXTVAL
---------
1005
Referencing a sequence's next value is like pulling the next number from a paper number dispenser: the number automatically increments. To avoid having the number increment, you can view a sequence's current value in this manner:
SQL> select Repair_ID_Seq.currval from dual;
CURRVAL
---------
1015
SQL> select Repair_ID_Seq.currval from dual;
CURRVAL
---------
1015
As you see, currval will not increment when it is referenced. However, you cannot reference currval until you have selected at least one value from the sequence. In the following example, Oracle returns an error message when the current value of a newly created sequence is referenced. Once the next value of Employee_ID_Seq is referenced, you can select Employee_ID_Seq.currval.
SQL> create sequence Employee_ID_Seq
2 start with 1001;
Sequence created.
SQL> select Employee_ID_Seq.currval from dual;
ERROR:
ORA-08002: sequence EMPLOYEE_ID_SEQ.CURRVAL is not yet defined in this session
SQL> select Employee_ID_Seq.nextval from dual;
NEXTVAL
---------
1001
SQL> select Employee_ID_Seq.currval from dual;
CURRVAL
---------
1001
A synonym is another name for a table. Actually, the term table synonym is more accurate. Synonyms come in two flavors: private and public. A private synonym is visible only to the Oracle user who created it. A public synonym is visible to all Oracle users. Any Oracle user that has been granted the Resource role can create a private synonym. On the other hand, only an Oracle user that has been granted the DBA role can create a public synonym. I'll cover these database roles and related issues in Chapter 27.
In one way, a synonym is similar to a view: both objects enable you to reference a table by a different name. However, a synonym doesn't enable you to restrict columns or rename them.
A synonym provides an additional name for referencing a table. For example, you might not be able to rename a table because existing applications reference the current table name. However, a synonym that provides a more intuitive and meaningful name might be ideal for use in an ad hoc query tool.
Creating a synonym is a straightforward process. The syntax is
CREATE [PUBLIC] SYNONYM synonym-name FOR owner.object-name;
where synonym-name is the synonym name and subject to Oracle database object-naming requirements; owner is the name of the Oracle account that owns the referenced table or view; and object-name is the name of the table or view referenced by the synonym.
You might want to create a synonym to reference a table whose name is inappropriate or difficult to remember. In this example, the synonym p_artifact points to the table parthaginian_artifacts, which belongs to the same Oracle user.
SQL> create synonym p_artifact for parthaginian_artifacts; Synonym created.
A private synonym can also point to a table owned by another Oracle user. Suppose Oracle user RJOHNSON has created the Project table and wants to enable Oracle user KCHOW to read the Project table. First, RJOHNSON grants the select privilege on the Project table to KCHOW. However, each time KCHOW wants to look at the table, she has to remember to qualify the table name with RJOHNSON--;the table owner. Consequently, she creates a private synonym that enables her to reference the table by the Project name alone.
SQL> select Project_Number
2 from Project;
from Project
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> create synonym Project for RJOHNSON.Project;
Synonym created.
SQL> select Project_Number
2 from Project;
PROJECT_NUMBER
--------------
1201
2143
4310
Oracle provides a statement for eliminating a synonym--;the DROP SYNONYM statement. Its syntax is
DROP [PUBLIC] SYNONYM synonym-name; where synonym-name is the name of the existing synonym that you want to eliminate.
Consider the following example. You've developed a project accounting application at your company. You now must support two groups of users: those users who are running version 1.0 and those running version 2.0. However, version 2.0 requires some database changes; the database used by version 1.0 cannot be used by version 2.0. You can store the tables used by both versions in the same Oracle database, provided they are owned by separate Oracle accounts.
Suppose that Oracle account PAV10 owns the version 1.0 tables and that Oracle account PAV20 owns the version 2.0 tables. If you wanted to support a group of software testers who need to switch back and forth between the two versions, you could construct two SQL*Plus scripts. The first script drops the existing synonyms and create synonyms that point to the version 1.0 tables.
drop synonym Account_Number; ... drop synonym Task_Header; drop synonym Task_Detail; ... ... create synonym Account_Number for PAV10.Account_Number; ... create synonym Task_Header for PAV10.Task_Header; create synonym Task_Detail for PAV10.Task_Detail;
The second script also drops the existing synonyms but creates synonyms that point to the version 2.0 tables.
drop synonym Account_Number; ... drop synonym Task_Header; drop synonym Task_Detail; ... ... create synonym Account_Number for PAV20.Account_Number; ... create synonym Task_Header for PAV20.Task_Header; create synonym Task_Detail for PAV20.Task_Detail;
With these two scripts, a user could switch back and forth between the two versions of the project accounting tables.
Personal Oracle7 for Windows 95 allows you to manipulate synonyms with the Navigator. To look at a list of the existing synonyms in your local database, double-click the Synonym folder. A list of all synonyms appears on the right side of the main window. (See Figure 16.15.)
To examine the definition of a synonym, select the synonym and right-click Properties. The Navigator will display a window that identifies the name of the synonym, its owner, and the table (or view) that it references. (See Figure 16.16.) However, if the owner name and table name are lengthy, you may not be able to read the full table name.
To create a new synonym, select the Synonym folder and right-click New. In the Create Synonym window (see Figure 16.17), enter a name for the new synonym and specify whether you want it to be public or private. Select Personal Oracle7 as the database and select the table from the drop-down list. Then click OK to create the synonym. The selections in Figure 16.17 create a public synonym.
If you want to create a private synonym, you must select the Private To radio button and identify the user that will own the synonym in the drop-down list to the right. The selections in Figure 16.18 create a private synonym.
To delete a synonym, select the synonym that you want to delete and right-click Delete. The Navigator asks you to confirm that you really want to delete the synonym. (See Figure 16.19.) ***16DPO19***
This chapter discusses the following concepts: