Top 10 interview question for Oracle PL/SQL ?

1.          How can delete duplicate rows from table ?

Delete from table_name where rowid not in( Select max(rowid) from table_name

Group by duplicate_value_fields)

2.          How can find Max 5^th Salary from Salary table ?

Select * From (Select Column1, Column2 from table_name

Order by Column_Name) Where rownum <=5)

3.          What is NVL function into Oracle ?

In Oracle PL/SQL, the NVL function lets us substitute a value when a null value is encountered.

The syntax of NVL function is:

NVL( str1, replaceWith )

str1 is the string to test for a null value.

replaceWith is the value returned if str1 is null.

4.          What is Triggers ?

Triggers are similar to procedures, in that they are named PL/SQL blocks with declarative, executable, and exception-handling sections. However, a procedure is executed explicitly from another block via a procedure call, with can also pass arguments. A trigger is executed implicitly whenever the triggering event happens, and a trigger doesn’t accept arguments. The act of executing a trigger is known as firing the trigger. The triggering event is a DML (INSERT, UPDATE, or DELETE) operation on database table.

Triggers can be used for many things, including

§ Maintaining complex integrity constraints not possible through declarative constraints enabled at table creation

§ Auditing information in a table by recording the changes made and who made them

§ Automatically signaling other programs that action needs to take place when changes are made to a table

5.       What is Synonyms ?

Table references can be complicated, especially if a schema and/or database link is included. In order to make maintenance easier, Oracle allows you to create a synonym for a complicated reference. The synonym essentially renames the table reference, similar to an alias for a select list item. However, a synonym is a data dictionary object and is created by the CREATE SYNONYM DDL statement:

            CREATE SYNONYM synonym_name FOR reference;

6.       What is Cursor ?

A cursor is a handle, or pointer, to the context area. Through the cursor, a PL/SQL program can control the context area and what happens to it as the statements is processed. Types of cursor

§     Explicit cursor (%Found, %NotFound, %RowCount & %IsOpen)

§     Implicit cursor (%Found, %NotFound, %RowCount & %IsOpen)

7.       What is Identifiers ?

Identifiers are used to name PL/SQL objects, such as variables, cursors, and subprograms. Identifiers consist of a letter optionally followed by any sequence of characters including letters, numbers, dollar signs, underscores, and pound signs. Other characters are illegal. The maximum length for an identifier is 30 characters, and all characters are significant. For example

X

V_studentId

TempVar

8.       What is View ?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows.

      Example

CREATE VIEW v_Emp

AS SELECT First_Name, Last_Name, Phone, Country

FROM Emp_Master

9.       What is Sign Function ?

Its’ check the numeric value and return followings:

                     1 – for Positive value

                     0 – for Zero

                   -1 – for Negative value

10.   Exceptions into PL/SQL ?

1.       Too_many_rows

2.       No_data_found

3.       Value_Error

4.       Zero_Error

5.       Others