PL/SQL questions for Computer Science students.

Define PL/SQL.

PL/SQL is a procedural language.
It is an extension to standard SQL.
It enables us to execute procedural logic on the database.
We can use PL/SQL to perform processing on the server.
We can also use PL/SQL to create stored procedures and functions.
It is mainly used in writing applications that needs to be structured and has error handling.

Features of PL/SQL

Offers conditional blocks of code having if else etc.
Offers error handling. It can handle exceptions.
The blocks can be nested within each other.
The PL/SQL engine processes the statements in blocks.

The block typically looks like,

DECLARE
.
.
BEGIN
.
.
EXCEPTION
.
.
END

What is object data type in oracle?

An object type is just an abstraction of the real world entities.
Object data type is created using object type in oracle.

An object has:

It makes easier to work with the complex data like images, media (audio / video).
New/User defined objects can be created from any database built in types or by their combinations.
It can be created using SQL data type such as Number, Varchar2, Date etc.
PL/SQL types can’t be used to create object data type.

Example:

Create type MyName as object (first varchar2(20), second varchar2(20));

Now you can use this data type while defining a table below:

Create table Emp (empno number(5),Name MyName);

What is composite data type?

Composite data type is any data type which can be constructed in a program using the primitives data types or other composite types.
Composite data types can be used to construct complex data types fulfilling different requirements.
Composite data types are also known as Collections i.e RECORD, TABLE, NESTED TABLE, VARRAY.
This is so because all of them are composed of multiple data types.

Composite data types are of 2 types:

Differences between CHAR and NCHAR in Oracle.

CHAR	NCHAR
CHAR is used to store fixed length character data.	NCHAR is used to store fixed length Unicode data.
It takes 'n' bytes.	It takes 2n bytes.
It accepts up to 8000 characters.	It accepts 4000 characters.
It has a specified size in bytes by default.	It has a size specified in characters by default.

Differences between CHAR and VARCHAR2 in Oracle.

CHAR	VARCHAR2
CHAR is used to store fixed length character strings.	VARCHAR2 can store variable length character strings.
It is faster than VARCHAR2.	It is slower than CHAR.
Example: If we have char name[10] and store “abcde”, then 5 bytes will be filled with the null values.	Example: VARCHAR2 name[10] 5 bytes will be used and other 5 bytes will be freed.

Define CLOB and NCLOB data types.

CLOB:

CLOB stands for Character large object.
It is 4GB in length.
CLOB is used to store character set data.
It stores single-byte character set data.

NCLOB:

NCLOB stands for National Character large object.
It is CLOB data type for multiple character sets , up to 4GB in length.
NCLOB is used to store Unicode national character set data.
It stores fixed-length multi-byte character set data.

What is BFILE data types?

BFILE is an external binary file.
It is used to store unstructured binary data outside the database.
The column of BFILE type stores file locater that points the OS file which actually stores data.
Its size is limited by the operating system.
BFILEs are read-only, we cannot modify them.

Differences between DATE and TIMESTAMP in Oracle.

DATE

Date is used to store date and time values including month, day, year, century, hours, minutes and seconds.
It fails to provide granularity and order of execution when finding difference between 2 instances (events) having a difference of less than a second between them.
Example: Date: 16:05:14

TIMESTAMP

TimeStamp datatype stores everything that DATE stores and additionally stores fractional seconds.
Example: Timestamp: 16:05:14:000

What are Varrays?

Varrays are one-dimensional arrays.
These can be only used when you know in advance about the maximum number of items to be stored.
Varray defines the maximum length in the declaration itself.
For example: One person can have multiple phone numbers. If we are storing this data in the tables, then we can store multiple phone numbers corresponding to a single name. If we know the maximum number of phone numbers, then we can use Varrays, else we use nested tables.

What are PL/SQL Subprograms?

Named PL/SQL blocks of code which can be invoked using parameters are called PL/SQL sub programs.
Subprograms are named PL/SQL blocks that can take parameters and be invoked.
PL/SQL has two types of subprograms called procedures and functions.
A procedure is used to perform an action and a function is used to compute a value.

Explain advantages of PL/SQL Subprograms.

Advantages of PL/SQL subprograms are,

The application makes a single call to the database to run a block of statements which improves performance against running SQL multiple times.
This will reduce the number of calls between the database and the application.
PL/SQL is secure since the code resides inside the database thus hiding internal database details from the application. The application will only make a call to the PL/SQL subprogram.
PL/SQL and SQL go hand in hand so there would be no need of any translation required between PL/SQL and SQL.

What are the types of constraints available in Oracle.

Oracle constraints are used to maintain consistency of data and ensure that the data is properly maintained.
A constraint is more or less restricted when applied on a table.

Types of Constraints
1. Check Constraint
2. NOT NULL Constraint
3. PRIMARY KEY Constraint
4. FOREIGN Constraint
5. UNIQUE Constraint

1. Check Constraint

Check constraint is used to ensure before inserting the data in the database.
It is validated and checked for the condition.

Example

CREATE TABLE employee ( id number CHECK (id between 0 and 1000), Name varchar(200) );

2. NOT NULL Constraint

NOT NULL is used on a column to ensure that the value for that column can never be NULL.

Example

CREATE TABLE employee ( id number NOT NULL, Name varchar(200) );

3. PRIMARY KEY Constraint

Primary key constraint ensures that the column(s) always has a unique value to identify the record.

Example

CREATE TABLE employee ( id number NOT NULL, Name varchar(200) Constraint prim_id PRIMARY KEY(id) );

4. FOREIGN Constraint

A foreign key is a reference to another table.
It is used to establish relationships between tables.
For example, relationship between employee and professor table. One employee can have multiple professors. The Primary key of employee becomes foreign key of professor.

Example

CREATE TABLE employee ( id number NOT NULL, professor_id NOT NULL, Name varchar(200) Constraint prim_id FOREIGN KEY(id) REFERENCES professor(professor_id) );

5. UNIQUE Constraint

A unique constraint on a column uniquely identifies the record by a combination of one or more fields.
Few unique constraint fields can have a NULL value as long as the combination of value is unique.

Example

CREATE TABLE employee ( id number NOT NULL, dob DATE, professor_id NOT NULL, Name varchar(200) Constraint id_unique UNIQUE(id,dob) );

Overview of Data Manipulation Language (DML).

Data manipulation language or DML is used to control the data in a relational database.
The most commonly used DML is SQL. DML can either be procedural or declarative.
The data is manipulated using SELECT, INSERT and UDPATE statements.

1. SELECT
SELECT clause is used to select or retrieve data from the database.

Syntax:

SELECT * from table_name;

Example:

SELECT * from employee;

2. INSERT
INSERT clause is used to enter or insert information in the database.

Example:

INSERT into employee values (‘1’,’John’,’20000’);

3. UPDATE
UPDATE clause is used to update information of a table.

Example:

UPDATE employee SET emp_salary=’23000’ Where emp_id=’1’;

4. DELETE
DELETE clause is used to delete or remove information from the table.

Example:

DELETE FROM employee Where emp_id=1’;

What are aggregate functions in SQL?

Aggregate functions in SQL are used to perform calculation on data.
These functions are inbuilt in SQL and return a single value.

Following are the list of aggregate functions,
1. SUM
2. AVG
3. COUNT
4. MAX and MIN
5. ROLLUP

1. SUM
SUM function returns the sum or addition of all NOT NULL values of a column.

Example:

SELECT SUM(emp_salary) from employee;

2. AVG
AVG function returns the average of all NOT NULL values of a column.

Example:

SELECT AVG(emp_salary) from employee;

3. COUNT
COUNT function returns the number of rows or values of a table.

Example:

SELECT COUNT(*) from employee;

4. MAX and MIN
MAX function returns the largest value of a column in a table.

Example:

SELECT MAX(emp_salary) from employee;

MIN function returns the smallest value of a column in a table.

Example:

SELECT MIN(emp_salary) from employee;

5. ROLLUP
ROLLUP in SQL allows you to summarize your data and view.

Example:

SELECT Dept, Branch, SUM(Number) as Number FROM company GROUP BY dept,branch WITH ROLLUP

Explain the difference between GRANT and REVOKE command.

GRANT	REVOKE
GRANT command allows a user to perform certain activities on the database.	REVOKE command disallows a user to perform certain activities.
It grants access privileges for database objects to other users.	It revokes access privileges for database objects previously granted to other users.
Example: GRANT privilege_name ON object_name TO { user_name\|PUBLIC\|role_name } [WITH GRANT OPTION];	Example: REVOKE privilege_name ON object_name FROM { user_name\|PUBLIC\|role_name }

Explain the difference between ROLLBACK and COMMIT commands.

ROLLBACK	COMMIT
ROLLBACK command is used to undo the changes made by the DML commands.	The COMMIT command is used to save the modifications done to the database values by the DML commands.
It rollbacks all the changes of the current transaction.	It will make all the changes permanent that cannot be rolled back.
Syntax: DELETE FROM table_name ROLLBACK	Syntax: COMMIT;

What is a Join? Explain types of Join in Oracle.

Combining the records from two or more tables in a relational database is called as Join.
Join is used to match/equate different fields from 2 or more tables using a primary or a foreign key.
Output is based on the type of Join and what is to be queries i.e. common data between 2 tables, unique data, total data, or mutually exclusive data.
Join creates a set that can be saved as a table or used as it is.

Types of JOINS:

Join Type	Example	Description
Simple Join	SELECT p.last_name, t.deptName FROM person p, dept t WHERE p.id = t.id;	Find name and department name of students who have been allotted a department.
Inner/Equi/Natural Join	SELECT * from Emp INNER JOIN Dept WHERE Emp.empid=Dept.empid	Extracts data that meets the JOIN conditions only. A JOIN is by default INNER unless OUTER keyword is specified for an OUTER JOIN.
Outer Join	SELECT distinct * from Emp LEFT OUTER JOIN Dept Where Emp.empid=Dept.empid	It includes non matching rows also unlike Inner Join.
Self Join	SELECT a.name,b.name from emp a, emp b WHERE a.id=b.rollNumber	Joining a Table to itself.

What is Stored Procedure?

Stored Procedure is a set of Structured Query Language (SQL) statements.
It is a subroutine available to applications that access a Relational Database Management System (RDBMS).
Stored procedure is assigned with the name that's stored in the database in compiled form, so that it can be shared by a number of programs.
It can be helpful in controlling access to data, preserving data integrity and improving productivity.

What are the advantages of a Stored Procedure?

They are easier to maintain and troubleshoot as they are modular.
Stored procedure enables better tuning for performance.
It is much easier from a GUI end than building/using complex queries.
They can be part of a separate layer which allows separating the concerns.
It helps in reducing the network usage.
It provides more scalability to an application.
It is reusable and hence reduces the code.

What is a Trigger?

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
Triggers executes when a user tries to modify data through a data manipulation language (DML) event, such as Insert, Delete, Update.
These triggers fire when any valid event is fired.

Explain the difference between trigger and stored procedure.

Trigger	Stored Procedure
Trigger is an act which is performed automatically before or after a event occurs.	Stored procedure is a set of functionality which is executed when it is explicitly invoked.
It cannot accept parameters.	It can accept parameters.
A trigger cannot return any value.	A stored procedure can return value.
It is executed automatically on some event.	It needs to be explicitly called.
Triggers are used for insertion, update and deletion.	Stored procedures are often using independently in the database.

Explain Row level and Statement level trigger.

Row level Trigger

They get fired once for each row in a table affected by the statements.
These triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.
Row Level Trigger is fired each time when row is affected by Insert, Update or Delete command.
If statement doesn’t affect any row, no trigger action happens.

Statement level Trigger

They get fired once for each triggering statement.
This kind of trigger fires when a SQL statement affects the rows of the table.
The trigger activates and performs its activity irrespective of number of rows affected due to SQL statement.
These triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.

What are the Cascading Triggers?

A Trigger that contains statement which causes the other triggers are known as cascading triggers.

Here’s the order of execution of statements in case of cascading triggers:

Execute all BEFORE statement triggers that apply to the current statement.
Loop for each row affected statement.
Execute all BEFORE row triggers that apply to the current statement in the loop.
Lock and change row, perform integrity constraints check; release lock.
Execute all AFTER row triggers that apply to the current statement.
Execute all AFTER statement triggers that apply to the current statement.

What is Package?

A package is an encapsulated collection of related schema objects.
A package is compiled and then stored in the database's data dictionary as a schema objects.
These objects can be procedure, functions, variables, constants, cursors and exceptions.

What is a Cursor? What are its types?

Cursor is a temporary work area created in the system memory when a SQL statement is executed.
It contains information on a select statement and the rows of data accessed by it.
It can hold more than one row, but can process only one row at a time.
Cursor is used to access the result set present in the memory.
This resultset contains the records returned on execution of a query.

There are two types of cursors:
1. Explicit
2. Implicit

1. Explicit Cursor

Explicit cursor created when you are executing a SELECT statement that returns more than one row.
It stores multiple records, only one record can be processed at a time, which is called as current row.
When you fetch a row the current row position moves to next row.

2. Implicit Cursor

PL/SQL creates an implicit cursor whenever an SQL statement is executed through the code, unless the code employs an explicit cursor.
The developer does not explicitly declare the cursor, thus, known as implicit cursor.
If the oracle engine has opened a cursor for its internal processing, then it is implicit cursor.
These cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.

Explain the attributes of explicit cursor.

Attributes	Description
%FOUND	True, if the SQL statement has changed any rows.
%NOTFOUND	True, if record was not fetched successfully.
%ROWCOUNT	The number of rows affected by the SQL statement.
%ISOPEN	True, if there is a SQL statement being associated to the cursor or the cursor is open.

What are the drawbacks of a Cursor?

Cursors allow row by row processing of recordset. For every row, a network roundtrip is made unlike in a Select query, where there is just one network roundtrip.
Cursors need more I/O and temp storage resources, thus it is slower.
Implicit cursors are less efficient than explicit cursors.
Implicit cursors are more vulnerable to data errors.
Poorly written cursors can completely deplete available memory.
It occupies memory from the system that may be available for other processes.

What is a Cursor Variable?

A cursor variable is a pointer that distinguishes the current row in a resultset from a multi-row query.
These variables allow cursors to be opened independently of being processed.
It can be passed as parameters between application layers and server side components.
These variables can be used to reduce the client-server network traffic.
In case of a cursor, Oracle opens an anonymous work area that stores processing information.
This area can be accessed by cursor variable which points to this area.
One must define a REF CURSOR type, and then declare cursor variables of that type to do so.

Example

/* Create the cursor type. */
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;

/* Declare a cursor variable of that type. */
company_curvar company_curtype;

Explain why cursor variables are easier to use than cursors?

Cursor variables are preferred over a cursor for following reasons:

A cursor variable is not tied to a specific query.
One can open a cursor variable for any query returning the right set of columns. Thus, more flexible than cursors.
A cursor variable can be passed as a parameter.
A cursor variable can refer to different work areas.

What is an Index? Explain how to create an Index?

An index is a object which is used to improve performance during retrieval of records.
It helps to retrieve the data quickly from the tables.

Syntax:

CREATE INDEX index_name ON table_name;

Example:

CREATE [UNIQUE] INDEX index_name
ON employee[emp_id, emp_name,dept_id]
[COMPUTE STATISTICS]

The UNIQUE keyword is used when combined values of the index should be unique. It does not allowed duplicate values to be inserted into the table.
The COMPUTE STATISTICS during the creation of index optimizes the plan of execution of the SQL statement and improves the performance.

What is locking? State its advantages and types.

Locking is a mechanism to ensure data integrity while allowing maximum concurrent access to data.
It is used to implement concurrency control when multiple users access table to manipulate its data at the same time.
Locking protects table when several users are accessing the same table.
Locking is a concurrency control technique in oracle.
It helps in data integrity while allowing maximum concurrency access to data.
Oracle offers automatic locking whenever situation requires. This is called implicit locking.

Advantages of Locking

Avoids deadlock conditions.
Avoid clashes in capturing the resources.

Types of Locks

1. Shared Lock
This type is placed on a record when the record is being viewed.

2. Exclusive lock
This is placed when Insert, Update or Delete command is performed. There can be only one exclusive lock on a record at a time.

Explain how to see modified code in oracle using Oracle Data Dictionary.

Data dictionary in oracle is a read only set of tables providing information about changed schema objects, default values etc.
The data dictionary is stored in database’s SYSTEM tablespace.
We can use SQL statements (only SELECT Statement) to view data of data dictionary.

What is Program Global Area (PGA)?

The PGA is a memory area that contains data and control information for the Oracle server processes.

This area consists of the following components:

Stack space:

Session information:

Private SQL area:

It stores the data and controls the information for a server process in the memory.
The PGA consists of a private SQL area and the session memory.

What is a shared pool?

The shared pool is a key component.
The shared pool is like a buffer for SQL statements.
It is used to store the SQL statements so that the identical SQL statements do not have to be parsed each time they're executed.
It contains cache information that collects, parses, interprets and executes the SQL statements that goes against the database.
It is the area in SGA that allows sharing of parsed SQL statements among concurrent users.

What is SQL*Plus?

SQL*PLUS is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation.
SQL*plus allows SQL and PL/SQL scripts to interactively run in command line.
It allows three kinds of commands to run; SQL, PL/SQL and SQL * Plus commands.
It’s most commonly used by DBA’s to interact with the oracle database.

What are SQLCODE and SQLERRM?

SQLCODE

SQLCODE is a function that returns the error number for the last encountered error.
It returns the current error code from the error stack and the error message from the current error.
It is only useful in an exception handler.
You cannot use SQLCODE directly in a SQL statement, first assign the value of SQLCODE to a local variable.

SQLERRM

SQLERRM is a function that returns the actual error message of the last encountered error.
If the argument is omitted, it returns the error message associated with the current value of SQLCODE.
SQLERRM with no argument is useful only in an exception handler.
For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred.
For user-defined exceptions, SQLERRM returns the message user-defined exception.

Explain user defined exceptions in Oracle.

A User-defined exception has to be defined by the programmer.
User-defined exceptions are declared in the declaration section with their type as exception.
They must be raised explicitly using the RAISE statement, unlike pre-defined exceptions that are raised implicitly.
RAISE statement can also be used to raise internal exceptions.

Exception:

DECLARE
userdefined EXCEPTION;

BEGIN
<Condition on which exception is to be raised>
RAISE userdefined;

EXCEPTION
WHEN userdefined THEN
<task to perform when exception is raised>
END;

Explain the concepts of Exception in Oracle. Explain its type.

Exception is raised when an error occurs while program execution.
As soon as the error occurs, the program execution stops and the control is then transferred to exception-handling part.

There are two types of exceptions:

Predefined
These types of exceptions are raised whenever something occurs beyond oracle rules. E.g. Zero_Divide.

User defined

The ones that occur based on the condition specified by the user.
They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly.

How exceptions are raised in Oracle?

RAISE Statement

RAISE statement is used to explicitly raise an exception within a PL/SQL block.
It can be used to raise both system defined and user defined exceptions.
This statement immediately stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler.

Syntax

RAISE [exception_name]

The ways by which the PL/SQL runtime engine can raise an exception are:

Exceptions are raised automatically by the program.
The programmer raises a user defined exceptions.
The programmer raises pre-defined exceptions explicitly.

What is tkprof? And how is it used?

The tkprof is a performance diagnosing utility available to DBAs.
It is used for diagnosing performance issues.
It formats a trace file into a more readable format for performance analysis.
It is needed because trace file is a very complicated file to be read as it contains minute details of program execution.

How is it used?

When a user comes across problems related to the response time in comparison to his previous weeks response time, Session tracing and tkprof can be used to see exactly what is happening on the database, enabling the DBA to take corrective action.
At times, viewing SQL that is being executed for an application will be the only mechanism a DBA will have.
These situations arrive during the execution of encrypted PL/SQL code on the database or submission of SQL statements from the third party applications.

What is SQL*Loader?

SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk.
It is used for high performance data loads.
It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile.

What is Oracle Server Autotrace?

Oracle Server Autotrace is a underutilized feature of SQL*PLUS.
It is the first tracing utility used for most SQL performance tunning issues.
It is a utility that provides instant feedback on successful execution of any statement (select, update, insert, delete).
It is the most basic utility to test the performance issues.
Autotrace is important data that can be used to tune the SQL statement.

Overview of PL/SQL Error Handling.

When the system throws a warning or has an error it can lead to an exception.
Such exception needs to be handled and can be defined internally or user defined.
Exceptions stop normal execution.
Internal exceptions are the ones handled by the system like divide by zero, out of memory etc.
Exceptions (PL/SQL runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources.
You cannot anticipate all possible exceptions, but you can write exception handlers that let your program to continue to operate in their presence.
Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers.

Explain the advantages of PL/SQL Exceptions.

PL/SQL exceptions improve readability of the code by separating the error routines in the code.
It helps you to handle the errors.
Each and every point of error need not be checked as exception handler can be added to the PL/SQL block.

Explain some of the commonly used Predefined PL/SQL Exceptions.

1. DIVIDE BY ZERO
This is raised when any number is attempted to divide by zero.

2. TOO MANY ROWS
A SELECT INTO statement returns more than one row.

3. CASE_NOT_FOUND
No choice in the WHEN clause of a case statement is selected.

4. LOGIN_DENIED
An attempt to login with an invalid username or password.

5. PROGRAM_ERROR
An internal PL/SQL problem.

PL/SQL questions for Computer Science students.

Define PL/SQL.

What is object data type in oracle?

What is composite data type?

Differences between CHAR and NCHAR in Oracle.

Differences between CHAR and VARCHAR2 in Oracle.

Define CLOB and NCLOB data types.

What is BFILE data types?

Differences between DATE and TIMESTAMP in Oracle.

What are Varrays?

What are PL/SQL Subprograms?

Explain advantages of PL/SQL Subprograms.

What are the types of constraints available in Oracle.

Overview of Data Manipulation Language (DML).

What are aggregate functions in SQL?

Explain the difference between GRANT and REVOKE command.

Explain the difference between ROLLBACK and COMMIT commands.

What is a Join? Explain types of Join in Oracle.

What is Stored Procedure?

What are the advantages of a Stored Procedure?

What is a Trigger?

Explain the difference between trigger and stored procedure.

Explain Row level and Statement level trigger.

What are the Cascading Triggers?

What is Package?

What is a Cursor? What are its types?

Explain the attributes of explicit cursor.

What are the drawbacks of a Cursor?

What is a Cursor Variable?

Explain why cursor variables are easier to use than cursors?

What is an Index? Explain how to create an Index?

What is locking? State its advantages and types.

Explain how to see modified code in oracle using Oracle Data Dictionary.

What is Program Global Area (PGA)?

What is a shared pool?

What is SQL*Plus?

What are SQLCODE and SQLERRM?

Explain user defined exceptions in Oracle.

Explain the concepts of Exception in Oracle. Explain its type.

How exceptions are raised in Oracle?

What is tkprof? And how is it used?

What is SQL*Loader?

What is Oracle Server Autotrace?

Overview of PL/SQL Error Handling.

Explain the advantages of PL/SQL Exceptions.

Explain some of the commonly used Predefined PL/SQL Exceptions.

Related Content