Oracle Database Interview Questions & Answers Set-4

1. What are the differences between stored procedures and triggers?
Ans: A stored procedures are compiled collection of programs or SQL statements that live in the database. A stored procedure can access and modify data present in many tables. Also a stored procedure is not associated with any particular database object. But triggers are
even-driven special procedures which are attached to a specific database object.

2. What must be installed with ODBC on the client in order for it to work with Oracle? 
Ans: SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.

3. What are the different file extensions that are created by oracle reports?
Ans:  Rep file and Rdf file.

4. What is trigger? 
Ans: Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

5. Explain the difference between a data block, an extent and a segment?
Ans: 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.

6. What is bind reference and how can it be created?
Ans: Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a parameter name.

7. How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY? 
Ans: Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

8. What are various types of joins? 
  • Equi joins 
  • Cartesian joins 
  • Self join 
  • Outer join 

9. What is the maximum number of triggers, can apply to a single table?
Ans: 12 triggers

10. What command would you use to create a backup control file?
Ans: Alter database backup control file to trace.

11. What do you mean by GROUP BY Clause?
Ans: A GROUP BY clause can be used in select statement where it will collect data across multiple records and group the results by one or more columns.

Ans: The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.

13. What is key preserved table?
Ans: A table is set to be 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.

14. What is NULL value in oracle?
Ans: NULL value represents missing or unknown data. This is used as a place holder or represented it in as default entry to indicate that there is no actual data present.

15. What is COALESCE function?
Ans: COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL.

Coalesce(value1, value2,value3,…)