• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar

Oracle PL/SQL Tutorial

  • Home
  • Joins
    • Inner Join
    • Left Join
    • Right Join
    • Self Join
    • Full Join
  • Constraints
    • Add primary key
    • Add constraint foreign key
    • Drop constraint key
    • Enable a foreign key
    • Disable foreign key
  • Cursors
  • Triggers
  • Indexes
    • Create index oracle
    • Alter index oracle
    • Drop index oracle
    • List all indexes from Oracle database
  • Exceptions
  • Interview Questions

Dev

PL/SQL Interview Questions and Answers


PL SQL Interview Questions and Answers

1. What is an index?

Index is an object in a database that provides direct access and quick search without scanning the entire table.
The index is used for the most used columns for queries.

2. What are the types of indexes?

B-tree index (default)
Bitmap index
Partition
Function-based
Domain index
Compound index

3. What are the constraints in the Oracle?

PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK
NOT NULL

4. What is the difference between primary key and unique key?

Primary key can not be NULL.
Unique key Constraint may have a NULL value.

5. What is a Foreign Key?

A foreign key is a column containing the primary key of another table.

6. What are the Data Manipulation Language (DML) Statements?

INSERT
UPDATE
DELETE
MERGE
SELECT

7. What are the Data Definition Language (DDL) Statements?

ALTER
CREATE
DROP
RENAME
TRUNCATE
COMMENT

8. What is the difference between truncate table and delete from table?

Truncated deletes all records in the table and does not allow rollbacks.
After the delete command, you can rollback the deleted records. Delete allows the use of clause where.

9. What are the Transaction Control Statements

SAVEPOINT
COMMIT
ROLLBACK

10. What are the types of UNION?

UNION
UNION ALL

11. What are the types of JOIN?

Left join
Full join
Right join
Self join
Inner join

12. What are the Oracle Set Operators?

UNION
UNION ALL
INTERSECT
MINUS

13. What is the difference between a function and a procedure?

The function returns only one mandatory value.
The procedure returns multiple values using OUT parameters.
Functions can be called from a SELECT statement.
Procedures can not be called from a SELECT statement.
Functions are used for computation.
Procedures are used for business logic.
Procedures always return integer value, 0 default.

14. What is a Cursor?

A cursor is a pointer to unnamed private SQL area.

15. What are the types of cursors?

Implicit cursors
Explicit cursors

16. Which are the parts of an explicit cursor?

Declare Cursor
Open cursor
FETCH statement
CLOSE statement

17. What are the attributes of a cursor?

SQL%ISOPEN
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%BULK_ROWCOUNT
SQL%BULK_EXCEPTIONS

18. What are the parts of a PL/SQL block?

Declaration
Execution
Exceptions

18. What are the LOOP statements?

LOOP
FOR LOOP
WHILE LOOP

20. What are the types of exceptions?

Predefined exceptions
User defined exceptions

21. Which are the most used predefined exceptions?

no_data_found
others
too_many_rows
dup_val_on_index
invalid_number

22. What is the difference between SQLCODE and SQLERRM?

SQLCODE returns the numeric code of the exception.
SQLERRM returns the error message associated with an error code.

23. What is the difference between %ROWTYPE and %TYPE?

%ROWTYPE allow to declare a record that represents a row of a database table or view.
%TYPE allow to declare a data item of the same data type as a previously declared variable or column.

24. What is a Trigger?

A trigger is a PLSQL block that is executed whenever an event occurs.
It fires implicitly whenever the triggering event occurs.
It has similar to stored procedures.
Triggers are implicitly fired by Oracle database when an triggering event occurs.
Triggers are used to prevent invalid transactions, are also used to set business rules or for event logging.

25. How many triggers can be set on a table?

The maximum number of Triggers that can be seted on a table is 12.

26. How do you disable a trigger?

alter trigger trigger_name disable;

27. How do you change the name of a table?

alter table old_table_name rename to new_table_name;

28. What is Mutating Trigger?

Mutating Trigger error occurs when a trigger tries to modify the same triggering table.
A trigger can not change a table that it has read from.
Solution: Use a view. Use autonomus transaction. Use Compound Trigger.

29. What is a View?

A PL/SQL view is a virtual table based on a query of one or more tables.

30. What is a Query?

A PL/SQL query is select statement based on a table or multiple tables.

31. What is a Subquery?

A subquery is a SELECT statement within another statement.

32. What are the Types of Subquery?

Single Row
Multiple Row

33. What are the Oracle Aggregate Functions?

AVG
COUNT
MAX
MIN
SUM

34. What are the Oracle Character Functions?

CONCAT
LENGTH
SUBSTR
INSTR
LOWER
UPPER
REPLACE

35. What are the Oracle Conversion Functions?

CAST
CONVERT
TO_CHAR
TO_DATE
TO_NUMBER
TO_CLOB
TO_LOB
TO_TIMESTAMP

36. What are the Oracle Date Functions?

SYSDATE
ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
EXTRACT (datetime)
MONTHS_BETWEEN
TRUNC (date)
LOCALTIMESTAMP

37. What is a Package?

A package is a schema object that groups logically related PL/SQL objects like: types, variables, constants, functions, procedures, cursors, and exceptions.

38. What are the advantages of using packages?

Modularity – Packages allow you encapsulate logically related objects
Hidden Implementation Details – Packages allow you hide the implementation details in the package body.
Added Functionality – Package public objects like variables and cursors can persist for the life of a session.
Better Performance – Oracle Database loads the whole package into memory.
Easier to Grant Roles – You can grant roles on the package, instead of granting roles on each object.

39. What are the types of collections?

Index-by tables (associative arrays)
Nested tables
Varrays (variable size arrays)

40. What is a Synonym?

A synonym is an alias for another schema object.

41. What is Data Dictionary?

Data Dictionary is a catalog that includes a set of readonly tables that provide information about objects in the database.
Objects that compose the Data Dictionary are: tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers.

42. List the Oracle Database PL/SQL Packages.

DBMS_ALERT
DBMS_JOB
DBMS_OUTPUT
DBMS_SQL
UTL_ENCODE
UTL_FILE
UTL_MAIL
UTL_SMTP

Filed Under: PL/SQL

Sys_Connect_By_Path

Sys_Connect_By_Path

SYS_CONNECT_BY_PATH returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition. SYS_CONNECT_BY_PATH is valid only in hierarchical queries.

Syntax:

SYS_CONNECT_BY_PATH( column, char )

Example:

SELECT SYS_CONNECT_BY_PATH(ENAME, '/') "Path"
FROM EMP
START WITH ENAME = 'BLAKE'
CONNECT BY PRIOR EMPNO = MGR;

Output:

/BLAKE
/BLAKE/ALLEN
/BLAKE/WARD
/BLAKE/MARTIN
/BLAKE/TURNER
/BLAKE/JAMES

Filed Under: PL/SQL

Rank

Rank

RANK calculates the rank of a value in a group of values. The return type is NUMBER.

Syntax:

RANK( expression ) WITHIN GROUP ( ORDER BY expression )

RANK( ) OVER ([query_partition_clause] order_by_clause)

Example 1:

SELECT RANK(1000, 300) WITHIN GROUP (ORDER BY SAL, COMM)
from EMP;

Example 2:

SELECT e.EMPNO, e.ENAME, e.DEPTNO, e.SAL, 
RANK() OVER (PARTITION BY e.DEPTNO ORDER BY e.SAL DESC) as emp_rank 
FROM EMP e;

Filed Under: PL/SQL

Dense_Rank

Dense_Rank

DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.

Syntax:

DENSE_RANK( expression ) WITHIN GROUP ( ORDER BY expression )

DENSE_RANK( ) OVER ([query_partition_clause] order_by_clause)

Example 1:

select DENSE_RANK(1000, 300) WITHIN GROUP (ORDER BY SAL, COMM)
from EMP;

Example 2:

SELECT e.EMPNO, e.ENAME, e.DEPTNO, e.SAL, 
dense_rank() OVER (partition by e.DEPTNO ORDER BY e.SAL desc) as emp_rank 
FROM EMP e;

Filed Under: PL/SQL

Listagg

Listagg

LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.

Syntax:

LISTAGG (measure_expression [, ‘delimiter’])
WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]

Example:

SELECT LISTAGG(e.ENAME, '; ') WITHIN GROUP (ORDER BY e.ENAME)
FROM EMP e
WHERE e.DEPTNO=10;

Output:

CLARK; KING; MILLER

Filed Under: PL/SQL

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 49
  • Go to Next Page »

Primary Sidebar

  • Facebook
  • RSS
  • Twitter
  • Create B-tree index
  • Create Composite Index
  • Create Bitmap Index
  • Create Function-Based Index
  • Index with Collect Statistics
  • List all indexes from Oracle database
  • List all tables from a schema of Oracle database
  • List all triggers from Oracle database

PL/SQL tutorial

  • PL/SQL Interview Questions and Answers
  • Sys_Connect_By_Path
  • Rank
  • Dense_Rank
  • Listagg

Secondary Sidebar

PL/SQL Tutorial

  • PL/SQL Tutorial
  • PL/SQL Functions
  • PL/SQL Select Query
  • PL/SQL Table Joins
  • PL/SQL Cursors
  • PL/SQL Collections and Records
  • PL/SQL Triggers
  • PL/SQL Views
  • PL/SQL Exception Handling
  • PL/SQL Sequential Control
  • PL/SQL Iterative Control
  • PL/SQL Control Structures
  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Data Types
  • PL/SQL Indexes
  • PL/SQL Constraints
  • PL/SQL %ROWTYPE Attribute
  • PL/SQL %TYPE Attribute
  • PL/SQL Variables
  • PL/SQL Anonymous block
  • PL/SQL Operators
  • PL/SQL Delete
  • PL/SQL Update
  • PL/SQL Insert
  • PL/SQL Select

Copyright  2013 - 2021 PL/SQL Tutorial

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

Non-necessary

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.