Oracle PL/SQL Interview Questions and Answers

Are you preparing for PL/SQL Interviews, if yes then we are listing here a list of PL/SQL interview questions?

PL (Procedural language) is an extension to SQL (Structured Query Language) where a developer can write complex database interactions using procedures, control structures like branching and iteration, modules and functions. It has tight integration with SQL and supports both dynamic and static SQL.

Amongst other questions during an interview, it is also important to know how SQL and PL/SQL are different. Some benefits of PL/SQL over SQL are:

• Supports procedural processing, conditional statements, looping and other features similar to high-level languages.
• Multiple statements can be sent to the database server at once in the form of a procedure, hence saving time and network traffic.
• Customized error handling is possible.
• Fully portable.

Apart from the above benefits, PL/SQL has some more attractive features like functions, procedures, triggers, cursors that make it one of the most versatile relational databases.

Below are some common basic and advanced pl/sql interview questions and answers which are asked in the interview by the interviewer

1. What is the difference between PL SQL and SQL?

Ans:

PL SQL vs SQL
Comparison	SQL	PL/SQL
Execution	Single command at a time	Block of code
Application	Source of data to be displayed	Application created by data acquired by SQL
Structures include	DDL and DML based queries and commands	Includes procedures, functions, etc
Recommended while	Performing CRUD operations on data	Creating applications to display data obtained using SQL
Compatibility with each other	SQL can be embedded into PL/SQL	PL/SQL cant be embedded in SQL

2. What is SQL and also describe types of SQL statements?

Ans:

SQL stands for Structured Query Language. SQL is a language used to communicate with the server to access, manipulate and control data.

There are 5 different types of SQL statements.

• Data Retrieval: SELECT
• Data Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE
• Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.
• Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT
• Data Control Language (DCL): GRANT, REVOKE

3. What is an alias in SQL statements?

Ans:

Alias is a user-defined alternative name given to the column or table. By default column alias headings appear in upper case. Enclose the alias in a double quotation marks (“ “) to make it case sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.

For ex:

(Here AS is a keyword and “name” is an alias).

4. What is a Literal? Give an example of where it can be used?

Ans:

For ex:

(Here “is a” is a literal).

5. What is the difference between SQL and iSQL*Plus?

Ans:

SQL Vs iSQL*Plus
SQL	iSQL*Plus
Is a Language	Is an Environment
Character and date columns heading are left-justified and number column headings are right-justified.	Default heading justification is in Centre.
Cannot be Abbreviated (short forms)	Can be Abbreviated
Does not have a continuation character	Has a dash (-) as a continuation character if the command is longer than one line
Use Functions to perform some formatting	Use commands to format data

6. Define the order of Precedence used in executing SQL statements.

Ans:

Order of Precedence used in executing SQL statements
Order Evaluated	Operator
1	Arithmetic operators (*, /, +, -)
2	Concatenation operators (||)
3	Comparison conditions
4	Is[NOT] NULL, LIKE, [NOT] IN
5	[NOT] BETWEEN
6	NOT Logical condition
7	AND logical condition

7. What are SQL functions? Describe in brief different types of SQL functions?

Ans:

There are two distinct types of SQL functions:

1) Single-Row functions: These functions operate on a single row to give one result per row.

Types of Single-Row functions:

• Character
• Number
• Date
• Conversion
• General

2) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.

Types of Multiple-Row functions:

• AVG
• COUNT
• MAX
• MIN
• SUM
• STDDEV
• VARIANCE

8. Explain character, number and date function in detail?

Ans:

Character functions: accept character input and return both character and number values. Types of character function are:

Case-Manipulation Functions: LOWER, UPPER, INITCAP

Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE

Number Functions: accept Numeric input and return numeric values. Number Functions are: ROUND, TRUNC and MOD

Date Functions: operates on values of the Date data type. (All date functions return a value of DATE data type except the MONTHS_BETWEEN Function, which returns a number. Date Functions are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC. 

9. What is a Dual Table?

Ans:

Dual table is owned by the user SYS and can be accessed by all users. It contains one columnDummy and one row with the value X. The Dual Table is useful when you want to return a value only once. The value can be a constant, pseudocolumn or expression that is not derived from a table with user data.

10. Explain Conversion function in detail?

Ans:

Conversion Functions converts a value from one data type to another. Conversion functions are of two types:

Implicit Data type conversion:

• VARCHAR2 or CHAR To NUMBER, DATE
• NUMBER To VARCHAR2
• DATE To VARCHAR2

Explicit data type conversion:

• TO_NUMBER
• TO_CHAR
• TO_DATE

TO_NUMBER function is used to convert Character string to Number format. TO_NUMBER function use fx modifier. Format:

fx modifier specifies the exact matching for the character argument and number format model of TO_NUMBER function.

TO_CHAR function is used to convert NUMBER or DATE data type to CHARACTER format. TO_CHAR Function use fm element to remove padded blanks or suppress leading zeros. TO_CHAR Function formats:

Format model must be enclosed in single quotation marks and is case sensitive.

For ex:

The TO_DATE function is used to convert Character string to date format. TO_DATE function uses the fx modifier which specifies the exact matching for the character argument and date format model of TO_DATE function. TO_DATE function format: TO_DATE ( char[, ‘ format_model’] ).

For ex:

11. Describe different types of General Function used in SQL?

Ans:

General functions are of following types:

• NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.
• NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)
• NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
• COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.
• Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function. 

12. What is the difference between COUNT (*), COUNT (expression), COUNT (distinct expression)? (Where expression is any column name of Table)

Ans:

COUNT (EXP): Returns the number of non-null values in the column identified by expression.

COUNT (DISTINCT EXP): Returns the number of unique, non-null values in the column identified by expression.

13. What is a Subquery? Describe its Types?

Ans:

 A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. Sub query can be placed in WHERE, HAVING and FROM clause.

Guidelines for using subqueries:

• Enclose sub queries within parenthesis
• Place subqueries on the right side of the comparison condition.
• Use Single-row operators with single-row subqueries and Multiple-row operators with multiple-row subqueries.

Types of subqueries:

• Single-Row Subquery: Queries that return only one row from the inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>
• Multiple-Row Subquery: Queries that return more than one row from the inner Select statement. There are also multiple-column subqueries that return more than one column from the inner select statement. Operators include: IN, ANY, ALL.

14. What is the difference between ANY and ALL operators?

Ans:

> ANY means more than the minimum.

< ANY means less than the maximum

= ANY is equivalent to an IN operator.

15. What is a MERGE statement?

Ans:

 The MERGE statement inserts or updates rows in one table, using data from another table. It is useful in data warehousing applications. 

16. What is the difference between the “VERIFY” and the “FEEDBACK” command?

Ans:

VERIFY Command: Use VERIFY Command to confirm the changes in the SQL statement (Old and New values). Defined with SET VERIFY ON/OFF.

Feedback Command: Displays the number of records returned by a query.

17. What is the use of Double Ampersand (&&) in SQL Queries? Give an example?

Ans:

For ex:

18. What are Joins and how many types of Joins are there?

Ans:

Joins are used to retrieve data from more than one table.

There are 5 different types of joins.

Types of Joins
Oracle 8i and Prior	SQL: 1999 (9i)
Equi Join	Natural/Inner Join
Outer Join	Left Outer/ Right Outer/ Full Outer Join
Self Join	Join ON
Non-Equi Join	Join USING
Cartesian Product	Cross Join

19. Explain all Joins used in Oracle 8i?

Ans:

Cartesian Join: When a Join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. To avoid a Cartesian product, always include a valid join condition in a “where” clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions.

20. Explain all Joins used in Oracle 9i and later release?

Ans:

Cross Join: Cross Join clause produces the cross-product of two tables. This is same as a Cartesian product between the two tables.

Natural Joins: Is used to join two tables automatically based on the columns which have matching data types and names, using the keyword NATURAL JOIN. It is equal to the Equi-Join. If the columns have the same names but different data types, than the Natural Join syntax causes an error.

Join with the USING clause: If several columns have the same names but the data types do not match, than the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equi Join. Use the USING clause to match only one column when more than one column matches. Do not use a table name or alias in the referenced columns. The NATURAL JOIN clause and USING clause are mutually exclusive

21. What is cross join?

Ans:

Cross join is defined as the Cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table.

22. What are temporal data types in Oracle?

Ans:

• Date Data Type – Different formats of Dates
• TimeStamp Data Type – Different formats of TimeStamp
• Interval Data Type – Interval between dates and time

23. How do we create privileges in Oracle?

Ans:

A privilege is nothing but right to execute an SQL query or to access another user object. Privilege can be given as system privilege or user privilege.

24. What is VArray?

Ans:

Varray is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.

25. How do we get field details of a table?

Ans:

Describe <Table_Name> is used to get the field details of a specified table.

26. What is the difference between rename and alias?

Ans:

Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename is nothing but replacement of name and Alias is an alternate name of the table or column.

27. What is a View?

Ans:

View is a logical table which is based on one or more tables or views.  The tables upon which the view is based are called Base Tables and it doesn’t contain data.

28. What is a cursor variable?

Ans:

A cursor variable is associated with different statements which can hold different values at run time. A cursor variable is a kind of reference type.

29. What are cursor attributes?

Ans:

Each cursor in Oracle has set of attributes which enables an application program to test the state of the cursor. The attributes can be used to check whether cursor is opened or closed, found or not found and also find row count.

30. What are SET operators?

Ans:

SET operators are used with two or more queries and those operators are Union, Union All, Intersect and Minus.

31. How can we delete duplicate rows in a table?

Ans:

Duplicate rows in the table can be deleted by using ROWID.

32. What are the attributes of Cursor?

Ans:

Attributes of Cursor are

%FOUND

Returns NULL if cursor is open and fetch has not been executed

Returns TRUE if the fetch of cursor is executed successfully.

Returns False if no rows are returned.

%NOT FOUND

Returns NULL if cursor is open and fetch has not been executed

Returns False if fetch has been executed

Returns True if no row was returned

%ISOPEN

Returns true if the cursor is open

Returns false if the cursor is closed

%ROWCOUNT

Returns the number of rows fetched. It has to be iterated through the entire cursor to give exact real count.

33. Can we store pictures in the database and if so, how it can be done?

Ans:

Yes, we can store pictures in the database by Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. But the table can have only on Long Raw data type.

34. What is an integrity constraint?

Ans:

An integrity constraint is a declaration defined a business rule for a table column. Integrity constraints are used to ensure accuracy and consistency of data in a database. There are types – Domain Integrity, Referential Integrity and Domain Integrity.

35. What is an ALERT?

Ans:

An alert is a window which appears in the center of the screen overlaying a portion of the current display.

36. What is a hash cluster?

Ans:

Hash Cluster is a technique used to store the table for faster retrieval. Apply hash value on the table to retrieve the rows from the table.

37. What are the various constraints used in Oracle?

Ans:

Following are constraints used:

• NULL – It is to indicate that particular column can contain NULL values
• NOT NULL – It is to indicate that particular column cannot contain NULL values
• CHECK – Validate that values in the given column to meet the specific criteria
• DEFAULT – It is to indicate the value is assigned to default value

38. What is the difference between SUBSTR and INSTR?

Ans:

SUBSTR returns specific portion of a string and INSTR provides a character position in which a pattern is found in a string.

SUBSTR returns string whereas INSTR returns numeric.

39. What is the parameter mode that can be passed to a procedure?

Ans:

IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.

40. What are the different Oracle Database objects?

Ans:

There are different data objects in Oracle –

• Tables – set of elements organized in vertical and horizontal
• Views  – Virtual table derived from one or more tables
• Indexes – Performance tuning method for processing the records
• Synonyms – Alias name for tables
• Sequences – Multiple users generate unique numbers
• Tablespaces – Logical storage unit in Oracle

41. What are the differences between LOV and List Item?

Ans:

LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.

42. What are privileges and Grants?

Ans:

Privileges are the rights to execute SQL statements – means Right to connect and connect. Grants are given to the object so that objects can be accessed accordingly. Grants can be provided by the owner or creator of an object.

43. What is the difference between $ORACLE_BASE and $ORACLE_HOME?

Ans:

Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath the base folder in which all oracle products reside.

44. What is the fastest query method to fetch data from the table?

Ans:

Row can be fetched from the table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.

45. What is the maximum number of triggers that can be applied to a single table?

Ans:

12 is the maximum number of triggers that can be applied to a single table.

46. How to display row numbers with the records?

Ans:

Display row numbers with the records numbers –

This query will display row numbers and the field values from the given table.

47. How can we view the last record added to a table?

Ans:

Last record can be added to a table and this can be done by –

48. What is the data type of DUAL table?

Ans:

The DUAL table is a one-column table present in oracle database.  The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.

49. What is the difference between Cartesian Join and Cross Join?

Ans:

There are no differences between the joins. Cartesian and Cross joins are same. Cross join gives cartesian product of two tables – Rows from first table is multiplied with another table which is called cartesian product.

Cross join without where clause gives Cartesian product.

50. How to display employee records who gets more salary than the average salary in the department?

Ans:

This can be done by this query –gets more salary than the average salary in the department

51. What is Oracle and what are its different editions?

Ans:

Oracle is one of the popular databases provided by Oracle Corporation, which works on relational management concepts and hence it is referred to as Oracle RDBMS as well. It is widely used for online transaction processing, data warehousing, and enterprise grid computing.

52.How will you identify Oracle Database Software Release?

Ans:

Oracle follows a number of formats for every release.

For Example,

Release 10.1.0.1.1 can be referred to as:

10: Major DB Release Number

1: DB Maintenance Release Number

0: Application Server Release Number

1: Component Specific Release Number

1: Platform Specific Release Number

53.How will you differentiate between VARCHAR & VARCHAR2?

Ans:

Both VARCHAR & VARCHAR2 are Oracle data types that are used to store character strings of variable length. Their differences are:

• VARCHAR can store characters up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.
• VARCHAR will hold the space for characters defined during declaration even if all of them are not used whereas VARCHAR2 will release the unused space.

54.What is the difference between TRUNCATE & DELETE command?

Ans:

Both the commands are used to remove data from the database.

The difference between the two include:

• TRUNCATE is a DDL operation while DELETE is a DML operation.
• TRUNCATE drops the structure of a database and hence cannot be rolled back while the DELETE command can be rolled back.
• The TRUNCATE command will free the object storage space while the DELETE command does not.

55.What is meant by RAW datatype?

Ans:

RAW datatype is used to store variable-length binary data or byte strings.

The difference between RAW & VARCHAR2 datatype is that PL/SQL does not recognize this data type and hence, cannot do any conversions when RAW data is transferred to different systems. This data type can only be queried or inserted in a table.

Syntax:

56.What is meant by Joins? List the types of Joins.

Ans:

Joins are used to extract data from multiple tables using some common columns or conditions.

There are various types of Joins as listed below:

• INNER JOIN
• OUTER JOIN
• CROSS JOINS or CARTESIAN PRODUCT
• EQUI JOIN
• ANTI JOIN
• SEMI JOIN

57.What is the difference between SUBSTR & INSTR functions?

Ans:

SUBSTR function returns the sub-part identified by numeric values from the provided string.

For Example

INSTR will return the position number of the sub-string within the string.

For Example,

58.How can we find out the duplicate values in an Oracle table?

Ans:

We can use the below example query to fetch the duplicate records.

59. How does the ON-DELETE-CASCADE statement work?

Ans:

Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.

We can add ON DELETE CASCADE option on an existing table using the below set of commands.

Syntax:

60. What is an NVL function? How can it be used?

Ans:

NVL is a function that helps the user to substitute value if null is encountered for an expression.

It can be used as the below syntax.

61. What is the difference between a Primary Key & a Unique Key?

Ans:

Primary Key is used to identify each table row uniquely, while a Unique Key prevents duplicate values in a table column.

Given below are a few differences:

• The primary key can be only one on the table while unique keys can be multiple.
• The primary key cannot hold null value at all while the unique key allows multiple null values.
• The primary key is a clustered index while a unique key is a non-clustered index.

62.How TRANSLATE command is different from REPLACE?

Ans:

TRANSLATE command translates characters one by one in the provided string with the substitution character. REPLACE command will replace a character or a set of characters with a complete substitution string.

For Example:

63. How can we find out the current date and time in Oracle?

Ans:

We can find the current date & time using SYSDATE command in Oracle.

Syntax:

64. Why do we use COALESCE function in Oracle?

Ans:

COALESCE function is used to return the first non-null expression from the list of arguments provided in the expression. There must be a minimum of two arguments in an expression.

Syntax:

65. How will you write a query to get 5th RANK students from the table STUDENT_REPORT?

Ans:

The query will be as follows:

66.When do we use the GROUP BY clause in SQL Query?

Ans:

GROUP BY clause is used to identify and group the data by one or more columns in the query results. This clause is often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG, etc.

Syntax:

67.What is the quickest way to fetch the data from a table?

Ans:

The quickest way to fetch the data would be to use ROWID in the SQL query.

68. Where do we use DECODE and CASE Statements?

Both DECODE & CASE statements will function like IF-THEN-ELSE statement and they are the alternatives for each other. These functions are used in Oracle to transform the data values.

For Example:

DECODE Function 

CASE Function

Both the commands will display order numbers with their respective status as,

If,

Status O= Ordered

Status P= Packed

Status S= Shipped

Status A= Arrived

69. Why do we need integrity constraints in a database?

Ans:

Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables. With the help of the below-mentioned constraints, relationships can be maintained between the tables.

Various integrity constraints are available which include Primary Key, Foreign Key, UNIQUE KEY, NOT NULL & CHECK.

70.What do you mean by MERGE in Oracle and how can we merge two tables?

Ans:

The MERGE statement is used to merge the data from two tables. It selects the data from the source table and inserts/updates it in the other table based on the condition provided in the MERGE query.

Syntax:

71.What is the use of Aggregate functions in Oracle?

Ans:

Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations. These are:

• AVG
• MIN
• MAX
• COUNT
• SUM
• STDEV

72. What are the set operators UNION, UNION ALL, MINUS & INTERSECT meant to do?

Ans:

The set operator facilitates the user to fetch the data from two or more than two tables at once if the columns and relative data types are the same in the source tables.

• UNION operator returns all the rows from both the tables except the duplicate rows.
• UNION ALL returns all the rows from both the tables along with the duplicate rows.
• MINUS returns rows from the first table, which does not exist in the second table.
• INTERSECT returns only the common rows in both the tables.

73. Can we convert a date to char in Oracle and if so, what would be the syntax?

Ans:

We can use the TO_CHAR function to do the above conversion.

Syntax:

74.What do you mean by a database transaction & what all TCL statements are available in Oracle?

Ans:

Transaction occurs when a set of SQL statements are executed in one go. To control the execution of these statements, Oracle has introduced TCL i.e. Transaction Control Statements that use a set of statements.

The set of statements include:

• COMMIT: Used to make a transaction permanent.
• ROLLBACK: Used to roll back the state of DB to last the commit point.
• SAVEPOINT: Helps to specify a transaction point to which rollback can be done later.

75. What do you understand by a database object? Can you list a few of them?

Ans:

Objects used to store the data or references of the data in a database is known as a database object. The database consists of various types of DB objects such as tables, views, indexes, constraints, stored procedures, triggers, etc.

76.What is a nested table and how is it different from a normal table?

Ans:

A nested table is a database collection object, which can be stored as a column in a table. While creating a normal table, an entire nested table can be referenced in a single column. Nested tables have only one column with no restriction of rows.

For Example:

Here, we are creating a normal table as EMP and referring to a nested table TYPE_NAME as a column.

77. Can we save images in a database and if yes, how?

Ans:

BLOB stands for Binary Large Object, which is a data type that is generally used to hold images, audio & video files or some binary executables. This datatype has the capacity of holding data up to 4 GB.

78. What do you understand by database schema and what does it hold?

Ans:

Schema is a collection of database objects owned by a database user who can create or manipulate new objects within this schema. The schema can contain any DB objects like table, view, indexes, clusters, stored procs, functions, etc.

79. What is a data dictionary and how can it be created?

Ans:

Whenever a new database is created, a database-specific data dictionary gets created by the system. This dictionary is owned by the SYS user and maintains all the metadata related to the database. It has a set of read-only tables and views and it is physically stored in the SYSTEM tablespace.

80.What is a View and how is it different from a table?

Ans:

View is a user-defined database object that is used to store the results of an SQL query, which can be referenced later. Views do not store this data physically but as a virtual table, hence it can be referred to as a logical table.

View is different from the table as:

• A table can hold data but not SQL query results whereas View can save the query results, which can be used in another SQL query as a whole.
• The table can be updated or deleted while Views cannot be done so.

81.What is meant by a deadlock situation?

Ans:

Deadlock is a situation when two or more users are simultaneously waiting for the data, which is locked by each other. Hence it results in all blocked user sessions.

82.What is meant by an index?

Ans:

An index is a schema object, which is created to search the data efficiently within the table. Indexes are usually created on certain columns of the table, which are accessed the most. Indexes can be clustered or non-clustered.

83.What is a ROLE in the Oracle database?

Ans:

Giving access to individual objects to the individual users is a tough administrative task. In order to make this job easy, a group of common privileges is created in a database, which is known as ROLE. The ROLE, once created can be assigned to or revoked from the users by using GRANT & REVOKE command.

Syntax: 

84.What are the attributes that are found in a CURSOR?

Ans:

A CURSOR has various attributes as mentioned below:

(i) %FOUND:

• Returns INVALID_CURSOR if the cursor has been declared but closed.
• Returns NULL if fetch has not happened but the cursor is open only.
• Returns TRUE, if the rows are fetched successfully and FALSE if no rows are returned.

(ii) NOT FOUND:

• Returns INVALID_CURSOR if the cursor has been declared but closed.
• Returns NULL if fetch has not happened but the cursor is open only.
• Returns FALSE, if rows are fetched successfully and TRUE if no rows are returned

(iii) %ISOPEN: Returns TRUE, if the cursor is OPEN else FALSE

(iv) %ROWCOUNT: Returns the count of fetched rows.

85. Why do we use %ROWTYPE & %TYPE in PLSQL?

Ans:

%ROWTYPE & %TYPE are the attributes in PL/SQL that can inherit the datatypes of a table defined in a database. The purpose of using these attributes is to provide data independence and integrity.

If any of the datatypes or precision gets changed in the database, PL/SQL code gets updated automatically with the changed data type.

%TYPE is used for declaring a variable that needs to have the same data type as of a table column.

While %ROWTYPE will be used to define a complete row of records having a structure similar to the structure of a table.

86.Why do we create Stored Procedures & Functions in PL/SQL and how are they different?

Ans:

A stored procedure is a set of SQL statements that are written to perform a specific task. These statements can be saved as a group in the database with an assigned name and can be shared with different programs if permissions are there to access the same.

Functions are again subprograms that are written to perform specific tasks but there are differences between both of them.

Stored Procedures	Functions
Stored Procedures may or may not return a value and can return multiple values as well.	Function will always return only a single value.
Stored Procedures can include DML statements like insert, update & delete.	We cannot use DML statements in a function.
Stored Procedures can call functions.	Functions cannot call stored procedures.
Stored Procedures support exception handling using Try/Catch block.	Functions does not support Try/Catch block.

87. What are the parameters that we can pass through a stored procedure?

Ans:

We can pass IN, OUT & INOUT parameters through a stored procedure and they should be defined while declaring the procedure itself.

88. What is a trigger and what are its types?

Ans:

A trigger is a stored program which is written in such a way that it gets executed automatically when some event occurs. This event can be any DML or a DDL operation.

PL/SQL supports two types of triggers:

• Row Level
• Statement Level

89. How will you distinguish a global variable with a local variable in PL/SQL?

Ans:

Global variable is the one, which is defined at the beginning of the program and survives until the end. It can be accessed by any methods or procedures within the program, while the access to the local variable is limited to the procedure or method where it is declared.

90. What are the packages in PL SQL?

Ans:

A package is a group of related database objects like stored procs, functions, types, triggers, cursors, etc. that are stored in the Oracle database. It is a kind of library of related objects which can be accessed by multiple applications if permitted.

PL/SQL Package structure consists of 2 parts: package specification & package body.

Conclusion

I hope the above set of questions would have helped you to get a glimpse of what Oracle is all about.

Even if you have a thorough knowledge of all the basic concepts, the way in which you present it in the interview matters a lot. Hence stay calm and face the interview confidently without any hesitation.