PL/SQL Interview Questions

1.   What is PL/SQL ?

PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and datatypes are similar to that of ADA. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.  PL SQL is a block structured programming language. It combines data manipulation & data processing power. It supports all SQL data types. Also has its own data types i,e BOOLEAN,BINARY INTEGER

2.  What is the basic structure of PL/SQL ?

A PL/SQL block has three parts:

a declarative part,

an executable part,

and an exception-handling part.

First comes the declarative part, in which items can

be declared. Once declared, items can be manipulated in the executable part.

Exceptions raised during execution can be dealt with in the exception-handling

part.

3.  What are the components of a PL/SQL block ?

PL/SQL Block contains :
Declare : optional
Variable declaration
Begin  : Manadatory
Procedural statements.
Exception : Optional
any errors to be trapped
End :  Mandatory

5.  What are the datatypes a available in PL/SQL ?
Following are the datatype supported in oracle PLSQL
Scalar Types
BINARY_INTEGER
DEC
DECIMAL
DOUBLE PRECISION
FLOAT
INT
INTEGER
NATURAL
NATURALN
NUMBER
NUMERIC
PLS_INTEGER
POSITIVE
POSITIVEN
REAL
SIGNTYPE
SMALLINT
CHAR
CHARACTER
LONG
LONG RAW
NCHAR
NVARCHAR2
RAW
ROWID
STRING
UROWID
VARCHAR
VARCHAR2
DATE
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE

BOOLEAN

Composite Types
RECORD
TABLE
VARRAY
LOB Types
BFILE
BLOB
CLOB
NCLOB

Reference Types
REF CURSOR
REF object_type

6.  What  are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?%  TYPE  provides  the data type of a variable or a database column to that variable.

% ROWTYPE  provides the record type that represents a entire row of a table or view or columns selected in the cursor.

 The advantages are :

I. Need not  know about variable's data type

ii.  If  the  database  definition of a column in a table changes, the data type of a variable changes accordingly.

Advantage is, if one change the type or size of the column in the table, it will be reflected in our program unit without making any change.
%type is used to refer the column's datatype  where as %rowtype is used to refer the whole record in a table.
7.  What is difference between % ROWTYPE and TYPE RECORD ?

%  ROWTYPE  is to be used whenever query returns a entire row of a table or view.

TYPE  rec  RECORD is to be used whenever query returns columns of different table or views and variables.      E.g.  TYPE  r_emp is RECORD (eno emp.empno% type,ename emp ename %type);     e_rec emp% ROWTYPE    cursor c1 is select empno,deptno from emp;     e_rec c1 %ROWTYPE.

8.  What is PL/SQL table ?

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers

One-dimensional

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array.

Unbounded or Unconstrained

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.

Sparse

In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.

Homogeneous elements

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.

Indexed by integers

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -2³¹-1 to 2³¹-1, so you have an awful lot of rows with which to work

9.  What is a cursor ? Why Cursor is required ?

Cursor is a named private SQL area from where information can be accessed. Cursors  are  required  to  process rows individually for queries returning multiple rows.

10.  Explain the two type of Cursors ?

implicit cursor: implicit cursor is a type of cursor which is automatically maintained by the Oracle server itself.implicit cursor returns only one row.
Explicit Cursor: Explicit Cursor is defined by the Proframmer,and it has for phases:declare,open,fetch and close.explicit Cursor returns more than one row.

11.  What are the PL/SQL Statements used in cursor processing ?

DECLARE  CURSOR  cursor  name,  OPEN  cursor  name,  FETCH cursor name INTO  or Record types, CLOSE cursor name.

12.  What are the cursor attributes used in PL/SQL ?

%ISOPEN  - to check whether cursor is open or not

 % ROWCOUNT - number of rows fetched/updated/deleted.

   %  FOUND - to check whether cursor has fetched any row. True if rows are fetched.

     % NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.

These  attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors

13.  What is a cursor for loop ?

Cursor  for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes

when all the records have been processed.

 eg. FOR emp_rec IN C1 LOOP

salary_total := salary_total +emp_rec sal;

          END LOOP;

cursor for loop is use for automatically open ,fetch,close

15.  Explain the usage of  WHERE CURRENT OF clause in cursors ?

PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor in order to allow you to easily make changes to the most recently fetched row of data.

The general format for the WHERE CURRENT OF clause is as follows:

UPDATE table_name   SET set_clause WHERE CURRENT OF cursor_name;DELETE  FROM table_name WHERE CURRENT OF cursor_name;

Notice that the WHERE CURRENT OF clause references the cursor and not the record into which the next fetched row is deposited.

The most important advantage to using WHERE CURRENT OF where you need to change the row fetched last is that you do not have to code in two (or more) places the criteria used to uniquely identify a row in a table. Without WHERE CURRENT OF, you would need to repeat the WHERE clause of your cursor in the WHERE clause of the associated UPDATEs and DELETEs. As a result, if the table structure changes in a way that affects the construction of the primary key, you have to make sure that each SQL statement is upgraded to support this change. If you use WHERE CURRENT OF, on the other hand, you only have to modify the WHERE clause of the SELECT statement.

This might seem like a relatively minor issue, but it is one of many areas in your code where you can leverage subtle features in PL/SQL to minimize code redundancies. Utilization of WHERE CURRENT OF, %TYPE, and %ROWTYPE declaration attributes, cursor FOR loops, local modularization, and other PL/SQL language constructs can have a big impact on reducing the pain you may experience when you maintain your Oracle-based applications.

Let's see how this clause would improve the previous example. In the jobs cursor FOR loop above, I want to UPDATE the record that was currently FETCHed by the cursor. I do this in the UPDATE statement by repeating the same WHERE used in the cursor because (task, year) makes up the primary key of this table:

WHERE task = job_rec.task  AND year = TO_CHAR (SYSDATE, 'YYYY');

This is a less than ideal situation, as explained above: I have coded the same logic in two places, and this code must be kept synchronized. It would be so much more convenient and natural to be able to code the equivalent of the following statements:

Delete the record I just fetched.

or:

Update these columns in that row I just fetched.

A perfect fit for WHERE CURRENT OF! The next version of my winterization program below uses this clause. I have also switched to a simple loop from FOR loop because I want to exit conditionally from the loop:

DECLARE   CURSOR fall_jobs_cur IS SELECT ... same as before ... ;   job_rec fall_jobs_cur%ROWTYPE;BEGIN   OPEN fall_jobs_cur;   LOOP      FETCH fall_jobs_cur INTO job_rec;      IF fall_jobs_cur%NOTFOUND      THEN         EXIT;      ELSIF job_rec.do_it_yourself_flag = 'YOUCANDOIT'      THEN         UPDATE winterize SET responsible = 'STEVEN'          WHERE CURRENT OF fall_jobs_cur;         COMMIT;         EXIT;      END IF;   END LOOP;   CLOSE fall_jobs_cur;END;

16.  What is a database trigger ? Name some usages of database trigger ?
A database trigger is a stored procedure that is invoked automatically when a predefined event occurs.
Database triggers enable DBA's (Data Base Administrators) to create additional relationships between separate databases.
For example, the modification of a record in one database could trigger the modification of a record in a second database.
17.  How many types of database triggers can be specified on a table ? What are they ?

Insert         Update          Delete Before  Row    o.k.           o.k.            o.k.  After Row      o.k.           o.k.            o.k. Before Statement    o.k.      o.k.            o.k. After Statement     o.k.      o.k.            o.k. If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement. If  WHEN  clause  is specified, the trigger fires according to the returned Boolean value. the different types of triggers: * Row Triggers and Statement Triggers * BEFORE and AFTER Triggers * INSTEAD OF Triggers * Triggers on System Events and User Events 18.  What are two virtual tables available during database trigger execution ? The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available. For  triggers related to UPDATE only OLD.column_name NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available. The two virtual table available are old and new. 19.What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?

 To avoid the mutation table error ,the procedure should be declared as an AUTONOMOUS TRANSACTION.
By this the procedure will be treated as an separate identity.
20.  Write  the  order  of precedence for validation of a column in a table ?

         I.  done using Database triggers.

          ii. done using Integarity Constraints.

21.  What is an Exception ? What are types of Exception ?

·         Predefined

Do not declare and allow the Oracle server to raise implicitly

NO_DATA_FOUND

TOO_MANY_ROWS

INVALID_CURSOR

ZERO_DIVIDE

INVALID_CURSOR

WHEN EXCEPTION THEN …

·         Non predefined

                                         Declare within the declarative section and allow allow Oracle server to raise implicitly

o        SQLCODE – Returns the numeric value for the seeor code

o        SQLERRM – Returns the message associated with error number

DECLARE -- PRAGMA EXCEPTION_INIT (exception, error_number)

RAISE – WHEN EXCEPTION_NAME THEN …

·         User defined

                    Declare within the declarative section and raise explicitly.

IF confidition the

RAISE EXCEPTION or RAISE_APPLICATION_ERROR

22.  What is Pragma EXECPTION_INIT ? Explain the usage ?
Pragma exception_init  Allow you to handle the Oracle predefined message by you'r own message. means you can instruct compiler toassociatethe specific message to oracle predefined message at compile time.This way you Improve the Readbility of your program,and handle it accoding to your own way.
It should be declare at the DECLARE section.
example
declare
salary number;
FOUND_NOTHING exception;
Pragma exception_init(FOUND_NOTHING ,100);
begin
select sal in to salaryfrom emp where ename ='ANURAG';
dbms_output.put_line(salary);
exception
WHEN FOUND_NOTHING THEN
dbms_output.put_line(SQLERRM);
end;
23.  What is Raise_application_error ?
Raise_application_error is used to create your own error messages which can be more descriptive than named exceptions.
Syntax is:-
Raise_application_error (error_number,error_messages);
where error_number is between -20000 to -20999..
24.  What are the return values of functions SQLCODE and SQLERRM ?
Pl / Sql Provides Error Information via two Built-in functions, SQLCODE & SQLERRM.
SQLCODE Returns the Current Error Code.
              Returns 1.
SQLERRM  Returns the Current Error Message Text.
               Returns  " User Defined Exception  "
25.  Where the Pre_defined_exceptions are stored ?
PL/SQL declares predefined exceptions in the STANDARD package.
26.  What is a stored procedure ?
Stored Procedure is the PlSQL subprgram stored in the databasse .

Stored Procedure

A program running in the database that can take complex actions based on the inputs you send it. Using a stored procedure is faster than doing the same work on a client, because the program runs right inside the database server. Stored procedures are nomally written in PL/SQL or Java.

advantages fo Stored Procedure
Extensibility,Modularity, Reusability, Maintainability and one time compilation.
28.  What are the modes of parameters that can be passed to a procedure ?
1.in:
in parameter mode is used to pass values to subprogram when invoked.
2.out:
out is used to return values to callers of subprograms
3.in out:
it is used to define in and out
29.  What are the two parts of a procedure ?

PROCEDURE name (parameter list.....)

     is

     local variable declarations

     BEGIN

          Executable statements.

     Exception.

     exception handlers

     end;

31.  Give the structure of the function ?

  FUNCTION name (argument list .....) Return datatype is

     local variable declarations

     Begin

          executable statements

     Exception

          execution handlers

     End;

32.  Explain how procedures and functions are called in a PL/SQL block ?
Procedure can be called in the following ways

a) CALL <procedure name> direc

b) EXCECUTE <procedure name> from calling environment

c) <Procedure name> from other procedures or functions or packages

Functions can be called in the following ways

a) EXCECUTE <Function name> from calling environment. Always use a variable to get the return value.

b) As part of an SQL/PL SQL Expression
33.  What are two parts of package ?

The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.

Package Specification contains declarations that are global to the packages and local to the schema.

Package  Body  contains  actual  procedures  and  local  declaration of the procedures and cursor declarations.

33.What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?

A  cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.

A  cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

The scope of A cursor declared in a procedure is limited to that procedure only.
The Scope of cursor declared in a package specification is global .
Example:
create or replace package curpack is
cursor c1 is select * from emp;
end curpack;
This will create a package Now You can use this cursor any where. Like:
set serveroutput on
begin
for r1 in curpack.c1 loop
dbms_output.put_line(r1.empno||'  '||r1.ename);
end loop;
end;
this will dispaly all empno and enames.
It will be better to use ref cursor in packages
35.  How packaged procedures and functions are called from the following?

     a. Stored procedure or anonymous block

     b. an application program such a PRC *C, PRO* COBOL

     c. SQL *PLUS

     a. PACKAGE NAME.PROCEDURE NAME (parameters);

        variable := PACKAGE NAME.FUNCTION NAME (arguments);

        EXEC SQL EXECUTE

     b.

          BEGIN

               PACKAGE NAME.PROCEDURE NAME (parameters)

               variable := PACKAGE NAME.FUNCTION NAME      (arguments);

          END;

          END EXEC;

     c.  EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any

out/in-out parameters. A function can not be called.

36.Name  the  tables  where  characteristics  of  Package,  procedure and functions are stored ?

The Data dictionary tables/ Views where the characteristics of subprograms and Packages are stored are mentioned below

a) USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTS

b) USER_SOURCE, ALL_SOURCE, DBA_SOURCE

c) USER_DEPENCENCIES

d) USER_ERRORS, ALL_ERRORS, DBA_ERRORS

37.  What is Overloading of procedures ?

Overloading procs are 2 or more procs with the same name but different arguments.
Arguments needs to be different by class it self. ie char and Varchar2 are from same class.
Packages -
The main advantages of packages are -
1- Since packages has specification and body separate so, whenever any ddl is run and if any proc/func(inside pack) is dependent on that, only body gets invalidated and not the spec. So any other proc/func dependent on package does not gets invalidated.
2- Whenever any func/proc from package is called, whole package is loaded into memory and hence all objects of pack is availaible in memory which means faster execution if any is called. And since we put all related proc/func in one package this feature is useful as we may need to run most of the objects.
3- we can declare global variables in the package

38.Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

Autonomous Transaction is a feature of oracle 8i which maintains the state of
its transactions and save it , to affect with the commit or rollback of the
surrounding transactions.

Here is the simple example to understand this :-

ora816 SamSQL :> declare

2 Procedure InsertInTest_Table_B

3 is

4 BEGIN

5 INSERT into Test_Table_B(x) values (1);

6 Commit;

7 END ;

8 BEGIN

9 INSERT INTO Test_Table_A(x) values (123);

10 InsertInTest_Table_B;

11 Rollback;

12 END;

13 / PL/SQL procedure successfully completed.

ora816 SamSQL :> Select * from Test_Table_A; X---------- 123

ora816 SamSQL :> Select * from Test_Table_B; X---------- 1

Notice in above pl/sql COMMIT at line no 6 , commits the transaction at
line-no 5 and line-no 9. The Rollback at line-no 11 actually did nothing.
Commit/ROLLBACK at nested transactions will commit/rollback all other DML
transaction before that. PRAGMA AUTONOMOUS_TRANSACTION override this behavior.
Let us the see the following example with PRAGMA AUTONOMOUS_TRANSACTION.

39.  What is difference between a PROCEDURE & FUNCTION ?

A function always return a values while procedure can return one or more values through Parameters.
A function can call directly by sql statement like select "func_name" from dual while procedure cannot.

40.  What is Data Concarency and Consistency?

Concurrency

How well can multiple sessions access the same data simultaneously

Consistency

How consistent is the view of the data between and within multiple sessions, transactions or statements

41.  Talk about "Exception Handling" in PL/SQL?

the exception are written to handle the exceptions thrown by programs.
we have user defined and system exception.
user defined exception are the exception name given by user (explicitly decalred and used) and they are raised to handle the specific behaviour of program.
system exceptions are raised due to invalid data(you dont have to deaclre these). few examples are when no_data_found, when others etc.

44.  Can we use commit or rollback command in the exception part of PL/SQL block?

Yes, we can use the TCL commands(commit/rollback) in the exception block of a stored procedure/function. The code in this part of the program gets executed like those in the body without any restriction. You can include any business functionality whenever a condition in main block(body of a proc/func) fails and requires a follow-thru process to terminate the execution gracefully!

DECALRE

…..

BEGIN

…….

EXCEPTION

WHEN NO_DATA_FOUND THEN

            INSERT INTO err_log(

err_code, code_desc)

                        VALUES(‘1403’, ‘No data found’)

            COMMIT;

            RAISE;

END

46.  What is bulk binding please explain me in brief ?

Bulk Binds (BULK COLLECT , FORALL ) are a PL/SQL technique where, instead of multiple individual SELECT, INSERT, UPDATE or DELETE statements are executed to retrieve from, or store data in, at table, all of the operations are carried out at once, in bulk.
This avoids the context-switching you get when the PL/SQL engine has to pass over to the SQL engine, then back to the PL/SQL engine, and so on, when you individually access rows one at a time. To do bulk binds with Insert, Update and Delete statements, you enclose the SQL statement within a PL/SQL FORALL statement.
To do bulk binds with Select  statements, you include the Bulk Collect INTO a collection clause in the SELECT Statement instead of using Simply into .
Collections, BULK COLLECT and FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance
Bulk Binding is used for avoiding the context switching between the sql engine and pl/sql engine. If we use simple For loop in pl/sql block it will do context switching between sql and pl/sql engine for each row processing that degrades the performance of pl/sql bloack.
So that for avoiding the context switching betn two engine we user FORALL keyword by using the collection pl/sql tables for DML. forall is pl/sql keyword.
It will provides good result and performance increase.

47.Why Functions are used in oracle ?Can Functions Return more than 1 values?Why Procedures are used in oracle ?What are the Disadvantages of packages?What are the Global Variables in Packages?

The functions are used where we can't used the procedure.i.e we can use a function the in select statments,in the where clause of delete/update statments.But the procedure can't used like that.
It is true that function can return only one value, but a function can be used to return more than one value,by using out parameters and also by using ref cursors.
There is no harm in using the out parameter,when functins are used in the DML statements we can't used the out parameter(as per rules).

49.  What are the restrictions on Functions ?

Function cannot have DML statemets and we can use select statement in function
If you create function with DML statements we get message function will be created
But if we use in select statement we get error

50.  What happens when a package is initialized ?

when a package is initialised that is called for the first time the entire package is loaded into SGA and any variable declared in the package is initialises.

52.  What is PL/SQL table?

Pl/Sql table is a type of datatype in procedural language Extension.It has two columns.One for the index,say Binary index And another column for the datas,which might further extend to any number of rows (not columns)in future.

PL/SQL table is nothing but one dimensional array. It is used to hold similar type of data for temporary storage. Its is indexed by binary integer.

3.  can i write plsql block inside expection

Yes you can write PL/SQL block inside exception section. Suppose you want to insert the exception detail into your error log table, that time you can write insert into statement in exception part. To handle the exception which may be raised in your exception part, you can write the PL/SQL code in exception part.

54.  Can e truncate some of the rows from the table instead of truncating the full table.

You can truncate few rows from a table if the table is partitioned. You can truncate a single partition and keep remaining.

CREATE TABLE parttab (
state  VARCHAR2(2),
sales  NUMBER(10,2))
PARTITION BY LIST (state) (
PARTITION northwest VALUES ('OR', 'WA')
 TABLESPACE uwdata,
PARTITION southwest VALUES ('AZ', 'CA')
 TABLESPACE uwdata);

INSERT INTO parttab VALUES ('OR', 100000);
INSERT INTO parttab VALUES ('WA', 200000);
INSERT INTO parttab VALUES ('AZ', 300000);
INSERT INTO parttab VALUES ('CA', 400000);
COMMIT;

SELECT * FROM parttab;

ALTER TABLE parttab
TRUNCATE PARTITION southwest;

SELECT * FROM parttab;
56.  What is the difference between a reference cursor and normal cursor ?
REF cursors are different than your typical, standard cursors. With standard cursors, you know the cursor's query ahead of time. With REF cursors, you do not have to know the query ahead of time. With REF Cursors, you can build the cursor on the fly
Normal Cursor is a Static Cursor.
Refernce Cursor is used to create dynamic cursor.
There are two types of Ref Cursors:
1. Weak cursor and 2.Strong cursor
 Type ref_name is Ref cursor [return type]
[return type] means %Rowtype
if Return type is mentioned then it is Strong cursor else weak cursor
The Reference cursor does not support For update clause.

Normal cursor is used to process more than one record in plsql.
Refcusor is a type which is going to hold set of records which can be sent out through the procedure or function out variables.
we can use Ref cursor as an IN OUT parameter .
58.  Based on what conditions can we decide whether to use a table or a view or a materialized view ?
Table is the basic entity in any RDBMS , so for storing data you need table .
for view - if you have complex query from which you want to extract data again and again , moreover it is a standard data which is required by many other user also for REPORTS generation then create view . Avoid to insert / update / delete through view unless it is essential. keep view as read only (FOR SHOWING REPORTS)
for materialized view - this view ia mainly used in datawarehousing . if you have two databases and you want a view in both databases , remember in datawarehousing we deal in GB or TB datasize . So create a summary table in a database and make the replica(materialized view) in other database.
when to create materialized view-
[1] if data is in bulk and you need same data in more than one database then create summary table at one database and replica in other databases
[2] if you have summary columns in projection list of query.
main advatages of materialized view over simple view are -
[1] it save data in database whether simple view's definition is saved in database
[2] can create parition or index on materialize view to enhance the performance of view , but cannot on simple view.
59.  What is the difference between all_ and user_ tables ?

  An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.

While

  A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.

User_tables data dictionary contains all the tables created by the users under that schema.
whereas All_tables stores all the tables created in different schema. If any user id have the Grants for access table of diff. schema then he can see that table through this dictionary.
61.  what is p-code and sourcecode ?
P-code is Pre-complied code stored in Public cache memory of System Global Area after the Oracle instance is started, whereas sourcecode is a simple code of sp, package, trigger, functions etc which are stored in Oracle system defined data dictionary. Every session of oracle access the p-code which have the Execute permission on that objects.
Source code stored in user_objects data dictinary for user defined Store proc, Trigger, Package, Function. DBA_object stores all the db objects in sp. DB. ALL_objects stores all the db objects in sp. schema.
Source code: The code say a PLSQL block that the user types for the exectionP-Code: The source code after -Syntax check, Parse tree generation, Symantic check, and further execution of the parse tree..giving the final P-code ready for data fetch or manipulation ...

63.  Is there any limitation on no. of triggers that can be created on a table?

There is no limit on number of triggers on one table.
you can write as many u want for insert,update or delte by diff names.
if table has got n columns. we can create n triggers based on each column.
64.What happens when DML Statement fails?A.User level rollbackB.Statement Level RollbackC.Sustem evel Rollback

When a DML statement executes (fails/sucess) an automatic Commit is executed. Eg : Create a table t1. Insert a record in t1. Then again to create the same object t1.  

65.What steps should a programmer should follow for better tunning of the PL/SQL blocks?

SQL Queries – Best Practices

1.         Always use the where clause in your select statement to narrow the number of rows returned.

If we don’t use a where clause, the Oracle performs a full table scan on our table and returns all of the rows.

2.         Use EXISTS clause instead of IN clause as it is more efficient than IN and performs faster.

Ex:

Replace

SELECT * FROM DEPT WHERE DEPTNO IN

 (SELECT DEPTNO FROM EMP E)

With

SELECT * FROM DEPT D WHERE EXISTS

  (SELECT 1 FROM EMP E WHERE D.DEPTNO = E.DEPTNO)

Note: IN checks all rows. Only use IN if the table in the sub-query is extremely small.

3.         When you have a choice of using the IN or the BETWEEN clauses in your SQL, use the BETWEEN clause as it is much more efficient than IN.

            Depending on the range of numbers in a BETWEEN, the optimizer will choose to do a full table scan or use the index.

4.         Avoid WHERE clauses that are non-sargable. Non-sargable search arguments in the WHERE clause, such as "IS NULL", "OR", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE %500" can prevent the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, or expressions that have the same column on both sides of the operator, are not sargable.

Convert multiple OR clauses to UNION ALL.

5.         Use equijoins. It is better if you use with indexed column joins.  For maximum performance when joining two or more tables, the indexes on the columns to be joined should have the same data type.

6.         Avoid a full-table scan if it is more efficient to get the required rows through an index. It decides full table scan if it has to read more than 5% of the table data (for large tables).

7.         Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows and choose selective indexes.

8.         Indexes can't be used when Oracle is forced to perform implicit datatype conversion.

9.         Choose the join order so you will join fewer rows to tables later in the join order.

l       use smaller table as driving table

l       have first join discard most rows

10.       Set up the driving table to be the one containing the filter condition that eliminates the highest percentage of the table.

11.       In a where clause (or having clause), constants or bind variables should always be on the right hand side of the operator.

12.       Do not use SQL functions in predicate clauses or WHERE clauses or on indexed columns, (e.g. concatenation, substr, decode, rtrim, ltrim etc.) as this prevents the use of the index. Use function based indexes where possible

    SELECT * FROM EMP WHERE SUBSTR (ENAME, 1, 3) = ‘KES’

Use the LIKE function instead of SUBSTR ()

13.       If you want the index used, don’t perform an operation on the field.

Replace

SELECT * FROM EMPLOYEE WHERE SALARY +1000 = :NEWSALARY

With

SELECT * FROM EMPLOYEE WHERE SALARY = :NEWSALARY –1000

14.       All SQL statements will be in mixed lower and lower case.  All reserve words will be capitalized and all user-supplied objects will be lower case. (Standard)

15.       Minimize the use of DISTINCT because it forces a sort operation.

16.       Try joins rather than sub-queries which result in implicit joins

Replace

         SELECT * FROM A WHERE A.CITY IN (SELECT B.CITY FROM B)

With

          SELECT A.* FROM A, B WHERE A.CITY = B.CITY

17.       Replace Outer Join with Union if both join columns have a unique index:

Replace

SELECT A.CITY, B.CITY FROM A, B WHERE A.STATE=B.STATE (+)

With

SELECT A.CITY, B.CITY FROM A, B         WHERE A.STATE=B.STATE

UNION

SELECT NULL, B.CITY FROM B WHERE NOT EXISTS

                                                           (SELECT 'X' FROM A.STATE=B.STATE)

18.       Use bind variables in queries passed from the application (PL/SQL) so that the same query can be reused. This avoids parsing.

19.       Use Parallel Query and Parallel DML if your system has more than 1 CPU.

  

20.       Match SQL where possible. Applications should use the same SQL statements wherever possible to take advantage of Oracle's Shared SQL Area. The SQL must match exactly to take advantage of this.

21.       No matter how many indexes are created, how much optimization is done to queries or how many caches and buffers are tweaked and tuned if the design of a database is faulty, the performance of the overall system suffers. A good application starts with a good design.

22.       The following operations always require a sort:

            SELECT DISTINCT

            SELECT UNIQUE

            SELECT ....ORDER BY...

            SELECT....GROUP BY...

            CREATE INDEX

            CREATE TABLE.... AS SELECT with primary key specification

            Use of INTERSECT, MINUS, and UNION set operators

            Unindexed table joins

            Some correlated sub-queries

Also the order in which the conditions are given in the 'WHERE' cluase are very important while performing a 'Select' query. The Performance Difference is unnoticed ifother wise the query is run on a Massive Database.
For example for a select statement,
SELECT Emp_id FROM Emp_table WHERE Last_Name = 'Smith' AND Middle_Initial = 'K' AND Gender = 'Female';
The look up for matches in the table is performed by taking the conditions in the WHERE cluase in the reverse order i.e., first all the rows that match the criteria Gender = 'Female' are returned and in these returned rows, the conditon Last_Name = 'Smith' is looked up.
There fore, the order of the conditions in the WHERE clause must be in such a way that the last condition gives minimum collection of potential match rows and the next condition must pass on even little and so on. So, if we fine tune the above query, it should look like,
SELECT Emp_id FROM Emp_table WHERE Gender = 'Female' AND Middle_Initial = 'K' AND Last_Name = 'Smith' ; as Last_Name Smith would return far more less number of rows than Gender = 'Female' as in the former case.
66.what is difference between varray and nested table.can u explain in brief and clear my these concepts.also give a small and sweet example of both these.
Varry and Nestead table both are belongs to CollectionsThe Main difference is Varray has Upper bound, where as Nestead table doesn't. It's size is unconstrained like any other database tableNestead table can be stored in DatabaseSyntax of Nestead TableTYPE nes_tabtype IS TABLE OF emp.empno%type;nes_tab nes_tabtype;Syntax of VarryTYPE List_ints_t IS VARRAY(8) OF NUMBER(2);aList List_ints_t:=List_ints_t(2,3,5,1,5,4);
Nested table can be indexed where as VArray can't.
69.  What is PL/Sql tables?Is cursor variable store in PL/SQL table?
pl/sql table is temparary table which is used to store records temrparaily in PL/SQL Block, whenever block completes execution, table is also finished.
71.  What is the DATATYPE of PRIMARY KEY
Binary Integer
72.What is the difference between User-level, Statement-level and System-level Rollback? Can you please give me example of each?
1. System - level or transaction level
   Rollback the current transaction entirely on errors. This was the unique
   behavior of old drivers becauase PG has no savepoint functionality until
   8.0.

2. Statement
   Rollback the current (ODBC) statement on errors (in case of 8.0 or later
   version servers). The driver calls a SAVEPOINT command just before starting
   each (ODBC) statement and automatically ROLLBACK to the savepoint on errors
   or RELEASE it on success. If you expect Oracle-like automatic per statement
   rollback, please use this level.

3. User Level
   You can(have to) call some SAVEPOINT commands and rollback to a savepoint
   on errors by yourself. Please note you have to rollback the current
   transcation or ROLLBACK to a savepoint on errors (by yourself) to continue
   the application
74.  Details about FORCE VIEW why and we can use
Generally we are not supposed to create a view without base table. If you want to create any view without base table that is called as Force View or invalid view.
Syntax: CREATE FORCE VIEW AS < SELECT STATMENT >;
That View will be created with the message
View created with compilation errors
Once you create the table that invalid view will become as valid one.
75.  1) Why it is recommonded to use INOUT instead of OUT parameter type in a procedure?
     2) What happen if we will not assign anything in OUT parameter type in a procedure?
Hi,OUT parameter will be useful for returning the value from subprogram, value can be assigned only once and this variable cannot be assigned to another variable.IN OUT parameter will be used to pass the value to subprogram and as well as it can be used to return the value to caller of subprogram. It acts as explicitly declared variable. Therefore it can be assigned value and its value can be assigned to another variable.So IN OUT will be useful than OUT parameter.

1)      IN OUT and OUT selection criteria depends upon the program need.if u want to retain the value that is being passed then use seperate (IN and OUT)otherwise u can go for IN OUT.2)If nothing is assigned to a out parameter in a procedure then NULL will be returned for that parameter.

78.  What is autonomous Transaction? Where are they used?
Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done.
example using prgma autonomous incase of mutation problem happens in a trigger.
79.  How can I speed up the execution of query when number of rows in the tables increased
Standard practice is -
1. Indexed the columns (Primary key)
2. Use the indexed / Primary key columns in the where clause
3. check the explain paln for the query and avoid for the nested loops / full table scan (depending on the size of data retrieved and / or master table with few rows)
80.  What is the purpose of FORCE while creating a VIEW
usually the views are created from the basetable if only the basetable exists.
The purpose of FORCE keyword is to create a view if the underlying base table doesnot exists.
ex : create or replace FORCE view <viewname> as <query>
while using the above syntax to create a view the table used in the query statement doesnot necessary to exists in the database
83.  What is Mutation of a trigger? why and when does it oocur?
A table is said to be a Mutating table under the following three circumstances
1) When u try to do delete, update, insert into a table through a trigger and at the same time u r trying to select the same table.
2) The same applies for a view
3) Apart from that, if u r deleting (delete cascade),update,insert on the parent table and doing a select in the child tableAll these happen only in a row level trigger
90.  How to handle exception in Bulk collector?
During bulk collect you can save the exception and then you can process the exception.
Look at the below given example:

DECLARE    TYPE NumList IS TABLE OF NUMBER;   

num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);  

 errors  NUMBER;

BEGIN   FORALL i IN num_tab.FIRST..num_tab.LAST 

SAVE EXCEPTIONS     

 DELETE * FROM emp WHERE sal > 500000/num_tab(i);

EXCEPTION WHEN OTHERS THEN    -- this is not in the doco, thanks to JL for pointing this out   

errors := SQL%BULK_EXCEPTIONS.COUNT;   

dbms_output.put_line('Number of errors is ' || errors);  

 FOR i IN 1..errors LOOP         -- Iteration is SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;         -- Error code is SQL%BULK_EXCEPTIONS(i).ERROR_CODE;   

END LOOP;END;

91.#1 What are the advantages and disadvantages of using PL/SQL or JAVA as the primary programming tool for database automation.

#2 Will JAVA replace PL/SQL?

Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".

Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.

PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:

PL/SQL:

  Data centric and tightly integrated into the database

  Proprietary to Oracle and difficult to port to other database systems

  Data manipulation is slightly faster in PL/SQL than in Java

  Easier to use than Java (depending on your background)

Java:

  Open standard, not proprietary to Oracle

  Incurs some data conversion overhead between the Database and Java type systems

  Java is more difficult to use (depending on your background)

110.  

1.What is bulk collect?
2.What is instead trigger
3.What is the difference between Oracle table & PL/SQL table?
4.What R built in Packages in Oracle?
5.what is the difference between row migration & row changing?

1.What is bulk collect?
Bulk collect is part of PLSQL collection where data is stored/ poped up into a variable.
example:
declare
type sal_rec is table of number;
v_sal sal_rec;
begin
select sal bulk collect into v_sal from emp;
for r in 1.. v_sal.count loop
dbms_output.put_line(v_sal(r));
end loop;
end;

2.What is instead trigger
instead triggers are used for views.
3.What is the difference between Oracle table & PL/SQL table?
Table is logical entity which holds the data in dat file permanently .  where as scope of plsql table is limited to the particular block / procedure .  refer above example  sal_rec table will hold data only till programme is reaching to end;

4.What R built in Packages in Oracle?
There R more then 1000 oracle builtin packges like:
Dbms_output,  dbms_utility    dbms_pipe .............

 
5.what is the difference between row migration & row
changing?
Migration: The data is stored in blocks whic use Pctfree 40%
and pctused 60% ( normally). The 40% space is used for update and delete statements .  when a condition may arise that update/delete statement takes more then pctfree then it takes the space from anther block.  this is called migration.
RowChaining: while inserting the data if data of one row takes more then one block then this row is stored in two blocks and rows are chained.

insted of triggers: They provide a transparent way of modifying view that can't be modified directly through SQL,DML statement.

111.Can anyone tell me the difference between instead of trigger, database trigger, and schema trigger?
INSTEAD OF Trigger control operation on view , not table. They can be used to make non-updateable views updateable and to override the behvior of view that are updateable.
Database triggers fire whenever the database startup or is shutdown, whenever a user logs on or log off, and whenever an oracle error occurs. these tigger provide a means of tracking activity in the database
if we have created a view that is based on join codition then its not possibe to apply dml operations like insert, update and delete on that view. So what we can do is we can create instead off trigger and perform dml operations on the view.
131.  HI,What is Flashback query in Oracle9i...?
Flahsback is used to take your database at old state like a system restore in windows. No DDL and DML is allowed when database is in flashback condition.
user should have execute permission on dbms_flashback package
for example:
at 1030 am
from scott user : delete from emp;
commit;
at 1040 am I want all my data from emp table then ?
  declare
  cursor c1 is select * from emp;
  emp_cur emp%rowtype;
   begin
   dbms_flashback.enable_at_time(sysdate - 15/1440);
   open c1;
   dbms_flashback.disable;
   loop
  fetch c1 into emp_cur;
 exit when c1%notfound;
  insert into emp values(emp_cur.empno, emp_cur.ename, emp_cur.job,
  emp_cur.mgr,emp_cur.hiredate, emp_cur.sal, emp_cur.comm,
  emp_cur.deptno);
  end loop;
commit;
 end;
 /
select * from emp;
14 rows selected
132.  what is the difference between database server and data dictionary
Database server  is  collection of all objects of oracle
Data Dictionary contains the information of for all the objects like when created, who created etc.
Database server is a server on which the instance of oracle as server runs..whereas datadictionary is the collection of information about all those objects like tables indexes views triggers etc in a database..
134.  Mention the differences between aggregate functions and analytical functions clearly with examples?
Aggregate functions are sum(), count(), avg(), max(), min()
like: 
select sum(sal) , count(*) , avg(sal) , max(sal) , min(sal) from emp;
analytical fuction differ from aggregate function
some of examples:
SELECT ename "Ename", deptno "Deptno", sal "Sal",
  SUM(sal)
    OVER (ORDER BY deptno, ename) "Running Total",
  SUM(SAL)
    OVER (PARTITION BY deptno
          ORDER BY ename) "Dept Total",
  ROW_NUMBER()
    OVER (PARTITION BY deptno
          ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename

SELECT * FROM (
  SELECT deptno, ename, sal, ROW_NUMBER()
  OVER (
    PARTITION BY deptno ORDER BY sal DESC
  ) Top3 FROM emp
)
WHERE Top3 <= 3

 136.  what are the advantages & disadvantages of packages ?
Modularity,Easier Application Design,Information Hiding,Added Functionality,Better Performance,
Disadvantages of Package - More memory may be required on the Oracle database server when using Oracle PL/SQL packages as the whole package is loaded into memory as soon as any object in the package is accessed.
Disadvantages: Updating one of the functions/procedures will invalid other objects which use different functions/procedures since whole package is needed to be compiled.
we cant pass parameters to packages

137.  What is a NOCOPY parameter? Where it is used?

NOCOPY Parameter Option
Prior to Oracle 8i there were three types of parameter-passing options to procedures and functions:

·         IN: parameters are passed by reference

·         OUT: parameters are implemented as copy-out

·         IN OUT: parameters are implemented as copy-in/copy-out

The technique of OUT and IN OUT parameters was designed to protect original values of them in case exceptions were raised, so that changes could be rolled back. Because a copy of the parameter set was made, rollback could be done. However, this method imposed significant CPU and memory overhead when the parameters were large data collections—for example, PL/SQL Table or VARRAY types.

With the new NOCOPY option, OUT and IN OUT parameters are passed by reference, which avoids copy overhead. However, parameter set copy is not created and, in case of an exception rollback, cannot be performed and the original values of parameters cannot be restored.

Here is an example of using the NOCOPY parameter option:

TYPE Note IS RECORD(   Title VARCHAR2(15),   Created_By VARCHAR2(20),   Created_When DATE,   Memo VARCHAR2(2000));TYPE Notebook IS VARRAY(2000) OF Note;CREATE OR REPLACE PROCEDURE Update_Notes(Customer_Notes IN OUT NOCOPY Notebook) ISBEGIN   ...END;

NOCOPY is a hint given to the compiler, indicating that the parameter is passed as a reference and hence actual value should not be copied in to the block and vice versa. The processing will be done accessing data from the original variable. (Which other wise, oracle copies the data from the parameter variable into the block and then copies it back to the variable after processing. This would put extra burdon on the server if the parameters are of large collections/sizes)

For better understanding of NOCOPY parameter, I will suggest u to run the following code and see the result.
DECLARE
   n NUMBER := 10;
   PROCEDURE do_something (
      n1 IN NUMBER,
      n2 IN OUT NUMBER,
      n3 IN OUT NOCOPY NUMBER) IS
   BEGIN
      n2 := 20;
      DBMS_OUTPUT.PUT_LINE(n1);  -- prints 10
      n3 := 30;
      DBMS_OUTPUT.PUT_LINE(n1);  -- prints 30
   END;
BEGIN
   do_something(n, n, n);
   DBMS_OUTPUT.PUT_LINE(n);  -- prints 20
END;
138.  How to get the 25th row of a table.
select * from Emp where rownum < 26
minus
select * from Emp where rownum<25
SELECT * FROM EMP A WHERE 25=(SELECT COUNT(*) FROM EMP BWHERE A.EMPNO>B.EMPNO);
139.  What is Atomic transaction?
An atomic transaction is a database transaction or a hardware transaction which either completely occurs, or completely fails to occur. A prosaic example is pregnancy - you can't be "halfway pregnant"; you either are or you aren't

140.  What is materialized view?
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. Materialized views, which store data based on remote tables are also, know as snapshots.A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
141.  How to change owner of a table?
Owner of a table is the schema name which holds the table. To change the owner just recreate the table in the new schema and drop the previous table
142.  How can i see the time of execution of a sql statement?
sqlplus >set time on
144.  what happens when commit is given in executable section and an error occurs ?please tell me what ha
Whenever the exception is raised ..all the transaction made before will be commited. If the exception is not raised then all the transaction will be rolled back.
145.  Wheather a Cursor is a Pointer or Reference?
cursor is basically a pointer as it's like a address of virtual memory which is being used storage related to sql query & is made free after the values from this memory is being used
146.  What will happen to an anonymus block,if there is no statement inside the block?eg:-declarebeginend
We cant have
declare
begin
end
we must have something between the begin and the end keywords
otherwise a compilation error will be raised.

147.Can we have same trigger with different names for a table?

eg: create trigger trig1
after insert on tab1;

and

eg: create trigger trig2
after insert on tab1;

If yes,which trigger executes first.
The triggers will be fired on the basis of TimeStamp of their creation in Data Dictionary. The trigger with latest timestamp will be fired at last.
148.creating a table, what is the difference between VARCHAR2(80) and VARCHAR2(80 BYTE)?
Historically database columns which hold alphanumeric data have been defined using the number of bytes they store. This approach was fine as the number of bytes equated to the number of characters when using single-byte character sets. With the increasing use of multibyte character sets to support globalized databases comes the problem of bytes no longer equating to characters.Suppose we had a requirement for a table with an id and description column, where the description must hold up to a maximum of 20 characters.We then decide to make a multilingual version of our application and use the same table definition in a new instance with a multibyte character set. Everything works fine until we try of fill the column with 20 two-byte characters. All of a sudden the column is trying to store twice the data it was before and we have a problem.Oracle9i has solved this problem with the introduction of character and byte length semantics. When defining an alphanumeric column it is now possible to specify the length in 3 different ways: 1. VARCHAR2(20) 2. VARCHAR2(20 BYTE) 3. VARCHAR2(20 CHAR)Option 1 uses the default length semantics defined by the NLS_LENGTH_SEMANTICS parameter which defaults to BYTE. Option 2 allows only the specified number of bytes to be stored in the column, regardless of how many characters this represents. Option 3 allows the specified number of characters to be stored in the column regardless of the number of bytes this equates to.
151.  how to insert a music file into the database
LOB datatypes can be used to store blocks of unstructured data like graphic images, video, audio, etc
152.  what is diff between strong and weak ref cursors
A strong REF CURSOR type definition specifies a return type, but a weak definition does not.
DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;  -- strong
   TYPE GenericCurTyp IS REF CURSOR;  -- weak
 in a strong cursor structure is predetermined --so we cannot query having different structure other than emp%rowtype
in weak cursor structure is not predetermined -- so we can query with any structure

Strong Ref cursor type is less Error prone, because oracle already knows what type you are going to return as compare to weak ref type.
154.  Explain, Is it possible to have same name for package and the procedure in that package.
Yes, its possible to have same name for package and the procedure in that package.
159.  Without closing the cursor, If you want to open it what will happen. If error, get what is the error
If you reopen a cursor without closing it first,PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN.

161.  What is PRAGMA RESTRICT_REFERENCES:
By using pragma_restrict_references we can  give the different status to functions,Like WNDB(WRITE NO DATA BASE),RNDB(read no data base),Write no package state,read no packge state.W
164.  What is difference between PL/SQL tables and arrays?
array is set of values of same datatype.. where as tables can store values of diff datatypes.. also tables has no upper limit where as arrays has.
168.  How do you set table for read only access ?
If you update or delete the records in the table, at the same time, no body can update or delete the same records which you updated or deleted because oracle lock the data which u updated or deleted.

Select for update

169.  What are the disadvantages of Packages and triggers??

Disadvantages of Packages:
1. You cannot reference remote packaged variables directly or indirectly..
2. Inside package you cannot reference host variable..
3. We are not able to grant a procedure in package..

Disadvantages of Trigger:
1. Writing more number of codes..

170.  How to disable a trigger for a particular table ?

alter trigger <trigger_name> disable

172.  how can we avoid duplicate rows. without using distinct command

Using Self join like :
  select dup.column from tab a,tab b where a.dup.column=b.dup.column and a.rowid<>b.rowid

This query will return the first row for each unique id in the table. This query could be used as part of a delete statement to remove duplicates if needed.

SELECT ID

FROM func t1

WHERE ROWID = (SELECT MIN (ROWID)

               FROM func               WHERE ID = t1.ID)

Also: You can use a group by without a summary function
SELECT ID

FROM func t1

GROUP BY id

173.  Why we use instead of trigger. what is the basic structure of the instead of trigger. Explain speci

Conceptually, INSTEAD OF triggers are very simple. You write code that the Oracle server will execute when a program performs a DML operation on the view. Unlike a conventional BEFORE or AFTER trigger, an INSTEAD OF trigger takes the place of, rather than supplements, Oracle's usual DML behavior. (And in case you're wondering, you cannot use BEFORE/AFTER triggers on any type of view, even if you have defined an INSTEAD OF trigger on the view.)
CREATE OR REPLACE TRIGGER images_v_insert
INSTEAD OF INSERT ON images_v
FOR EACH ROW
BEGIN
   /* This will fail with DUP_VAL_ON_INDEX if the images table
   || already contains a record with the new image_id.
   */
   INSERT INTO images
      VALUES (:NEW.image_id, :NEW.file_name, :NEW.file_type,
              :NEW.bytes);

   IF :NEW.keywords IS NOT NULL THEN
      DECLARE
         /* Note: apparent bug prevents use of :NEW.keywords.LAST.
         || The workaround is to store :NEW.keywords as a local
         || variable (in this case keywords_holder.)
         */
         keywords_holder Keyword_tab_t := :NEW.keywords;
      BEGIN
         FOR the_keyword IN 1..keywords_holder.LAST
         LOOP
            INSERT INTO keywords
            VALUES (:NEW.image_id, keywords_holder(the_keyword));
         END LOOP;
       END;
   END IF;
END;

Once we've created this INSTEAD OF trigger, we can insert a record into this object view (and hence into both underlying tables) quite easily using:

INSERT INTO images_v VALUES (Image_t(41265, 'pigpic.jpg', 'JPG', 824,         Keyword_tab_t('PIG', 'BOVINE', 'FARM ANIMAL')));

This statement causes the INSTEAD OF trigger to fire, and as long as the primary key value (image_id = 41265) does not already exist, the trigger will insert the data into the appropriate tables.

Similarly, we can write additional triggers that handle updates and deletes. These triggers use the predictable clauses INSTEAD OF UPDATE and INSTEAD OF DELETE.

180.  what is the difference between database trigger and application trigger?
Database triggers are backend triggeres and perform as any event occurs on databse level (ex. Inset,update,Delete e.t.c) wheras application triggers are froentend triggers and perform as any event taken on application level (Ex. Button Pressed, New Form Instance e.t.c)
185.  Compare EXISTS and IN Usage with advantages and disadvantages.
exist is faster than IN Command

exist do full table scan...so it is faster than IN
Use Exists whenever possible. EXISTS only checks the existence of records (True/False), and in the case of IN each and every records will be checked.  performace wise EXISTS is better.
189.  Which type of binding does PL/SQL use?
it uses latebinding so only we cannot use ddl staments directly
it uses dynamicbinding
191.  Why DUAL table is not visible?
Because its a dummy table.