Top 20 Oracle PL/SQL Interview Questions and Answers

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.

Top 20 Python Interview Questions and Answers

 
Ques: 1. So, what is Python?
 
Answer:
 
Python is an open source programming language that is widely used as one of the most popular interpreted languages. Objects, modules, threads, exceptions, dynamic typing, very high-level dynamic data types, classes, and memory management are all included. It's easy to use, portable, expandable, interpreted, interactive, object-oriented, and has a built-in data structure. It's also open source. It's paired with a very simple syntax. Python is portable and scriptable, but it is mostly thought of as a general-purpose programming language.
 

BlockChain interview Questions and Answers

 
Ques: 2. What are the benefits of using Python?
 
Answer:
 

• Python is one of the most success interpreted languages. Means that, Python scripts does not need to be compiled before it is run.
• Python is an object oriented as it allows the definition of classes along with composition and inheritance.
• In Python, functions and classes are first-class objects. It means that they can be assigned to variables, returned from other functions and passed into functions.
• Python is dynamically typed, means that no need to state the types of variables when you declare them. You can declare variables without error like a=10 and then a="a good programmer".
• Python code is very quick to write. The numpy package is a good example of this, it is very quick because a lot of the number crunching in it.
• Python has a vast area to use in web applications, automation, scientific modelling, big data applications and many more.
• It’s also often used as inclusive code to get other languages and components to use.

 
 
Python Pandas Interview and Questions
 
 
Ques: 3. What do you know about PEP 8?
 
Answer:
 
PEP is short for Python Enhancement Proposal. It defines a set of rules that specify how to format Python code for maximum readability. It is the latest Python coding standard, a set of coding recommendations. It guides to deliver more readable Python code.
 

C language Interview Questions and Answers

 
Ques: 4. How does the Conversion from number to string happens in Python?
 
Answer:
 
To convert, the number to string, in Python, use the built-in function str(). For hexadecimal or octal representation conversion, use the built-in functions hex() or oct(). And for fancy formatting, use the % operator on strings, e.g. "%04d" % 144 yields '0144' and "%.3f" % (1/3.0) yields '0.333'.
 

C++ language Interview Questions and Answers


Ques: 5. What Is Class and method in Python? How will you use them?
 
Answer:
 
A class can be based on one or more other classes, called its base class(es). It then inherits the attributes and methods of its base classes. This allows an object model to be successively refined by inheritance.  A class is the object type created by executing a class statement. Class objects are used as templates to create instance objects, which embody both the data (attributes) and code (methods) specific to a data type.
 
A method is a function on some object x that you normally call as x.role(arguments...). Methods are defined as functions inside the class definition:
class C:
def names (self, arg):
return arg*2 + self.attribute.
 

Machine Learning Interview Questions and Answers

 
Ques: 6. What are the some of the core default modules available in Python?
 
Answer:
 
There are a few of the core default modules available in Python.
 
    XML – Enables XML support.
    string – Contains functions to process standard Python strings.
    traceback – Allows to extract and print stack trace details.
    email – Help to parse, handle, and generate email messages.
    sqlite3 – Provides methods to work with the SQLite database.
    logging – Adds support for log classes and methods.
 

MySQL Interview Questions and Answers

 
Ques: 7. What Is Self in Python?
 
Answer:
 
Self is just a name for the first argument of a method. A method defined as name(self, a, b, c) should be called as x.name(a, b, c) for some instance x of the class in which the definition occurs; the called method will think it is called as name(x, a, b, c).
 

PHP Interview Questions and Answers

 
Ques: 8. How Do I Generate Random Numbers In Python?
 
Answer:
 
The standard module random implements a random number generator. Usage is simple:
import random
random.random()
This returns a random floating point number in the range [0, 1).
But you can also generate random numbers in Python in different ways:
· uniform(X, Y) - It returns a floating point number between the values given as X and Y.
· randint(X, Y) - This command returns a random integer between the values given as X and Y.
 

PowerShell Interview Questions and Answers

 
Ques: 9. In Python, how memory is managed?
 
Answer:
 
The memory is managed by private heap space in Python. All objects and data structures located in a private heap. The developer/coder/programmer does not have any access to the private heap and interpreter will takes care of Python private heap in memory. The Python memory manager will allocate Python heap space for Python objects. The Python core APIs give access to some tools for the programmer to code. There is an inbuilt garbage collector in Python, which recycle all the unused memory and frees the memory and makes it available to the heap space.
 
 

SQL Server Interview Questions and Answers

Ques: 10. What are the available tools to search the bugs or perform static analysis?
 
Answer:
 
PyChecker is a static analysis tool that searches the bugs in source code and gives warnings about the complexity and style of the bug. And Pylint is another tool that checks whether the module satisfies the coding standard and makes sure to write plug-ins to add a custom feature.
 

Unix interview Questions and Answers

 
Ques: 11. Can you explain the rules For Local and Global Variables in Python?
 
Answer:
 
Variables are only referenced inside a function and are implicitly global. A local variable is assigned a new value anywhere within the function's body. The variable is implicitly local, if a variable is assigned a new value inside the function. Otherwise, you need to explicitly declare it as 'global'. If global was required for all global references, you'd be using global all the time. You'd have to declare as global every reference to a built-in function or to a component of an imported module.
 
 
Ques: 12. What do you know about The Dictionary Function in Python?
 
Answer:
 
In python, you have to associate keys with values. Key should be unique because it is useful for retrieving information in Python. A dictionary is a place where you will find and store information on address, contact details, etc. In Python, the strings will be passed as keys.
 
Keys must be separated by a colon and the pairs are separated themselves by commas. And the whole statement will be enclosed in curly brackets.
 
 
Ques: 13. Can you explain the Sharing of Global Variables Across Modules in Python?
 
Answer:
 
The canonical way to share information across modules within a single program is to create a special module (often called config or cfg). Just import the config module in all modules of your application; the module then becomes available as a global name. Because there is only one instance of each module, any changes made to the module object get reflected everywhere. For example:
 
config.py:
x = 0 # Default value of the 'x' configuration setting
mod.py:
import config
config.x = 1
main.py:
import config
import mod
print config.x
 
 
Ques: 14. Do you know about Indexing and Slicing Operation in Sequences?
 
Answer:
 
Python supports two main operations which are indexing and slicing. Tuples, lists and strings are some examples about sequence. Indexing operation allows you to fetch an item in the sequence and slicing operation allows you to retrieve an item from the list of sequence. Python starts from the beginning and if successive numbers are not specified it starts at the last. In python the start position is included but it stops before the end statement.
 
 
Question: 15. What do you mean by Raising Error Exceptions in Python?
 
Answer:
 
Programmer can raise exceptions using the raise statements in python. While using exception statement, error and exception object must be specified first. This error should be related to the derived class of the Error. We can use this to specify about the length of the user name, password field.
 
 
Ques: 16. What do you mean by a Lambda Form?
 
Answer:
 
Make_repeater is used to create a function during the run time and it is later called at run time. The lambda statement is used to create a new function which can be later used during the run time. Lambda function takes expressions only in order to return them during the run time.
 
 
Ques: 17. What do you understand by Assert Statement?
 
Answer:
 
This statement is very useful when you want to check the items in the list for true or false function. This statement should be predefined because it interacts with the user and raises an error if something goes wrong. Assert statement is used to assert whether something is true or false.
 
 
Ques: 18. What are Pickling and Unpickling in Python?
 
Answer:
 
By specifying the dump function, you can store the data into a specific file and this is known as pickling. Python has a standard module known as Pickle which enables you to store a specific object at some destination and then you can call the object back at later stage. While you are retrieving the object, this process is known as unpickling.
 
 
Ques: 19. How a Tuple is different from a List?
 
Answer:
 
A tuple is a list that is immutable. A list is mutable, means that the members of a list can be changed and altered. But a tuple is immutable, it means that the members of list cannot be changed.
Other significant difference is of the syntax. A list is defined as
 
listA = [1,2,5,8,5,3,]
listB= ["John", "Alice", "Tom"]
A tuple is defined in the following way
tupX = (1,4,2,4,6,7,8)
tupY = ("John","Alice", "Tom")
 
 
Ques: 20. Does Python allow arguments Pass by Value or Pass by Reference?
 
Answer:
 
No, there is nothing like passing of the arguments by Value or pass by reference. Instead, they are Pass by assignment. Python does not support passing arguments by value or reference.
 
The parameter which you pass is originally a reference to the object not the reference to a fixed memory location. But the reference is passed by value. Additionally, some data types like strings and tuples are immutable whereas others are mutable.