oracle interview questions for freshers(1)

1.What is the difference between a hot backup and a cold backup and the benefits associated with each?

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode.

Benefits:

The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2.I know the nvl function only allows the same data type(ie. number or char or date

Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead ofblank space. How do I write the query?

SQL> select nvl(to_char(comm.),’NA’) from emp;

Output :NVL(TO_CHAR(COMM),’NA’)

———————–

NA

300

500

NA

1400

NA

NA

3. Oracle cursor : Implicit & Explicit cursors

Oracle uses work areas called private SQL areas to create SQL statements.

PL/SQL construct to identify each and every work are used, is called as Cursor.

For SQL queries returning a single row, PL/SQL declares all implicit cursors.

For queries that returning more than one row, the cursor needs to be explicitly declared.

4. Explicit Cursor attributes

There are four cursor attributes used in Oracle

cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

5. Implicit Cursor attributes

Same as explicit cursor but prefixed by the word SQL

SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after

executing SQL statements.

: 2. All are Boolean attributes.

6. What is the maximum number of triggers, can apply to a single table?

              12 triggers.

7.what are actual and formal parameters

      Actual Parameters

The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.
Following procedure call lists two actual parameters named empno and amt:

raise_sal(empno, amt);

      Formal Parameters

The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.
Following procedure declares two formal parameters named empid and amt:

PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;

8.Explain an exception and its types

Exception is the error handling part of PL/SQL block. 

Exception is of two types: Predefined and user defined.

Some of Predefined exceptions are

• ZERO_DIVIDE
• NO_DATA_FOUND
• TOO_MANY_ROWS
• LOGON_DENIED
• CURSOR_ALREADY_OPEN
• INVALID_NUMBER
• NOT_LOGGED_ON
• STORAGE_ERROR
• PROGRAM-ERROR
• TIMEOUT_ON_RESOURCE
• VALUE_ERROR
• DUP_VAL_ON_INDEX
• INVALID_CURSOR.