PLSQL interview questions - part1
oracle pl sql interview questions for 3+ years experience
1. Tell me about yourself
2. What is difference between TRUNCATE & DELETE?
1. Truncate is a DDL command
2. We can remove bulk amount of records at a time
3. We can't rollback the records
4. Release the space in database
5. Truncate reset the high water mark
6. Truncate explicitly commit
1. Delete is a DML command
1. Delete is a DML command
2. We can delete record by record
3. We can rollback the records
4. Can’t release the memory in database
5. Delete can’t reset the water mark
6. Delete implicitly commit
(OR)
(OR)
Ans: Differences:
TRUNCATE commits after deleting entire table i.e., cannot be rolled back.
Database triggers do not fire on TRUNCATE DELETE allows the filtered deletion.
Deleted records can be rolled back or committed.Database triggers fire on DELETE.
3. Difference between view and materialized view
Difference
View is a logical table
View can hold the query
We can’t create indexes on view
View will create security purpose
Mv is a physical table
Mv can hold the query with refresh data
We can create indexes on mv
Mv will create performance issues
4. Difference between procedure and function?
Procedure:
Procedure allow the DML statements without any restrictions
Procedure allow the DML statements without any restrictions
We can’t call procedure in sql language
We can store images in stored procedure
Function:
Function not allow the DML statements (If you need to use we can use pragma)
Function not allow the DML statements (If you need to use we can use pragma)
We can call Function in sql language
Function can’t store images
5. What is cursor?
Cursor is private sql area which is used to execute sql statements and store processing information
6. What is explicit and implicit cursor and examples?
The implicit cursor is automatically declared by oracle every time an sql statement is executed whenever you issue a sql statement, the oracle server opens an area of memory in which the command is parsed and executed. Every implicit cursor attribute start with sql%.
An explicit cursor is created and managed by the user. And used for multi row select statement.
7.What do u understand by database and what is objects in oracle
Ans: A database is defined as a collection of meaningful data. Objects in oracle means Table, Views, Procedures, Triggers, Synonym etc
8.What is a table, view, snapshot?
Table: A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.a
Views: A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
Snapshot: A Snapshot is a recent copy of a table from database or in some cases ,a subset of rows/columns of a table. It is also known as Materialized view.
9.Do a view contain data?
Ans: Views do not contain or store data
What are the advantages of views?
Ans: Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.
10.What is an Oracle sequence?
Ans: A Sequence generates a serial list of unique numbers for numerical columns of a database's tables.
11.What is a synonym?
Ans: A synonym is an alias for a table, view, sequence or program unit.
12.What are the types of synonyms?
Ans: There are two types of synonyms private and public.
13.What is a private synonym?
Ans: Only its owner can access a private synonym.
14.What is a public synonym?
Ans: Any database user can access a public synonym
15.What is an Oracle index?
Ans: An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table. Index may also be considered as a ordered list of content of a column.
16.What is a schema?
Ans: The set of objects owned by user account is called the schema.
17.What is a join? Explain the different types of joins?
Ans: Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by not equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.
18.Difference between SUBSTR and INSTR?
Ans: INSTR (String1, String2 (n, (m)), INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m) SUBSTR returns a character string of size m in string1, starting from n-th position of string1.
19.What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
Ans: CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR the maximum length is 255 and 2000 for VARCHAR2
20.How to access the current value and next value from a sequence?
Ans: Current Value : Sequence name.CURRVAL
PLSQL interview questions - part5
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.
PLSQL interview questions - part4
oracle pl sql interview questions for 3+ years experience
51.What is
“Check Constraints” and “with check options” and “Default Specification”?
Ans: CHECK
Integrity Constraints: A CHECK integrity constraint on a column or a set of
columns requires that a specified condition be true or unknown (ie. Not false)
for every row of the table. If a DML statement is issued so that the condition
of the CHECK constraint evaluates to false, the statement is rolled back. With
check Option: With Check option restricts inserts and updates performed through
the view to prevent them from creating rows that the view cannot itself select
.based on where clause of the create view statement. For eg: Create or replace
view Women As select name from Employee Where Sex= ‘Female’ With Check Option;
Default Specification It supplies a default value if column value is not
specified on INSERT It can contain literals (constants) and SQL functions,
USER, SYSDATE, sequence It cannot include references to any columns.
52.What is
the maximum no. Of columns a table can have ?
Ans:
254(Oracle)
53.Can a
trigger written for a view ?
Ans: No
Consider a
sequence whose currval is 1 and gets incremented by 1 by using the nextval
reference we get the next number 2. Suppose at this point we issue an rollback
and again issue a nextval. What will the output be ?
Ans: 3
54.Can you
create index on view ?
Ans: No
55.What is
the difference between alias and synonym ?
Ans: Alias
is temporary and used with one query. Synonym is permanent and not used as
alias.
What’s the
length of SQL integer ?
Ans: 32 bit
length
56.What is
tkprof and how is it used?
Ans: The
tkprof tool is a tuning tool used to determine cpu and execution times for SQL
statements. You use it by first setting timed_statistics to true in the
initialization file and then turning on tracing for either the entire database
via the sql_trace parameter or for the session using the ALTER SESSION command.
Once the trace file is generated you run the tkprof tool against the trace file
and then look at the output from the tkprof tool . This can also be used to
generate explain plan output.
57.What is
explain plan and how is it used?
Ans: The
EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have
an explain_table generated in the user you are running the explain plan for.
This is created using the utlxplan.sql script. Once the explain plan table
exists you run the explain plan command giving as its argument the SQL
statement to be explained. The explain_plan table is then queried to see the
execution plan of the statement. Explain plans can also be run using tkprof.
58.What is
The Dynamic Performance Tables?
Ans:
Throughout its operation, ORACLE maintains a set of "virtual" tables
that record current database activity. These tables are called Dynamic
performance tables. Because dynamic performance tables are not true tables,
they should not be accessed by most users. However, database administrators can
query these tables and can create views on the tables and grant access to those
views to other users. The dynamic performance tables are owned by SYS and their
names all begin with V_$. Views are created on these tables, and then synonyms
are created for the views. The synonym names begin with V$.
59.What is
Savepoint ?
Ans:
Savepoints are intermediate markers that can be declared in long transactions
that contain many SQL statements. By using savepoints, you can arbitrarily mark
your work at any point within a long transaction. This allows you the option of
later rolling back all work performed from the current point in the transaction
to a declared savepoint within the transaction.
60.What is
Deadlocks?
Ans: A
deadlock is a situation that can occur in multi-user systems that causes some
number of transactions to be unable to continue work. A deadlock can occur when
two or more users are waiting for data locked by each other. It typically
happens when each of two or more users are waiting to access a resource that
another user has already locked. This creates a deadlock situation because each
user is waiting for resources held by the other user. Eg Transaction 1 Time
Point Transaction 2 UPDATE emp 1 UPDATE emp SET sal = sal*1.1 SET sal = 1342
WHERE empno = 1000; WHERE empno = 2000; UPDATE emp 2 UPDATE emp SET sal =
sal*1.1 SET sal = 1342 WHERE empno = 2000; WHERE empno = 1000; ORA-00060 3
deadlock detected while waiting for resource
61.What is
Privilege ?
Ans: A
privilege is a right to execute a particular type of SQL statement or to access
another user's object. Types of privileges : • system privileges • object
privileges System Privileges : System privileges allow users to perform a
particular systemwide action, or to perform a particular action on a particular
type of object. E.g. Create Tablespace, Delete the row of any table, etc.
Object Privileges : Object privileges allow users to perform a particular
action on a specific object. E.g. Delete row of specific table, etc. Roles :
Roles are named groups of related privileges that are granted to users or other
roles. Advantages of Roles : 1. Reduced granting of privileges 2. Dynamic
privilege management (Changing of privileges) 3. Selective availability of
privileges (Enalbling/Disabling roles) 4. Application awareness
(Enalbling/Disabling of roles by application)
62.What is
Two Phase Commit ?
Ans: Two
Phase Commit is a mechanism wherein ORACLE automatically controls and monitors
the commit or rollback of a distributed transaction and maintains the integrity
of the global database. The Phases of the Two-Phase Commit Mechanism :
• Prepare phase : The global co-ordinator (initiating node) asks participants to prepare (to promise to commit or rollback the transaction, even if there is a failure).
• Commit phase : If all participants respond to the co-ordinator that they are prepared, the co-ordinator asks all nodes to commit the transaction; if all participants cannot prepare, the co-ordinator asks all nodes to roll back the transaction.
• Prepare phase : The global co-ordinator (initiating node) asks participants to prepare (to promise to commit or rollback the transaction, even if there is a failure).
• Commit phase : If all participants respond to the co-ordinator that they are prepared, the co-ordinator asks all nodes to commit the transaction; if all participants cannot prepare, the co-ordinator asks all nodes to roll back the transaction.
63.Explain
about snapshots in detail?
Ans:
Snapshots are read-only copies of a master table (or multiple tables) located
on a remote node. A snapshot can be queried, but not updated; only the master
table can be updated. A snapshot is periodically refreshed to reflect changes
made to the master table. A snapshot is a full copy of a table or a subset of a
table that reflects a recent state of the master table. A snapshot is defined
by a distributed query that references one or more master tables, view, or other
snapshots. Simple vs. Complex Snapshots : Each row in a simple snapshot is
based on a single row in a single remote table. Therefore, a simple snapshot's
defining query has no GROUP BY or CONNECT BY clauses, or subqueries, joins, or
set operations. If a snapshot's defining query contains any of these clauses or
operations, it is referred to as a complex snapshot. Internals of Snapshot
Creation: When a snapshot is created, several operations are performed
internally by ORACLE: • ORACLE (at the snapshot node) creates a table to store
the rows retrieved by the snapshot's defining query; this is the snapshot's
base table. • ORACLE creates a read-only view on the SNAP$ table (base table)
for queries issued against the snapshot. • ORACLE creates a second local view
on the remote master table. It uses this view when it refreshes the snapshot. •
Additionally, if the snapshot is a simple snapshot, ORACLE creates an index on
the SNAP$ table. All of these internal objects are created in the schema of the
snapshot. Do not alter, change data in, or delete these objects manually.
64.What is
Ref Cursor?
Ans: A REF
CURSOR is basically a data type. A variable created based on such a data type
is generally called a cursor variable. A cursor variable can be associated with
different queries at run-time. The primary advantage of using cursor variables
is their capability to pass result sets between sub programs (like stored
procedures, functions, packages etc.).
65.What is
row chaining, how does it happen?
Ans: Row
chaining occurs when a VARCHAR2 value is updated and the length of the new
value is longer than the old value and won’t fit in the remaining block space.
This results in the row chaining to another block. It can be reduced by setting
the storage parameters on the table to appropriate values. It can be corrected
by export and import of the effected table.