The following technical interview questions and answers are as per my
experiences while working in Oracle PL/SQL projects. These may be useful for
the freshers and experienced developers whenever facing PL/SQL interview.
Ques: 1. What
is a mutating table error? How can you get this error?
Answer:
This error can
come with triggers, whenever a 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. This is all because of the table is in middle of a transaction and
referencing the same table again in the middle of the updating action causes
the trigger to mutate.
Oracle
Fusion Applications interview Questions and Answers
Ques: 2. What is the key difference between SQL and PL/SQL?
Answer:
SQL and PL/SQL
are used to access data within Oracle databases. SQL is a limited language that
allows you to directly interact with the database. You can write queries,
manipulate objects and data of oracle database with SQL language. SQL doesn't
include the programming concepts.
By using PLSQL,
you can extract and manipulate the objects and data of oracle database. And can
do all the things that normal programming languages can have, such as looping
and controlled executions.
Oracle Accounts Payables Interview Questions and Answers
Ques: 3. What
is the difference between Form triggers and Database level triggers?
Answer:
Form level
triggers are use in forms and fire on any level like item level, row level or
on block level on requirement of application. And database triggers are written
in database directly and fire on behalf of any transaction like Insert, Update
and delete on table automatically.
The key
difference in form level triggers and database trigger is that form level
trigger fire on user or application requirement and database triggers will fire
automatically.
Oracle ADF Interview Questions and Answers
Ques:
4. What is Pseudo column?
Answer:
Pseudo columns
are database columns which are used for different purposes in oracle database.
ROWNUM, ROWID, SYSDATE,
UID, USER, ORA_ROWSCN, SYSTIMESTAMP are pseudo columns in oracle database.
Oracle Access Manager Interview Questions and Answers
Ques: 5. What is exception handling in oracle? How can they be
handled?
Answer:
When an error
occurs, exception is raised normally, and execution is stopped. Control
transfers to exception handling part. Exception is an error situation which
arises during program execution. Exception handlers are routines written to
handle the exception. The exceptions can be internally defined User-defined
exception. In oracle, exception can be handled by using these exception
statements:
EXCEPTION WHEN
DUP_VAL_ON_INDEX
NOT_LOGGED_ON
TOO_MANY_ROWS
VALUE_ERROR
NO_DATA_FOUND
Oracle Fusion HCM Interview Questions and Answers
Ques:
6. What is the key difference between OPEN-FETCH-CLOSE and FOR LOOP in
CURSOR?
Answer:
A FOR LOOP in
cursor implicitly declares its loop index as a %ROWTYPE record, opens a cursor,
repeatedly fetches rows of values from the result set into fields in the
record, and closes the cursor when all rows have been processed.
While using the
OPEN-FETCH-CLOSE, we have to explicitly open the query and closing the query.
Oracle SCM Interview Questions and Answers
Ques:
7. What is Dynamic SQL? How can we use it in Oracle?
Answer:
Dynamic SQL is used by PL/SQL to execute Data Definition Language
(DDL) statements, Data Control (DCL) statements, or Transaction Control
statements within PL/SQL blocks. These statements can, probably will change
from execution to execution means change at runtime. These statements are
not stored within the source code but are stored as character variables in the
program.
The
SQL statements are created dynamically at runtime by using variables. This is
used either using native dynamic SQL or through the DBMS_SQL package. Dynamic
SQL supports all SQL data types.
Oracle Financials Interview questions and Answers
Ques:
8. What is the key difference Between Row Level Trigger and Statement
Level Trigger?
Answer:
Row level
trigger executes once for each row after or before in the DML
event. It can be defined by using FOR EACH ROW.
Statement Level
trigger executes once after or before the DML event, it doesn’t matter many
rows are affected by the DML event.
Oracle Cloud Interview Questions and Answers
Ques: 9. What are the various steps included in the compilation process of a PL/SQL block?
Answer:
In the
compilation process of a PL/SQL block, the syntax checking, binding, and p-code
generation are involved. Syntax checking involves checking PL/SQL code for
compilation errors. Syntax errors have been corrected, a storage address is
assigned to the variables that are used to hold data for Oracle. This process
is called binding. After binding, P-code is generated for the PL/SQL block.
P-code is a list of instructions to the PL/SQL engine. For named blocks, p-code
is stored in the database, and it is used the next time the program is
executed.
Oracle SQL
Interview Questions and Answers
Ques: 10. What packages have Oracle provided to PLSQL developers?
Answer:
Oracle provides
the DBMS_ series of packages to PLSQL developers to smooth the programming. The
below packages, the developer should be aware of:
DBMS_DDL
UTL_FILE
DBMS_OUTPUT
DBMS_JOB
DBMS_SQL
DBMS_PIPE
DBMS_TRANSACTION
DBMS_LOCK
DBMS_ALERT
DBMS_UTILITY
Oracle RDMS Interview Questions and Answers
Ques:
11. How can you protect your PL/SQL source code?
Answer:
Oracle provides
a binary wrapper utility that can be used to scramble PL/SQL source code. This
utility was introduced in Oracle 7.
This utility
use human-readable PL/SQL source code as input and writes out portable binary
object code. It can larger than the original in size. The binary code can be
distributed without fear of exposing your used algorithms and methods. Oracle
will still understand and know how to execute the code. Be careful, there is no
"decode" command available. So, always keep your source code saved.
BI Publisher Interview Questions and Answers
Ques:
12. What are SQLCODE and SQLERRM in PLSQL? What is their importance in
programming in PLSQL?
Answer:
In PLSQL,
SQLCODE returns the value of the error number for the last encountered error.
Whereas the SQLERRM returns the actual error message for the last encountered
error. They are used in exception handling in PLSQL. These are very useful for
the WHEN OTHERS exception.
Oracle 10g Interview Questions and Answers
Ques: 13. What do you understand by cursors in PLSQL?
Answer:
Cursor is a
pointer variable in a memory and use for data manipulation operations in PLSQL.
Basically, cursor is a private SQL memory area. It is also used to improve the
performance of the PLSQL block.
Two types of cursors are:
a). Implicit cursor: Implicit cursors use oracles to manipulate the data
manipulation operations internally and developers have no control on this type
of cursor. We use sql%notfound and sql%rowcount cursor attributes in implicit
cursor.
b). Explicit cursor: Explicit cursors are created by the developers and can
control it by using the Fetch, Open and close keywords.
There are five types of cursors attributes in PLSQL:
1). %isopen: used to verify whether this cursor is open or not.
2). %found: If cursor fetch the data then %found return true.
3). %notfound: If cursor fetches not data then %notfound return true.
4). %rowcount: It return no. of rows that are in cursor.
5). %bulk_rowcount: It is same as %rowcount but it is used in bulk.
Ques: 14. What is the Index-By-Tables in PLSQL?
Answer:
Index-By-Tables
is also known as Associative Arrays. These are the sets of key-value pairs,
where each key is unique and is used to locate a corresponding value in the
array. The key can be an integer or a string.
Syntax : TYPE tab_type_name IS TABLE OF element type
INDEX BY BINARY_INTEGER;
Ex: TYPE DeptTabTyp IS TABLE OF Dept%ROWTYPE
INDEX
BY BINARY_INTEGER;
Dept_tab
DeptTabTyp;
Ques: 15. What
do you understand by bulk binding?
Answer:
The bulk
binding technique improves performance by minimizing the number of context
switches between the PL/SQL and SQL engines. A DML operation statement can
transfer all the elements of a collection in a single operation.
For example, If the collection has 100 elements, It lets you to perform the 100 SELECT, INSERT, UPDATE, or DELETE statements using a single operation.
Ques: 16. What do you under by AUTONOMOUS_TRANSACTION?
Answer:
The pragma
AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a PLSQL block as
autonomous i.e. independent. Autonomous transactions let the compiler stop the
main transaction to do DML operations, commit or roll back those operations,
then resume the main transaction.
Ques:
17. What are the two components of LOB datatype in PLSQL?
Answer:
The two
components of the LOB datatype in PLSQL are:
1). LOB locator: LOB Locator is a locator, which points to the location in the database where the actual value is stored. This value is stored along with the record in the table row and is like a pointer to the actual location of LOB value.
2). LOB value: It is referred to an actual image, file or value of the LOB datatype.
Ques: 18. What are cascading of triggers?
Answer:
If we insert
data in one table and that table have trigger on it then trigger fire. And in
this trigger, there is another table that we are using for inserting the data
in it and this table has also trigger on it then this trigger also fire. This
is called cascading of triggers.
Ques: 19. What
do you understand by Table Functions in PLSQL?
Answer:
We can use the
table function like the name of the database table. Table functions are
functions that produce a collection of rows (either a nested table or a Varray)
that can be queried like a physical database table or assigned to PL/SQL
collection variable.
Ques: 20. What
is the use of NOCOPY in PLSQL?
Answer:
In PLSQL
programming, the NOCOPY is Compiler Hint. When the Parameters hold large data
structures such as collections and records, all this time copying slows down
the execution. To prevent this, we can specify NOCPY. This allows the PL/SQL
Compiler to pass OUT and INOUT parameters by reference.
