Oracle interview questions and answers - 3

15. What are the attributes of Cursor?

Answer:

Every explicit cursor has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. These attributes return useful information about the execution of a SQL statement.

%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 entire cursor to give exact real count.

16. What are the various constraints used in Oracle?

Answer:

A Constraint restricts the values in a database. Oracle Database lets you create 6 types of constraints i.e.

Not Null - It ensures that a column cannot have a NULL value

Unique - It ensures that all values in a column are different

Primary Key - It is a combination of a NOT NULL and UNIQUE. It uniquely identifies each row in a table. It prohibits multiple rows from having the same value in the same column or combination of columns

Foreign Key - It uniquely identifies a row/record in another table. A foreign key constraint requires values in one table to match values in another table

Check - It ensures that all values in a column satisfies a specific condition

Default - It sets a default value for a column when no value is specified

17. What is difference between SUBSTR and INSTR?

Answers:

The substr function allows you to extract a substring from a string.

Select SUBSTR('The United States of America',1,3) from Country;
will return 'The'.

The instr function returns the location of a substring in a string.

INSTR('Oracle-SUBSTR-INSTR-Example','-',2)

Result : 14 i.e 2nd occurance of '-'

18. What are the different Oracle Database objects?

Answer:

There are different data objects in Oracle:

Table - set of elements organized in vertical and horizontal. Oracle stores information in the form of a table

View - Virtual table derived from one or more tables. It is a way of hiding the logic

Index - Performance tuning method to speed up the processing, especially searching

Synonym - Alias name for tables

Sequence - A sequence is a unique number generator which can be assigned to the primary keys of the tables.

Tablespace - Logical storage unit in Oracle

Constraint - A Constraint restrict the values in a database

19. What is a tablespace?

Answer:

A database is divided into one or more logical storage units called tablespaces. Oracle stores data logically in tablespaces and physically in datafiles.

20. What is bulk copy or BCP in Oracle?

Answer:

Bulk copy or BCP in Oracle, is used to import or export data from tables and views but it does not copy structure of same data.

The main advantage of BCP is fast mechanism for coping data and you can also take the backup of data easily.

21. What is RAW datatype?

Answer:

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