PL/SQL NOCOPY Parameter

Visit OraclePassport Main Page

The PL/SQL has two different methods for passing parameter values between stored procedures and functions,

1. Pass by value
Actual value is being copied to another memory location for the calling procedure/function to use
i.e it copies the actual value of the parameter into the formal parameter. So, both called and calling procedures has got 2 different memory locations to store the value



2. Pass by reference

Only the memory location (address) is passed so that the data is not copied. The calling procedure and the called procedure, both uses the same value stored in a single memory location. So the actual and the formal parameters refer to the same memory location that holds the value.

As we know, there are three different type of parameters in PL/SQL:
Read More....

What is a Materialized view?

Visit OraclePassport Main Page

A materialized view is a snapshot of data which could be refreshed at defined intervals. From the name, it is unclear that a materialized view can hold data, since views dont. Views are nothing but a query stored in a Database. Whenever we do a select * from view, it runs the saved query and fetches data.

Sometimes there would be a need for a huge query with many join operations between more than 10-20 tables containing millions of records and many where conditions. Especially in a datawarehousing environment or for reporting purposes. In such conditions, running the query each time whenever required, is not a feasible option. So, there should be a technique to store the data such that we should run the query only if there is a change in data in the underlying tables.

There comes the usage of a materialized views as they can store the data they have queried .

What are different types of Materialized views?
Read More....