Oracle Application Technical Consultant Interview Questions - PL/SQL

PL/SQL

1) What are materialized Views? What is the difference between the MV's and Views?
A materialized view is a database object that contains the results of a query.
They are local copies of data located remotely, or are used to create summary
tables based on aggregations of a table's data.
Difference
-----------
Views do not contain data (logical table) but MV's contain data (physical table).
Advantages
-----------
1)Performance Advantages especially for large tables as we populate the table
with increamental changes MV's can be used dynamically by the optimizer to change
the execution paths for queries. This is called
query rewrite feature.
2)easy network loads
3)Enable Data Subsetting
For example, if you have a regional sales office, then you might replicate
only the data that is needed in that region, thereby cutting down on
unnecessary network traffic.
4)Enable disconnected Computing
Materialized views do not require a dedicated network connection. Though you
have the option of automating the refresh process by scheduling a job, you
can manually refresh your materialized view on-demand, which is an ideal
solution for sales applications running on a laptop

2) How to find duplicate rows in a table using a sql query? If so how will you delete
those rows using a single query?
select emp_id,count(*) from emp
group by emp_id having count(*) > 1;

Delete from emp where rowid = (select max(rowid) from emp
group by emp_id having count(*) > 1);



3) What are Nested tables,Varrays and index-by tables?
They belong to collection. All are single dimensional. Index by table is
not usable in SQL and also cannot be usable as a column datatype.
Initialization of index by table is automatic when declared. But for
others thru constructor,fetch, assignment.

4) What are the events that we can write in custom.pll?
1) ZOOM
2) WHEN-NEW-FORM-INSTANCE
3) WHEN-NEW-BLOCK-INSTANCE
4) WHEN-NEW-RECORD-INSTANCE
5) WHEN-NEW-ITEM-INSTANCE
6) WHEN-VALIDATE-RECORD