50+ Oracle Interview Questions and Answers to Get a Job

Thinking of joining as an Oracle expert in any firm? Nothing can stop you from achieving your dream if you have worked hard for the same. Try to go through all the possible questions that are asked in an Oracle interview.

This will definitely help you to at least answer the questions that revolve around them. For your convenience, we have brought you a list of 50+ Oracle interview questions as well as their answers that will help you in getting a job.

Oracle Interview Questions and Answers

Q1. What is Oracle?

Ans: Oracle Database is a multi-model database management system created by the Oracle Corporation.

Q2. What is PL/SQL?

Ans: PL/SQL is a programming language. SQL as well as procedural programming language ensures seamless processing of SQL statements by improving its security, portability, and robustness of the database.

Q3. What are constraining triggers?

Ans: A trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.

Q4. What is the maximum number of columns a table can have?

Ans: The maximum number of columns in a table are 254.

Q5. What is RAW datatype?

Ans: RAW datatype is basically used to store values in binary data format. The maximum size for a RAW in a table is 32,767 bytes.

Q6. What is BLOB datatype?

Ans: A BLOB data type is a varying length binary string that is used to store two gigabytes (GB) memory. The length should be specified in Bytes for BLOB.

Q7. What is VArray?

Ans: VArray is an Oracle data type that is used to have columns containing multivalued attributes and it can hold bounded array of values.

Q8. What is the key preserved table?

Ans: A table is set to be a key preserved table if every key of the table can also be the key of the result of the join.

It guarantees to return only one copy of each row from the base table.

Q9. What is a hash cluster?

Ans: Hash Cluster is a technique used to store the table for faster retrieval. You can apply hash value on the table to retrieve the rows from the table.

Q10. How you can create a copy of the EMP table without any data?

Ans: CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE‘CHIRU’=’VENKY’;

Q11. What is ROWID?

Ans: Well, ROWID is a pseudo column attached to each row of a table.

Q12. What is the difference between a local variable and a global one?

Ans: In Oracle, global variables are declared at the beginning, and it’s accessible from anywhere. Local variables only have a local scope and they can only be accessed by the methods that created them.

Q13. What are SET operators?

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

Q14. What are the differences between DATE and TIMESTAMP in Oracle?

Ans: Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. TimeStamp datatype stores everything that Date stores and additionally it stores fractional seconds.

Date: 16:05:14
Timestamp: 16:05:14:

Q15. Explain the use of NVL function?

Ans: The NVL function is utilized for restoring NULL values with provided or different values. For example – NVL(Value, replace value).

Q16. What is a cursor? Why is the cursor needed?

Ans: A cursor is a defined private SQL area from where the data can be passed and executed or accessed. The cursors are required to process rows separately for queries returning numerous rows.

Q17. What is a cross join?

Ans: The cross join is defined as the Cartesian product of records from the tables present in the join. It will produce a result that connects each row from the first table with every row from the second table.

Q18. What are the various types of parameter modes in a procedure?

Ans: IN, OUT AND INOUT.

Q19. Do you know what is meant by a Candidate Key?

Ans: The columns in a table that can serve as a Primary Key is called Candidate Key.

Q20. What is a query to display a list of tables owned by the user?

Ans: The query can be written as:

SELECT tablespace_name, table_name FROM user_tables;

Q21. What is a MERGE statement in Oracle?

Ans: MERGE is a deterministic statement. It helps to insert or update rows in one table. It is also beneficial in data warehousing applications. This statement is a comfortable way to combine various operations.

Q22. Explain the advantage of using a stored procedure over a database trigger?

Ans: The main advantage is that we have control over the firing of a stored procedure but we have no control over the firing of a trigger.

Q23. What is the difference between a view and a synonym?

Ans: Synonym is Schema Object, just a second name of the table. View can be created with many tables, and with virtual columns, and with conditions.

Q24. What is a package cursor?

Ans: A cursor declared in the package specification without an SQL statement.

The SQL statement for the cursor is attached at runtime from calling procedures.

Q25. When to create indexes?

Ans: Indexes are to be created when the table is queried for less than 2% or 4% to 25% of the table rows.

Q26. What is the difference between foreign key and reference key?

Ans: Foreign key is an attribute that refers to another table primary key. Reference key is the primary key of a table referred to by another table.

Q27. Can you pass a parameter to a cursor?

Ans: Explicit cursors can take parameters, User create cursor called Explicit Cursor

CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;

Q28. What is a transaction?

Ans: Well, a transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

Q29. What is a database?

Ans: A database is a logical collection of data with some inherent meaning. The data is stored in rows and columns. Databases are used to store information.

Q30. Explain the Temporal data types in Oracle?

Ans: Oracle mainly provides these following temporal data types:

Date Data Type: Different formats of Dates.
TimeStamp Data Type: Has different formats of TimeStamp.
Interval Data Type: Interval between dates and time.

Q31. Briefly explain what is Literal?

Ans: A Literal is a string that contains a character, a number, or a date – that is included in the Select list and which is not a column name or a column alias.

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

Ans: The fastest query method to fetch data from the table is by using the Row ID. A row can be fetched from a table by using RowID.

Q33. Explain what is NULL value in Oracle?

Ans: The NULL value represents missing or unknown data. It is utilized as a placeholder or represented as a default entry to show that there is no actual data present.

Q34. What is the use of the CONSISTENT option in EXP command?

Ans: It specifies the read-only statement for export to guarantee data consistency.

Q35. What is a key preserved table?

Ans: A key preserved table is when every key contained in that table could also be the key that’s a result of a join.

Q36. What is DML?

Ans: DML full form is Data Manipulation Language. It is used to change data in objects that already exist. DML has statements, that are Insert, Select, Update and Delete.

Q37. What is a data dictionary?

Ans: A data dictionary is a set of read-only tables and views that contain metadata for a database. It is created when one creates a new database, and it gets updated as the database is updated.

Q38. What is the operation of HAVING clause?

Ans: The HAVING clause restricts the aggregated queries. After doing aggregation utilizing the GROUP BY clause, we can utilize the HAVING clause to append new conditions to the result.

Q39. What are the parts of a database trigger?

Ans: A triggering event or statement

A trigger restriction.
A trigger action.

Q40. What is an ALERT?

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

Q41. What is a deadlock?

Ans: A deadlock is when two users are trying to access the same information that is also locked by both parties for the opposite party. So it results in no one can access the data.

Q42. What’s the difference between UNIQUE and PRIMARY KEY Constraints?

Ans: PRIMARY KEY and UNIQUE columns can contain unique values, however UNIQUE columns can contain NULL data while a PRIMARY KEY one cannot.

Q43. What is a trigger?

Ans: A trigger is a program that runs automatically when a specific event happens. That event can be any operation. There are two types of triggers in Oracle: row level and statement Level.

Q44. What are snapshots and views?

Ans: Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables.

Q45. How many triggers can one table have?

Ans: One table cannot have more than 12 triggers.

Q46. What do you mean by MERGE statement in Oracle?

Ans: MERGE is a deterministic statement. It also helps to insert or update rows in one table. It is also beneficial in data warehousing applications. This statement is a comfortable way to combine various operations.

Q47. What is the difference between Oracle and SQL?

Ans: SQL, the language, can be used by Oracle to access objects. Oracle uses PL/SQL specifically.

Microsoft SQL Server is a database software, different and separate from Oracle. It uses the T-SQL language.

Q48. How do we make comments in Oracle code?

Ans: There are two ways to make comments in Oracle, a double dash (–) for a single line of comments, or /*–*/ to contain a block of comments.

Q49. Which language is used to develop Oracle?

Ans: Oracle has been developed using C Language.

Q50. Explain the difference between SUBSTR and INSTR?

Ans: SUBSTR returns a particular portion of a string.

INSTR gives a character position in which a pattern is discovered in a string.

SUBSTR returns string whereas INSTR returns numeric.

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

Ans: If you want to delete duplicate rows in the table then you can use ROWID.

Q52. What is Overloading of procedures?

Ans: Whenever the same procedure name is repeated with different parameters such as data types, and in different positions, varying numbers of parameters are referred to as Overloading of procedures.

Wrap Up

So these were the interview questions that will definitely help you in forming an idea about questions that are generally asked in an Oracle interview.

For more details on interview-related questions, you must remain connected with our website.