PL/SQL Basic Interview Questions in 2021
Procedural Language Extensions to SQL (PL/SQL) (Structured Query Language). It was developed by Oracle to address the shortcomings of SQL and make it simpler to construct and manage complex, important applications.
Following are the disadvantages of SQL
- In SQL, decision-making, looping, and branching are not supported.
- Due to the nature of increased traffic, the pace of execution slows since the SQL statements are delivered to the Oracle engine all at once.
- While manipulating the data, there is no functionality for error checking.
- In order to solve the aforementioned drawbacks, PL/SQL was created, which keeps the strength of SQL while fusing it with procedural commands. It is designed as a block-structured language, and the Oracle engine receives the statements of each block, which speeds up processing thanks to a drop in traffic.
-
What are the features of PL/SQL?
The following are the features of PL/SQL:
- By utilizing its procedural character, PL/SQL offers the feature of decision-making, looping, and branching.
- PL/SQL allows for the processing of several queries in a single block by utilizing a single command.
- As PL/SQL pieces like functions, procedures, packages, triggers, and types may be bundled and saved in databases, applications can reuse the PL/SQL code.
- By utilizing an exception-handling block, PL/SQL allows exception handling.
- PL/SQL offers error checking and data validation before data processing in addition to exception handling.
- Where there is an Oracle engine, PL/SQL applications can run on any computer platform or operating system.
-
What do you understand by PL/SQL table?
- Objects of the type table that are depicted as database tables are what PL/SQL tables are. They are a means to offer arrays that are merely temporary tables in memory for processing that is carried out more quickly.
- These tables are helpful for transporting large amounts of data, which streamlines the procedure.
3.When does a DECLARE block become mandatory?
The anonymous blocks of PL/SQL, such as stand-alone and non-stored procedures, use this statement. The statement should always come first in the standalone file when they are used.
4.Explain the uses of database trigger.
A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
1) Audit data modifications.
2) log events transparently.
3) Enforce complex business rules.
4) Maintain replica tables
5) Derive column values
6) Implement Complex security authorizations3
5.Explain Raise_application_error.
This DBMS STANDARD function enables the issuance of user-defined error messages from a stored sub-program or database trigger.
6.Explain two virtual tables available at the time of database trigger execution.
- Table columns are referred as OLD.column_name and NEW.column_name.
- For INSERT related triggers, NEW.column_name values are available only.
- For DELETE related triggers, OLD.column_name values are available only.
- For UPDATE related triggers, both Table columns are available.
7.How is a process of PL SQL compiled?
- Syntax check, bind, and p-code generation are all included in the compilation process.
- The PL SQL codes are checked for compilation issues during syntax checking.
- The variables that hold the data are given a storage address once all errors have been fixed. It’s known as Binding.
- A collection of directives for the PL SQL engine is known as a P-code. For named blocks, the P-code is kept in the database and used the following time it is run.
8.Differentiate between Syntax and runtime errors.
- A PL/SQL compiler can quickly identify a syntax error. improper spelling, for instance.
- A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.
9.Define Implicit and Explicit Cursors.
- By default, a cursor is implied. The data in this cursor cannot be changed or processed by the user.
- The programme defines an explicit cursor if a query returns multiple rows of data. When a result, the application can handle each row in turn as the cursor returns it.
