Welcome to Mindmajix - The leading global online training platform. If you're looking for Oracle PL SQL Interview Questions for Experienced or Freshers, you are in the right place.
There are a lot of opportunities from many reputed companies in the world. According to research, Oracle PL SQL has a market share of about 2.2%. So, You still have the opportunity to move ahead in your career in Oracle PL SQL Development.
Mindmajix offers Advanced Oracle PL SQL Interview Questions 2021 that help you in cracking your interview & acquire a dream career as an Oracle PL SQL Developer. Here are frequently asked Oracle PL SQL Interview Questions, let's have a look into them.
|Types of PL/SQL Interview Questions|
|If you want to enrich your career and become a professional in Oracle PL SQL, then enroll in "Oracle PL SQL Training" - This course will help you to achieve excellence in this domain.|
Single command at a time
Block of code
Source of data to be displayed
Application created by data acquired by SQL
DDL and DML based queries and commands
Includes procedures, functions, etc
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
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 Manipulation Language (DML):
Data Definition Language (DDL):
Transaction Control Statements:
Data Manipulation Language (DCL):
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.
For example Select emp_name AS name from employee; (Here AS is a keyword and “name” is an alias).
A Literal is a string that can contain a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. Date and character literals must be enclosed within single quotation marks (‘ ‘), number literals need not.
For exp: Select last_name||’ is a’||job_id As “emp details” from the employee; (Here “is a” is a literal).
|Is a Language||Is an Environment|
|Character and date column headings are left-justified and number column headings are right-justified.||Default heading justification is in the 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|
|1||Arithmetic operators (*, /, +, -)|
|2||Concatenation operators (||)|
|4||Is[NOT] NULL, LIKE, [NOT] IN|
|6||NOT Logical condition|
|7||AND logical condition|
|8||OR logical condition|
SQL Functions are a very powerful feature of SQL. SQL functions can take arguments but always return some value.1
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:
2) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.
Types of Multiple-Row functions:
Character functions: accept character input and return both character and number values. Types of character function are:
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.
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.
Conversion Functions convert a value from one data type to another. Conversion functions are of two types:
Implicit Data type conversion:
Explicit data type conversion:
TO_NUMBER function is used to convert a Character string to Number format. TO_NUMBER function use fx modifier. Format: TO_NUMBER ( char[, ‘ format_model’] ). fx modifier specifies the exact matching for the character argument and number format model of the TO_NUMBER function.
TO_CHAR function is used to convert NUMBER or DATE data type to CHARACTER format. TO_CHAR Function uses fm element to remove padded blanks or suppress leading zeros. TO_CHAR Function formats:TO_CHAR (date, ‘format_model’).The format model must be enclosed in single quotation marks and is case sensitive.
For exp: Select TO_CHAR (hire date, ‘MM/YY’) from the employee.
TO_DATE function is used to convert a Character string to date format. TO_DATE function use 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 exp: Select TO_DATE (‘May 24, 2007’,’ mon dd RR’) from dual;
|Read these latest SQL Interview Questions and Answers that help you grab high-paying jobs|
General functions are of the following types:
NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then the NVL function returns the 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 the 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.
A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. A subquery can be placed in WHERE HAVING and FROM clause.
Guidelines for using subqueries:
Types of subqueries:
ANY Operator compares value to each value returned by the subquery. ANY operator has a synonym SOME operator.
ALL Operator compares value to every value returned by the subquery.
The MERGE statement inserts or updates rows in one table, using data from another table. It is useful in data warehousing applications.
Use “&&” if you want to reuse the variable value without prompting the user each time.
For ex: Select empno, ename, &&column_name from employee order by &column_name;
Joins are used to retrieve data from more than one table.
There are 5 different 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|
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.
For exp: 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 relations. 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 an 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 operators.
Self Join: Joining a table to itself.
The Cross Join clause produces the cross-product of two tables. This is the same as a Cartesian product between the two tables.
This 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.
For ex: 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 the 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.
|Read these latest SQL Interview Questions and Answers for Experienced that help you grab high-paying jobs|
Entity: A significant thing about which some information is required. For exp: EMPLOYEE (table). Attribute: Something that describes the entity. For exp: empno, emp name, emp address (columns). Tuple: A row in a relation is called Tuple.
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.
Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are:
Further, there are two locking methods:
Advantages of COMMIT and ROLLBACK statements are:
Naming rules to be considered for creating a table is:
A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the column if a row is inserted without a value for that column. The DEFAULT value can be a literal, an expression, or a SQL function such as SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.
Data Types is a specific storage format used to store column values. Few data types used in SQL are:
During modifying a column:
A LONG column is not copied when a table is created using a subquery. A LONG column cannot be included in a GROUP BY or an ORDER BY clause. Only one LONG column can be used per table. No constraint can be defined on a LONG column.
SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column.
A select * query will not retrieve data from unused columns. In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column. The SET UNUSED information is stored in the USER_UNUSED_COL_TABS dictionary view.
The main difference between Truncate and Delete is as below:
|SQL Truncate||SQL Delete|
|Removes all rows from a table and releases storage space used by that table.||Removes all rows from a table but does not release storage space used by that table.|
|TRUNCATE Command is faster.||DELETE command is slower.|
|Is a DDL statement and cannot be Rollback.||
Is a DDL statement and can be Rollback.
|Database Triggers do not fire on TRUNCATE.||
Database Triggers fire on DELETE.
CHAR pads blank spaces to a maximum length, whereas VARCHAR2 does not pad blank spaces.
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:
1. References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM Pseudo columns.
2. Calls to SYSDATE, UID, USER, and USERENV Functions
The main difference between Unique Key and Primary Key is:
|Unique Key||Primary Key|
|A table can have more than one Unique Key.||A table can have only one Primary Key.|
|The unique key column can store NULL values.||The primary key column cannot store NULL values.|
|Uniquely identify each value in a column.||Uniquely identify each row in a table.|
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 Covert 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.
The columns in a table that can act as a Primary Key are called Candidate Key.
A View logically represents subsets of data from one or more tables. 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:
The main differences between the two views are:
|Simple View||Complex View|
|Derives data from only one table.||Derives data from many tables. Contain functions or groups of data.|
|Contains no functions or group of data||Derives data from many tables. Contain functions or groups of data.|
|Can perform DML operations through the view.||Does not always allow DML operations through the view|
Few restrictions of DML operations on Views are:
You cannot DELETE a row if the View contains the following:
You cannot MODIFY data in a View if it contains the following:
You cannot INSERT data through a view if it contains the following:
Trigger is also the same as stored procedure & also it will automatically be invoked whenever DML operation performed against table or view.
There are two types of triggers supported by PL/SQL
Statement Level Trigger: In a statement-level trigger, the trigger body is executed only once for the DML statement.
Row Level Trigger: In a row-level trigger, the trigger body is executed for each row DML statement. It is the reason, we are employing each row clause and internally stored DML transaction in trigger specification, these qualifiers: old, new, are also called records type variables.
These qualifiers are used in trigger specification & trigger body.
When we use these qualifiers in trigger specification then we are not allowed to use “:” in form of the names of the qualifiers.
declare a exception begin If to_char(sysdate, ‘DY)=’THU’ then raise a; end if; exception when a then dbms_output.put_line(‘my exception raised on thursday’); end ;
Output: my exception raised on Thursday
declare v_sal number(10); begin select max(sal)intr v_sal; from emp; dbms_output.put_line(v.sal); end; /
declare A number(10); B number(10); C number(10); begin a:=70; b:=30; c:=greatest+(a,b); dbms_output.put_line(c); end; /
Declare cursor c1 is select sal from emp; v_sal number(10); n.number(10):=0; begin open c1; loop fetch c1 into v_sal; exit when c1%not found; n:=n+v_sal; end loop; dbms_output.put_line(‘tool salary is’||’ ‘ ||n); close c1; end; / Output: total salary is: 36975
Declare Cursor c1 is select ename, sal from emp; v_ename varchar2(10); v_sal number(10); begin open c1; loop fetch c1 into v_ename, v_sal; exist when c1 % notfound; dbms_output.put_line(v_name ||’ ‘||v_sal); end loop; close c1; end; /
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 triggers. These triggers are also called trigger predicate clauses.
|→ Explore Oracle PL SQL Sample Resumes Download & Edit, Get Noticed by Top Employers!|
This file extension is .dsc
Discard file we must specify within the control file by using the discard file clause.
The discard file also stores reflected records based on when clause condition within the control file. This condition must be satisfied in the table clause.
Oracle 7.2 introduced ref cursor, This is a user-defined type that 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 statements dynamically for a single active set area that's why those cursors are also called 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 the client application whereas the ref cursor is allowed to return multiple records into the client application (Java, .Net, PHP, VB, C++).
This is a user-defined type so we are creating it in 2 steps process i.e first we are creating a type then only we are creating a variable from that type that’s why this is also called a cursor variable.
In all databases having 2 ref cursors.
A strong ref cursor is a ref cursor that has a return type, whereas a weak ref cursor has no return type.
Type typename is ref cursor return record type data type; Variable Name typename
Type typename is ref cursor Variable Name typename;
In the Weak ref cursor, we must specify a select statement by using open for clause this clause is used in the executable section of the PL/SQL block.
Open ref cursor varname for SELECT * FROM table_name condition;
SQL> declare type t1 is table of number(10); v_t t1;=t1(10,20,30,40,50,60); beign v_t.trim(2); dbms_output.put_line(‘after deleting last two elements’); for i in v_t.first.. V_t.last loop dbms_output.put_line(v_t(i)); End loop; vt.delete(2); dbms_output.put_line(‘after deleting second element;); for i in v_t.first..v_t.last loop If v_t.exists(i) then dbms_output.put_line(v_t(i)); end if; end loop; end; /
Overload refers to the same name that can be used for a different purpose, in oracle we can also implement an overloading procedure through the package. Overloading procedure having the same name with different types or different numbers of parameters.
In oracle, we are declaring global variables in Package Specification only.
In oracle declaring procedures within the 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.
In oracle when we try to convert “string type to number type” or” data string into data type” then the oracle server returns two types of errors.
When PL/SQL block has a SQL statement and also those SQL statements try to convert string type to number type or data string into data type then oracle server returns an error: ora-1722-Invalid Number
For handling this error oracle provides number exception Invalid_number exception name.
begin Insert intoemp(empno, ename, sal) values(1,’gokul’, ‘abc’) exception when invalid_number then dbms_output.put_line(‘insert proper data only’); end;/
Whenever PL/SQL block having procedural statements and also those statements find to convert string type to number type then oracle servers return an error: ora-6502: numeric or value error: character to a number conversion error
For handling, this error oracle provided exception value_error exception name
begin declare z number(10); begin z:= ‘&x’ + ‘&y’; dbms_output.put_line(z); exception when value_error then dbms_output.put_line(‘enter numeric data value for x & y only’); end;/
Enter value for x:3 Enter value for y:2 z:=5 Enter value for x:a Enter value for y:b Error:enter numeric data value for x & y only.
Method1: using the timestamp
Method2: using scn number
PL/SQL consists of two major parts, they are package specification and package body.
These are the benefits of PL/SQL Packages
Tracing code is a necessary technique to test the performance of the code during runtime. We have different methods in PL/SQL to trace the code, which are,
In PL/SQL to retrieve and process more, it requires a special resource, and that resource is known as Cursor. A cursor is defined as a pointer to the context area. The context area is an area of memory that contains information and SQL statements for processing the statements.
An implicit cursor used in PL/SQL to declare, all SQL data manipulation statements. An implicit cursor is used to declare SQL statements such as open, close, fetch, etc.
An explicit cursor is a cursor and which is explicitly designed to select the statement with the help of a cursor. This explicit cursor is used to execute the multirow select function. An explicit function is used PL/SQL to execute tasks such as update, insert, delete, etc.
It is a program in PL/SQL, stored in the database, and executed instantly before or after the UPDATE, INSERT and DELETE commands.
Triggers are programs that are automatically fired or executed when some events happen and are used for:
Error handling part of PL/SQL is called an exception. We have two types of exceptions, and they are User-defined and predefined.
|Related article: Error Handling in SQL Server|
To delete the ‘Package’ in PL/SQL we use the DROP PACKAGE command.
The compilation process consists of syntax check, bind, and p-code generation. It checks the errors in PL/SQL code while compiling. Once all errors are corrected, a storage address allocated to a variable that stores this data. This process is called binding. P-Code consists of a list of rules for the PL/SQL engine. It is stored in the database and triggered when the next time it is used.
References: Stackoverflow | Scribd | Slideshare
If inserting then stmts; else if updating then stmts; else if deleting then stmts; end if;
Related Oracle Interview Questions
Priyanka Vatsa is a Senior Content writer with more than five years’ worth of experience in writing for Mindmajix on various IT platforms such as Palo Alto Networks, Microsoft Dynamics 365, Siebel, CCNA, Git, and Nodejs. She was involved in projects on these technologies in the past, and now, she regularly produces content on them. Reach out to her via LinkedIn and Twitter.