Oracle pl sql Interview Questions and Answers
1. What is PL/SQL?
Answer: Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
2. How can you Rollback a particular part of a procedure or any PL/SQL Program?
Answer: It can be done using Savepoints during the definition of a PL/SQL program.
3. What is set server output function used for in PL/SQL?
Answer: In PL/SQL, we frequently require to produce the Output on the console. We generally do it using dbms_output.put_line() function. For this to work properly and display the output on the console screen, we need to first set the server output to ON state. For this, the command is set server output on.
4. What are the disadvantages of Cursors and is there any alternative to it?
Answer: The processing of Cursors is very slow as compared to Joins. Hence, Joins can be an alternative to Cursors.
5. What are the different Loop Control Structures used in PL/SQL?
Answer: The different Loop Control Structures in PL/SQL are as follows:
6. What is the difference between a Rollback Command and a Commit Command?
Answer: A Commit command is used to save the current transaction in the database in which modification is done to the database using Database Manipulation Language (DML) commands. A Rollback command is however used to undo the modifications done by the DML commands previously.
7. What is the cursor and why it is required?
Answer: A cursor is a temporary work area created in 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. The cursor is required to process rows individually for queries.
8. How is the process of PL/SQL compiled?
Answer: Compilation process includes syntax check, bind, and p-code generation processes. Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL/SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed. (Elearning Portal)
9. what is Flashback Query?
Answer: Flashback query is handle by Database Administrator only flashback queries along allows the content of the table to be retrieved with reference to a specific point of time by using as of clause that is flashback queries retrieves accidental data after committing the transaction also.
Flashback queries generally use undo file that is flashback queries retrieve old data before committing the transaction oracle to provide two methods for flashback queries.
10. What is REF CURSOR (or) CURSOR VARIABLE (or) DYNAMIC CURSOR?
Answer: Oracle 7.2 introduced ref cursor, This is a user-defined type which is used to process multiple records and also this is a record by record process.
In static cursor, database servers execute only one select statement at a time for a single active set area wherein ref cursor database servers execute a number of select statement dynamically for a single active set area that’s why those cursors are also called as a dynamical cursor.
Generally, we are not allowed to pass static cursor as parameters to use subprograms whereas we can also pass ref cursor as a parameter to the subprograms because basically precursor is a user-defined type in oracle we can also pass all user-defined type as a parameter to the subprograms.
Generally, the static cursor does not return multiple records into client application whereas ref cursor is allowed to return multiple records into client application (Java, .Net, PHP, VB, C++).
This is an user-defined type so we are creating it in 2 steps process i.e first we are creating type then only we are creating a variable from that type that’s why this is also called as cursor variable.
11. What is PL/SQL, Why do we need PL/SQL instead of SQL, Describe your experience working with PLSQL and What are the difficulties faced while working with PL SQL and How did you overcome?
Answer: PL/SQL is a procedural language extension with SQL Language.
Oracle introduced PL/SQL
It is a combination of SQL and Procedural Statements and used for creating applications.
Basically, PL/SQL is a block-structured programming language whenever we are submitting PL/SQL
Blocks then all SQL statements are executing separately by using SQL engine and also all procedure statements are executed separately.
Explain your current and previous projects along with your roles and responsibilities, mention some of the challenging difficulties you’ve faced in your project while working with PL/SQL.
12. What is Mutating Trigger?
Answer: Into a row-level trigger based on a table, trigger body cannot read data from the same table and also we cannot perform DML operation on the same table. If we are trying this oracle server returns mutating error oracle-4091: table is mutating.This error is called mutating error, and this trigger is called a mutating trigger, and the table is called a mutating table.
Mutating errors are not occurred in statement-level trigger because through this statement-level trigger when we are performing DML operations automatically data committed into the database, whereas in the row-level trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating errors occur.
13. What are Views and why they are used?
Answer: A View logically represents subsets of data from one or more table. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.
Views are used:
To restrict data access
To make complex queries easy
To provide data Independence
Views provide groups of the user to access data according to their requirement.
Oracle PL/SQL Interview Questions And Answers For 7 Years Experience.
14. What is a difference between ON DELETE CASCADE and ON DELETE SET NULL?
Answer: ON DELETE CASCADE Indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted. ON DELETE SET NULL Coverts foreign key values to null when the parent value is removed. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.
15. Describe types of Constraints in brief?
Answer: NOT NULL: NOT NULL Constraint ensures that the column contains no null values.
UNIQUE KEY: UNIQUE Key Constraint ensures that every value in a column or set of columns must be unique, that is, no two rows of a table can have duplicate values in a specified column or set of columns. If the UNIQUE constraint comprises more than one column, that group of columns is called a Composite Unique Key. There can be more than one Unique key on a table. Unique Key Constraint allows the input of Null values. Unique Key automatically creates an index on the column it is created.
PRIMARY KEY: Uniquely identifies each row in the Table. Only one PRIMARY KEY can be created for each table but can have several UNIQUE constraints. PRIMARY KEY ensures that no column can contain a NULL value. A Unique Index is automatically created for a PRIMARY KEY column. PRIMARY KEY is called a Parent key.
FOREIGN KEY: Is also called Referential Integrity Constraint. FOREIGN KEY is one in which a column or set of columns take references of the Primary/Unique key of the same or another table. FOREIGN KEY is called a child key. A FOREIGN KEY value must match an existing value in the parent table or be null.
CHECK KEY: Defines a condition that each row must satisfy. A single column can have multiple CHECK Constraints. During CHECK constraint following expressions is not allowed:
1) References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM Pseudo columns.
2) Calls to SYSDATE, UID, USER, and USERENV Functions.
16. Describe naming rules for creating a Table?
Answer: Naming rules to be considered for creating a table are:
The table name must begin with a letter,
The table name can be 1-30 characters long,
Table name can contain only A-Z, a-z, 0-9,_, $, #.
The table name cannot duplicate the name of another object owned by the same user.
The table name cannot be an oracle server reserved word.
17. Explain all Joins used in Oracle 8i?
Answer: 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.
Forex: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.
Equi Join: This type of Join involves primary and foreign key relation. Equi Join is also called Simple or Inner Joins.
Non-Equi Joins A Non-Equi Join condition containing something other than an equality operator. The relationship is obtained using an operator other than equal operator (=). The conditions such as <= and >= can be used, but BETWEEN is the simplest to represent Non-Equi Joins.
Outer Joins: Outer Join is used to fetch rows that do not meet the join condition. The outer join operator is the plus sign (+), and it is placed on the side of the join that is deficient in information. The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. A condition involving an Outer Join cannot use IN and OR operator.
18. Explain all Joins used in Oracle 9i and later release?
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, then 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, then 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.
Forex: Select a.city, b.dept_name from loc a Join dept b USING (loc_id) where loc_id=10;Joins with the ON clause: Use the ON clause to specify a join condition. The ON clause makes code easy to understand. ON clause is equals to Self Joins. The ON clause can also be used to join columns that have different names.
Left/ Right/ Full Outer Joins: Left Outer Join displays all rows from the table that is Left to the LEFT OUTER JOIN clause, right outer join displays all rows from the table that is right to the RIGHT OUTER JOIN clause, and full outer join displays all rows from both the tables either left or right to the FULL OUTER JOIN clause.
19. Explain the character, number, and date function in detail?
Answer: Character functions: accept character input and return both character and number values. Types of character function are:
a) Case-Manipulation Functions: LOWER, UPPER, INITCAP
b) Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE
c) Number Functions: accept Numeric input and return numeric values. Number Functions are: ROUND, TRUNC, and MOD
d) 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.
20. What is an alias in SQL statements?
Answer: 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 double quotation marks (“ “) to make it case sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.
Forex: Select emp_name AS name from employee; (Here AS is a keyword and “name” is an alias).
21. What is SQL and also describe types of SQL statements?
Answer: 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
22. What is a Dual Table?
Answer: The 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.
23. What is the use of Double Ampersand (&&) in SQL Queries? Give an example?
Answer: Use “&&” if you want to reuse the variable value without prompting the user each time.
Forex: Select empno, name, &&column_name from employee order by &column_name;
24. What is a MERGE statement?
Answer: The MERGE statement inserts or updates rows in one table, using data from another table. It is useful in data warehousing applications.
25. What is a Transaction? Describe common errors can occur while executing any Transaction?
Answer: Transaction consists of a collection of DML statements that forms a logical unit of work.
The common errors that can occur while executing any transaction are:
The violation of constraints.
Data type mismatch.
Value too wide to fit in the column.
The system crashes or Server gets down.
The session Killed.
Locking takes place. Etc.
26. What are Constraints? How many types of constraints are there?
Answer: Constraints are used to prevent invalid data entry or deletion if there are dependencies. Constraints enforce rules at the table level. Constraints can be created either at the same time as the table is created or after the table has been created. Constraints can be defined at the column or table level. Constraint defined for a specific table can be viewed by looking at the USER-CONSTRAINTS data dictionary table. You can define any constraint at the table level except NOT NULL which is defined only at the column level.
There are 5 types of constraints:
- Not Null Constraint
- Unique Key Constraint
- Primary Key Constraint
- Foreign Key Constraint
- Check Key Constraint.
27. What is the utilization of interfaces?
Answer: An interface takes after a class in which none of the strategies have been executed—the system marks are there, yet the body of each methodology is void. To use an interface, another class must realize it by giving a body to most of the procedures contained in the interface. Interfaces can give a layer of reflection to your code. They isolate the specific execution of a system from the declaration for that procedure. In this manner, you can have unmistakable use of a methodology in perspective of your particular application.
28. What is the main difference between Unique Key and Primary Key?
Answer: The main difference between Unique Key and Primary Key are:
Unique Vs Primary Key
Unique Key : A table can have more than one Unique Key.
Unique key column can store NULL values.
Uniquely identify each value in a column.
Uniquely identify each row in a table. (Oracle pl sql Interview Questions)
Primary Key : A table can have only one Primary Key.
The primary key column cannot store NULL values.
29. What are the different functionalities of a Trigger?
Answer: Trigger is also same as stored procedure & also it will automatically be invoked whenever DML operation performed against table or view.
30. What is Triggering Events (or) Trigger Predicate Clauses?
Answer: If we want to perform multiple operations in different tables then we must use triggering events within the trigger body. These are inserting, updating, deleting clauses. These clauses are used in the statement, row-level trigger. These triggers are also called as trigger predicate clauses.
If inserting then stmts;
else if updating then stmts;
else if deleting then stmts;
31. What is Forward Declaration?
Answer: In oracle declaring procedures within package body are called forward declaring generally before we are calling private procedures into public procedure first we must implement private procedure within body otherwise use a forward declaration within the package body.
32. What is the difference between FUNCTION, PROCEDURE, AND PACKAGE in PL/SQL?
Answer: Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.
Package: A package is schema object which groups logically related PL/SQL types, items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.
33. Which is the Default Cursor in Oracle PL/SQL?
Answer: Implicit Cursors are the Default Cursor in PL/SQL. These cursors are automatically activated when DML statements are encountered such as Insert, Delete or Update.
34. Is there a PL/SQL Engine in SQL*Plus?
Answer: Oracle Forms, SQL*Plus does not have an SQL Engine. Thus, all your PL/SQL are sent directly to the database engine for execution. This makes it comparatively more efficient as SQL statements are not uncovered and sent to the database individually.
35. What are the different parts of an Explicit Cursor?
Answer: The different parts in the process of making an Explicit Cursor are as follows:
1. Declaring the Cursor
2. Opening the Cursor
3. Fetching the Cursor
4. Closing the Cursor
36. Explain the difference between Truncate and Delete?
Answer: Truncate is much faster than Delete Command. It basically resets the Memory Blocks after Execution. Delete is a Database Manipulation Language (DML) Command whereas Truncate is a Data Definition Language (DDL) Command and it is comparatively slower.
37. Explain the difference between Varchar and Char?
Answer: Varchar doesn’t set aside memory location during the declaration of a variable. It stores the value only after a variable is defined or assigned a value. Its storage capacity is 32767 Bytes.
Char, however, preserves the memory location mentioned in the variable declaration even if it is not used. The maximum storage capacity for a Character variable is 255 Bytes.
38. Explain Union, Union All, Intersect and Minus in PL/SQL?
Answer: Union: It returns all the distinct rows selected by either of the Queries.
Union All: It returns all the rows selected by one of the queries which include all the duplicates.
Intersect: It returns all the distinct rows selected by both the queries.
Minus: It returns all the distinct rows selected by the first query and not by the second one.
39. What is a mutating table error and how can you get around it?
Answer: Level: Intermediate
Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
40. How can you generate debugging output from PL/SQL?
Answer: Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.
41. Explain the difference between a data block, an extent, and a segment?
Answer: A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
42. What is NULL value in Oracle?
Answer: the NULL value represents unknown or missing data. It is used as a place holder or represented as a default entry indicating that no actual data is present.
43. What does coalescing a tablespace do?
Answer: Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
44. Explain materialized views and how they are used?
Answer: Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
45. Compare and contrast TRUNCATE and DELETE for a table?
Answer: Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
46. What is the difference between Varchar2 and Varchar?
Answer: Varchar2 Datatype is memory efficient as it variable memory storage datatype whereas a Varchar Datatype variable is not memory efficient as it has fixed memory storage. Varchar occupies space for NULL values whereas Varchar2 variable does not. Varchar can store up to 2KB whereas a Varchar2 Datatype can store up to 4KB.
47. What is the basic difference between a Procedure and a Function?
Answer: You can use ROWID to fetch Row from the table. The use of ROW ID is the fastest query method for fetching data from the table.
48. Enlist various loops in PL/SQL Database?
Answer: The various loops used in PL/SQL are as follows:
1. Simple Loop
2. For Loop
3. Nested Loop
4. While Loop
49. Describe the disadvantage of Database Trigger over Stored Procedures?
We cannot control the execution of a Trigger whereas a Stored Procedure Execution can be controlled by the programmer.
50. What is the basic difference between a Procedure and a Function?
Answer: Both Procedures and Functions have the capability of accessing parameters but Functions return a value to the PL/SQL Block whereas Procedures do not return any value.