Oracle PLSQL Interview Questions

Ratings:
(4)
Views:0
Banner-Img
  • Share this blog:

Oracle PL SQL Interview Questions And Answers

PLSQL is the programming language that is used in Oracle databases. It is a BASIC-like language with some SQL extensions.

Oracle PLSQL Interview Questions by Tekslate is a book that provides an overview of the most common questions asked at an interview for Oracle PLSQL developer positions. The book provides solutions to these questions and it also offers tips on how to answer them.

Most frequently asked Oracle PL SQL Interview Questions

Q1) What is PL-SQL?

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

Q2. What is the basic structure of PL/SQL?

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

Q3. What are the most important characteristics of PL/SQL?

Ans: 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 the sharing of applications.

Q4. How is the process of PL/SQL compiled?

Ans: The 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.

Want to acquire industry skills and gain complete knowledge of Oracle PL  SQL? Enroll in Instructor-Led live Oracle PL SQL Training to become Job Ready!

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

Ans: Before getting into the PL/SQL, we must create a REST API using JSON for establishing connections. 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.

Q6. Name a few schema objects that can be created using PL/SQL.

Ans: Stored procedures and functions

  • Packages
  • Triggers
  • Cursors

Q7. How is PL/SQL differs from SQL?

Ans:
SQL PL/SQL
Query/Command executions Programming Language Execution at a time
The data source for web pages Build, format, and display web pages that contain data source
Declarative in nature Procedural in nature
Manipulating data Creating Web Applications
 

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

Ans:

  • Declaration section
  • Execution section
  • Exception section

Q9. What is a trigger?

Ans: A trigger is a PL/SQL program that is stored in the database and executed immediately before or after the INSERT, updates, and deletes commands.

Q10. What are the uses of triggers?

Ans: Basically, triggers are used to create consistencies, access restrictions, 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.

Q11. How can we debug in PL/SQL?

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

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

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

Q13. What is an Oracle sequence?

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

Q14. Difference between SUBSTR and INSTR?

Ans: INSTR (String1, String2 (n, (m)), INSTR returns the position of the m-th occurrence of string 2 in string1. The search begins from the nth position of string1.

SUBSTR (String1 n, m) SUBSTR returns a character string of size m in string1, starting from an n-th position of string1.

Q15. What is PL/SQL Records?

Ans: PS/SQL Records is a type of data structure that contains 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

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

Ans: We make use of the DECODE keyword. For example, e.g. select DECODE (EMP_CAT,’3′,’Third’,’4′,fourth null);

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

Ans: 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.

Q18. How are functions and procedures called in PL/SQL?

Ans: 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.

Q19. What are the different data types available in PL/SQL?

Ans: PL SQL data types can be broadly divided into the 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.

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

Ans:

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

Q21. How to convert date to Julian Date format?

Ans: Using ‘J’ format string

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

JULIAN

Q22. What is meant by Exception Handling in PL/SQL and what are its distinct types?

Ans: The exception is raised in PL/SQL whenever an error occurs. In other words, these are raised to handle undesired situations where PL/SQL scripts terminated unexpectedly including an error handling code in the program. Exception Section in PL/SQL contains this exception handling code.

There are 3 types of Exception:

  • Predefined Exceptions:  General Errors with pre-defined names
  • Undefined Exceptions: Less common errors and are not pre-defined
  • User-defined Exceptions: Violates business rules but don’t disturb running

Q23. Discuss PL/SQL Records?

Ans: The collection of PL/SQL server training Hyderabad is referred as the collection of values or information that is divided into multiple pieces, each of which is of simpler types and related to one another as fields. There are three types of records supported in PL/SQL

  • Programmer based records
  • Table based records
  • Cursor based records
About Author
Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.


Stay Updated


Get stories of change makers and innovators from the startup ecosystem in your inbox