Oracle PLSQL Interview Questions

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.

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

Stored procedures and functions

Packages

Triggers

Cursors

How is PL/SQL differs from SQL?
SQLPL/SQL
Query/Command executionsProgramming Language Execution at a time
Data source for web pagesBuild, format and display web pages that contains data source
Declarative in natureProcedural in nature
Manipulating dataCreating Web Applications

 

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.

Pl SQL Interview Questions

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 NOMOUNTInstance startup
STARTUP MOUNTThe database is mounted
STARTUP OPENThe 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

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Oracle PLSQL Interview Questions"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Support


    Please leave a message and we'll get back to you soon.
    Three + 6