MW Podcast 003 - Explore the OTM UI: Map Feature

Note:-Please comment and reply me.

Read More

Replacing/Altering Views

Replacing/Altering Views

To alter the definition of a view, you must replace the view using one of the following methods:

• A view can be dropped and then re-created. When a view is dropped, all grants of corresponding view privileges are revoked from roles and users. After the view is re-created, necessary privileges must be regranted.
• A view can be replaced by redefining it with a CREATE VIEW statement that contains the OR REPLACE option. This option replaces the current definition of a view, but preserves the present security authorizations.

For example, assume that you create the ACCOUNTS_STAFF view, as given in a previous example. You also grant several object privileges to roles and other users. However, now you realize that you must redefine the ACCOUNTS_STAFF view to correct the department number specified in the WHERE clause of the defining query, because it should have been 30. To preserve the grants of object privileges that you have made, you can replace the current version of the ACCOUNTS_STAFF view with the following statement:

CREATE OR REPLACE VIEW Accounts_staff AS

    SELECT Empno, Ename, Deptno

    FROM Emp

    WHERE Deptno = 30

    WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

Replacing a view has the following effects:

• Replacing a view replaces the view's definition in the data dictionary. All underlying objects referenced by the view are not affected.
• If previously defined but not included in the new view definition, then the constraint associated with the WITH CHECK OPTION for a view's definition is dropped.
• All views and PL/SQL program units dependent on a replaced view become invalid.

With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view. The following statement inserts a new row into the EMP table using the ACCOUNTS_STAFFview:

INSERT INTO Accounts_staff

    VALUES (199, 'ABID', 30);

Restrictions on DML operations for views use the following criteria in the order listed:

1. If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

2. If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or updated in, the base table (using the view), if the view cannot select the row from the base table.

3. If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then a row cannot be inserted into the base table using the view.

4. If the view was created by using an expression, such as DECODE(deptno, 10, "SALES", ...), then rows cannot be inserted into or updated in the base table using the view.

The constraint created by WITH CHECK OPTION of the ACCOUNTS_STAFF view only allows rows that have a department number of 10 to be inserted into, or updated in, the EMP table. Alternatively, assume that the ACCOUNTS_STAFF view is defined by the following statement (that is, excluding the DEPTNO column):

CREATE VIEW Accounts_staff AS

    SELECT Empno, Ename

    FROM Emp

    WHERE Deptno = 10

    WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

Considering this view definition, you can update the EMPNO or ENAME fields of existing records, but you cannot insert rows into the EMP table through the ACCOUNTS_STAFF view because the view does not let you alter the DEPTNO field. If you had defined a DEFAULT value of 10 on the DEPTNO field, then you could perform inserts.

If you don’t want any DML operations to be performed on views, create them WITH READ ONLY option. Then no DML operations are allowed on views.

Read More

Views

Views are known as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also.

Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert, Update and delete from views, just as any other table.

Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.

The following sections explain how to create, replace, and drop views using SQL commands.

Creating Views

Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name and location we have to give a join query like this.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc

        From emp e, dept d where e.deptno=d.deptno;

So everytime we want to see emp details and department names where they are working we have to give a long join query. Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below

create view emp_det as select e.empno,

e.ename,e.sal,e.deptno,d.dname,d.loc

        from emp e, dept d where e.deptno=d.deptno;

Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.

select * from emp_det;

This will show same result as you have type the long join query.  Now you can treat this EMP_DET view same as  any other table.

For example, suppose all the employee working in Department No. 10 belongs to accounts department and most of the time you deal with these people. So every time you  have to give a DML or Select statement you have to give a WHERE condition like .....WHERE DEPTNO=10. To avoid this, you can create a view as given below

CREATE VIEW accounts_staff AS

    SELECT Empno, Ename, Deptno

    FROM Emp

    WHERE Deptno = 10

    WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

Now to see the account people you don’t have to give a query with where condition you can just type the following query.

Select * from accounts_staff;

Select sum(sal) from accounst_staff;

Select max(sal) from accounts_staff; 

As you can see how views make  things easier.

The query that defines the ACCOUNTS_STAFF view references only rows in department 10. Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATEstatements issued against the view are not allowed to create or result in rows that the view cannot select.

Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);

However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using theACCOUNTS_STAFF view:

INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);

Read More

Inline view in Oracle

An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query.

Example inline view:

SELECT * 
  FROM ( SELECT deptno, count(*) emp_count
         FROM emp
         GROUP BY deptno ) emp,
       dept
 WHERE dept.deptno = emp.deptno;

Another good example of an inline view is:

SELECT a.last_name, a.salary, a.department_id, b.maxsal
  FROM employees a,
       ( SELECT department_id, max(salary) maxsal
         FROM employees
         GROUP BY department_id ) b
 WHERE a.department_id = b.department_id
   AND a.salary = b.maxsal;

The above query display the employees who earn the highest salary in each department.

Note:-Please comment and reply me.

Read More

Oracle Transportation Management

Oracle Transportation Management delivers robust transportation planning and execution capabilities to shippers and third party logistics providers. It integrates and streamlines transportation planning, execution, freight payment, and business process automation on a single application across all modes of transportation, from full truckload to complex multileg air, ocean, and rail shipments.

Oracle Transportation Management lowers transportation costs, improves customer service and asset utilization, and provides flexible, global fulfillment options.

Tight Integration, Modular Adoption
Regardless of the size or volume of your business, Oracle Transportation Management delivers the capabilities you need in an open, standards-based architecture that allows you to start with a single component or a mix of components. It also provides the flexibility to grow easily, without installing or reinstalling added functionality.

Oracle Transportation Management can be used with an enterprise resource planning or legacy order management system, as well as any commercial best-of-breed or legacy warehouse management system.




Note:-Please comment and reply me.

Read More