Oracle PL SQL Interview Questions

Oracle PL SQL Interview Questions

  • What is PL SQL?

PL SQL is a Block Structured programming language created by Oracle in the 1990s in hoping to provide additional procedural programming solutions to SQL.

Oracle PL SQL Interview Questions

  • What are the key differences between SQL and PL SQL?

SQL is a Structured Query Language as opposed to P/L SQL which is a full procedural language. SQL code is processed one statement block at a time, while P/L SQL code is executed as a single program at one time. SQL can be within P/L SQL, but P/L SQL cannot be within SQL.

 Oracle PL SQL Interview Questions

  • What is the basic structure of PL/SQL?

PL SQL, as much as any other procedural language, contains blocks. These blocks which are the basic unit of sensible code are primarily categorized by two types: anonymous blocks and named blocks.

[DECLARE]
Declaration statements;
BEGIN
Execution statements;
[EXCEPTION]
Exception handling statements;
END;
They are called anonymous because they have no names and are not saved in
an Oracle Database.

Oracle PL SQL Interview Questions

  • What are triggers and its uses?

Triggers are blocks of code which are run whenever the criteria for a specific event is satisfied. They are hardcoded within the PL SQL program and listens to events such as: DML(database manipulation), DDL(database definition), and database operation. They can be coded within a view, table, database, or scheme for which the mentioned event belongs.

There are many uses of triggers. They can be used to generate column values upon activating. For event logging within the table activities, auditing, and creating table duplicates. For security, they can implement security authorization, and handle invalid transactions.

General Structure of creating a Trigger:

CREATE [OR REPLACE ] TRIGGER triggerName
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF colName]
ON tableName
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

Oracle PL SQL Interview Questions

  • How is a PL/SQL code compiled?

Firstly, PL/SQL code is transferred to the server and is compiled to byte code. This process takes place prior to program execution. To increase the performance of the procedural code, the procedures are converted to native code shared libraries which are conveniently linked to the kernel. Note that increase in performance still greatly depends on the code structure. This is independent to database calls, and affects performance only on loops, calculations, etc.

Oracle PL SQL Interview Questions

  • What are few of the schema objects that are created using PL/SQL?

A schema is a user-owned set of schema objects, or logical data structures. These schema objects types are as follows:

Clusters
Database links
Database triggers
Dimensions
External procedure libraries
Indexes and index types
Java classes, Java resources, and Java sources
Materialized views and materialized view logs
Object tables, object types, and object views
Operators
Sequences
Stored functions, procedures, and packages
Synonyms
Tables and index-organized tables
Views

Among other objects which are not contained in a schema are:

Contexts
Directories
Profiles
Roles
Tablespaces
Users
Rollback segments

Oracle PL SQL Interview Questions

  • Define Commit, Rollback and Savepoint.

The COMMIT Statement finalizes to end your transaction and sets all changes to permanent. A transaction in SQL is any of statements that the Oracle Database treats as a single block. This also enables users to see the updates and changes made by the transaction. Finally, the COMMIT statement deletes all the savepoints prior to the transaction and releases transaction locks.

The ROLLBACK statement undoes the changes that the transaction has made. This is practically the opposite of the COMMIT Statement. Also, any locks made due to the transaction are released.

In conjunction, the SAVEPOINT statement is also used to set a restoration point when the ROLLBACK Statement is used. This limits the bounds of the ROLLBACK Statement by only reverting to the SAVEPOINT set point.

Oracle PL SQL Interview Questions

  • 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.

 Oracle PL SQL Interview Questions

  • What are %TYPE and %ROWTYPE for?

The %ROWTYPE allows the coder to indirectly represent a full or partial row of a database table or view, whereas the %TYPE allows for the coder to indirectly represent the data type from a previously declared variable or column. Basically, %ROWTYPE works on a full object whereas %TYPE works on a single column. The advantage to using either of these enables the coder to maintain data type declarations without ever having to know or change the data type for the items that use these. Below is an example of how the %TYPE allows for a layer of abstraction between names; allowing the coder to just change the first occurrence of the data type.

DECLARE
name VARCHAR(50);
firstName name%TYPE;
lastName name%TYPE;
province name%TYPE;
nationality name%TYPE;

emp employees_table%ROWTYPE;
BEGIN
Execution section;
END;

Oracle PL SQL Interview Questions

  • What is an exception in PL/SQL? What are the two types of exceptions?

Exceptions are manageable errors in a program. This means that errors handled by exceptions are within the bounds of the programmer to repair and PL/SQL provides catch features to encapsulate these errors to enable debugging and preventing the program to stop working.

There are two main types of exceptions – System Exceptions and User-Defined Exceptions. System Exceptions are such as no_data_found or too_many_rows and already defined by PL SQL. User-Defined Exceptions are exceptions defined by the user to handle particular errors.

Oracle PL SQL Interview Questions

  • What is stored Procedure?

A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as schema object. It can be nested, invoked and parameterized.

  • What is cursor and why it is required?

A cursor is a temporary work area created in a system memory when an SQL statement is executed.A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.

KyrosAcademy

This information box about the author only appears if the author has biographical information. Otherwise there is not author box shown. Follow YOOtheme on Twitter or read the blog.
+91 9952948899 info@kyrosacademy.com

Offcanvas