Oracle PL SQL Interview Questions and Answers

What is PL-SQL?

Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

What is the basic structure of PL/SQL?

PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

What are the most important characteristics of PL/SQL?

A list of some notable characteristics:

• PL/SQL is a block-structured language.
• It is portable to all environments that support Oracle.
• PL/SQL is integrated with the Oracle data dictionary.
• Stored procedures help better sharing of application.

How is a process of PL/SQL compiled?

Compilation process includes syntax check, bind and p-code generation processes. Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL/SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.

How can we connect an Android App to an Oracle database and use the PL/SQL procedural code?

Before getting into the PL/SQL, we must create a REST API using JSON for establishing connection. You can import the Retrofit libraries along with dependencies to establish communication with your Android App. Then prepare the functions and procedures using PL/SQL, and then once your oracle database is connected.

Interview Questions On PL SQL

Name few schema objects that can be created using PL/SQL?

Stored procedures and functions

Packages

Triggers

Cursors

 

What are the three basic sections of a PL/SQL block?

Declaration section

Execution section

Exception section

What is a trigger?

A trigger is a PL/SQL program that is stored in the database and executed immediately before or after the INSERT, UPDATE, and DELETE commands.

What are the uses of triggers?

Basically triggers are used to create consistencies, access restriction and implement securities to the database. Triggers are also used for

• Creating validation mechanisms involving searches in multiple tables
• Creating logs to register the use of a table
• Update other tables as a result of inclusion or changes in the current table.

How can we debug in PL/SQL?

We can make use of the DBMS_OUTPUT for printing breakpoint activities. We can also use DBMS_DEBUG.

How can we implement Rollback or Commit statement in a Trigger?

We cannot. It is not logical to put a Rollback or Commit within a Trigger because these statements impose a savepoint which affects the logical transaction processing.

What is an Oracle sequence?

A Sequence generates a serial list of unique numbers for numerical columns of a database's tables.

Difference between SUBSTR and INSTR?

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.

What is PL/SQL Records?

PS/SQL Records is type of data structure that contain a set of data(can be of various types), or distinct information values that can be referenced with each other as fields. They are useful for classifying and retrieving data with common attributes or properties. With this, it is much easier to identify similar data by tracing the attributes.

PL/SQL can manage three types of records:

• Table based records
• Programmer based records
• Cursor based records

How can we make an IF Statement within a SELECT Statement?

We make use of the DECODE keyword. For example, e.g. select DECODE (EMP_CAT,’3′,’Third’,’4′,’Fourth’Null);

What are the components of physical database structure of Oracle database?

Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

How are functions and procedures called in PL/SQL?

CALL <procedure name> to call it directly

EXECUTE <procedure name> from calling environment

<Procedure name> from other procedures or functions or packages

Functions are called directly from other programs or procedures, no additional keyword is required.

What are the different datatypes available in PL/SQL?

PL SQL data types can be broadly divided into following categories. There are many data types available in PL SQL but mostly you will be using some of the popular ones.

• Numbers – INT, INTEGER, FLOAT, NUMBER, SMALLINT, REAL etc.
• Character or String – CHAR, CHARACTER, RAW, VARCHAR, VARCHAR2, NCHAR, NVARCHAR2 etc.
• Boolean – BOOLEAN
• Date Time – DATE, TIMESTAMP etc.
• Refer them in detail at oracle Database Documentation.

Give the stages of instance startup to a usable state where normal users may access it. 

STARTUP NOMOUNT	Instance startup
STARTUP MOUNT	The database is mounted
STARTUP OPEN	The database is opened

  How to convert date to Julian Date format?

Using ‘J’ format string

SQL > select to_char(to_date(’24-Jan-2013′,’dd-mm-yyyy’),’J’) as julian from dual;

JULIAN