oracle pl sql interview questions for 3+ years experience
66.Describe
hit ratio as it pertains to the database buffers. What is the difference
between instantaneous and cumulative hit ratio and which should be used for
tuning?
Ans: The
hit ratio is a measure of how many times the database was able to read a value
from the buffers verses how many times it had to re-read a data value from the
disks. A value greater than 80-90% is good, less could indicate problems. If
you simply take the ratio of existing parameters this will be a cumulative
value since the database started. If you do a comparison between pairs of
readings based on some arbitrary time span, this is the instantaneous ratio for
that time span. An instantaneous reading gives more valuable data since it will
tell you what your instance is doing for the time it was generated over.
67.What is a
Cartesian product?
Ans: A
Cartesian product is the result of an unrestricted join of two or more tables.
The result set of a three table Cartesian product will have x * y * z number of
rows where x, y, z correspond to the number of rows in each table involved in
the join.
68.What is a
mutating table error and how can you get around it?
Ans: This
happens with triggers. It occurs because the trigger is trying to update a row
it is currently using. The usual fix involves either use of views or temporary
tables so the database is selecting from one while updating the other.
69.What are
SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Ans:
SQLCODE returns the value of the error number for the last error encountered.
The SQLERRM returns the actual error message for the last error encountered.
They can be used in exception handling to report, or, store in an error log
table, the error that occurred in the code. These are especially useful for the
WHEN OTHERS exception.
70.What are
Transactional Triggers ? Give the uses of Transational Trigger ?
Ans:
Transactional Triggers fire in response to transaction processing events. These
events represent points during application processing at which Oracle Forms
needs to interact with the data source. Examples of such events include
updating records, rolling back to savepoints, and committing transactions. By
default, Oracle Forms assumes that the data source is an ORACLE database, and
issues the appropriate SQL statements to optimize transaction processing
accordingly. However, by defining
transactional
triggers and user exits, you can build a form to interact with virtually any
data source, including even non-relational databases and flat files. Calling
User Exits When you define transactional triggers to interact with a non-ORACLE
data source, you will usually include a call to a user exit in the appropriate
triggers. The code in your user exit interacts with the non-ORACLE data source.
Once the user exit has performed the appropriate function (as indicated by the
trigger from which it was called), it returns control to Oracle Forms for
subsequent processing. For example, a user exit called from an On-Fetch trigger
might be responsible for retrieving the appropriate number of records from the
non-ORACLE data source. Once the records are retrieved, Oracle Forms takes over
the display and management of those records in the form interface, just as it would
if the records had been fetched from an ORACLE database. Uses for Transactional
Triggers • Transactional triggers, except for the commit triggers, are
primarily intended to access certain data sources other than Oracle. • The
logon and logoff transactional triggers can also be used with Oracle databases
to change connections at run time.
71.What is
Autonomous transaction ? Where do we use it?
Ans: In
Oracle's database products, an autonomous transaction is an independent
transaction that is initiated by another transaction. It must contain at least
one Structured Query Language (SQL) statement. Autonomous transactions allow a
single transaction to be subdivided into multiple commit/rollback transactions,
each of which will be tracked for auditing purposes. When an autonomous
transaction is called, the original transaction (calling transaction) is
temporarily suspended. The autonomous transaction must commit or roll back
before it returns control to the calling transaction. Once changes have been
made by an autonomous transaction, those changes are visible to other
transactions in the database. Autonomous transactions can be nested. That is,
an autonomous transaction can operate as a calling transaction, initializing
other autonomous transactions within itself.
72.What is a
package, procedure and function?
Ans:
Package : A package is a group of related program objects stored together as a
unit in the database. A package is an encapsulated collection of related
program objects stored together in the database. Program objects are:
procedures, functions, variables, constants, cursors, exceptions.
Procedure/Function : A procedure or function is a set of SQL and PL/SQL
statements grouped together as an executable unit to perform a specific task.
The main difference between a procedure and function is functions return a
single variable by value whereas procedures do not return any variable by
value. Rather they return multiple variables by passing variables by reference
through their OUT parameter.
73.What do u mean by overloading?
Ans:
Function Overloading : Packages allow you to overload procedures or functions.
Overloading a procedure means creating multiple procedures with the same name
in the same package, each taking arguments of different number or datatype.
74.What are the constructs of a
procedure, function or a package ?
Ans: The
constructs of a procedure, function or a package are : • variables and constants
• cursors • exceptions
75.What are cascading triggers? What is
the maximum no of cascading triggers at a time?
Ans: When
a statement in a trigger body causes another trigger to be fired, the triggers
are said to be cascading. Max = 32
76.What is the significance of the &
and && operators in PL/SQL ?
Ans: The
& operator means that the PL SQL block requires user input for a variable.
The && operator means that the value of this variable should be the
same as inputted by the user previously for this same variable.
77.If all the values from a cursor have
been fetched and another fetch is issued, the output will be?
Ans: Last
Record
78.What is a forward declaration ? What
is its use ?
Ans:
PL/SQL requires that you declare an identifier before using it. Therefore, you
must declare a subprogram before calling it. This declaration at the start of a
subprogram is called forward declaration. A forward declaration consists of a
subprogram specification terminated by a semicolon.
79.Any three PL/SQL Exceptions?
Ans:
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
80.Describe
the use of %ROWTYPE and %TYPE in PL/SQL
Ans:
%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE
associates a variable with a single column type.
81.How can
you call a PL/SQL procedure from SQL?
Ans: By
use of the EXECUTE (short form EXEC) command.
82.What are
the various types of Exceptions ?
Ans: User
defined and Predefined Exceptions.
83.What is
RAISE_APPLICATION_ERROR ?
Ans:
DBMS_STANDARD provides a procedure named raise_application_error, which lets
you issue user-defined error messages. That way, you can report errors to an
application and avoid returning unhandled exceptions. The calling syntax is :
raise_application_error(error_number, error_message); where error_number is a
negative integer in the range -20000...-20999 and error_message is a character
string up to 2048 bytes in length. An application can call
raise_application_error only from an executing stored subprogram. When called,
raise_application_error ends the subprogram, rolls back any database changes it
made, and returns a user-defined error number and message to the application.
The error number and message can be trapped like any ORACLE error. The calling
application gets a PL/SQL exception, which it can process using the
error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. • The
statement Raise_Application_Error can be called either from a procedure body or
from an exception handler. • Irrespective of whether an error occurred or not,
a raise_application_error command always raises an exception in the calling
program (eg a forms trigger). If an exception handler is not written in that
forms trigger, then a forms error occurs.
No comments:
Post a Comment