Monday 18 June 2012

oracle sql interview questions and answers

Oracle Interview Questions and Answers

101. HOW PL/SQL IS DIFFERENT FROM SQL?

Ans: SQL is non-procedural language whereas PL/SQL is procedural
language that includes features and design of programming language.

102. WHAT IS ARCHITECTURE OF PL/SQL?

Ans:   Give picture & Explain

103. WHAT IS A PL/SQL BLOCK?

Ans:     DECLARE
               <declarations>
                BEGIN
               <Exececutable Statements>
            EXCEPTION
               <Exception Handler(s)>
            END;

104. WHAT ARE DIFFERENT TYPES OF PL/SQL BLOCKS?

Ans: DECLARE BLOCK: In this block all the declarations of the variable
used in the program is made. If no variables are used this block will become optional.
          BEGIN BLOCK: In this block all the executable statements are
          placed. This block is Mandatory.
          EXCEPTION BLOCK: In this block all the exceptions are handled.
                     This block is also very optional.
          END: Every begin must be ended with this END; statement.

105. WHAT ARE COMPOSITE DATA TYPES?

Ans: Records, Tables are two Composite data types.

106. WHAT IS SCOPE OF A VARIABLE IN PL/SQL BLOCK?

Ans: The visuability and accessibility of a variable within the
block(s) is called scope of a variable.

107. WHAT IS A NESTED BLOCK?

Ans: A block within a block is called Nested Block.
  
108. WHAT IS A PL/SQL ENGINE?

Ans:  The PL/SQL engine accepts any valid PL/SQL block as input, executes the procedural part of the statements and sends the SQL statements to the SQL statement executor in the Oracle server.

109. WHAT IS DEFAULT VALUE FOR A NUMERIC PL/SQL VARIABLE?

Ans: NULL

110. WHAT IS DIFFERENCE BETWEEN SIMPLE LOOP AND A FOR LOOP?

Ans: Simple requires declaration of variables used in it and exit
condition but For Loop doesn’t require this.

111. WHAT IS A CURSOR? STEPS TO USE A CURSOR?

Ans: Cursor is Private SQL area in PL/SQL.
     Declare the Cursor,
     Open the Cursor,
     Fetch values from SQL into the local Variables,
     Close the Cursor.

112. HOW MANY TYPES OF CURSORS ARE SUPPORTED BY ORACLE?

Ans:  There are two types of cursors namely Implicit Cursor, Explicit Cursor.

113. WHAT IS A CURSOR FOR LOOP?

Ans: Cursor For Loop is shortcut process for Explicit Cursors because
the Cursor is Open, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.

114. WHAT ARE CURSOR ATTRIBUTES?

Ans:   %Found
          %NotFound
          %IsOpen
          %RowCount are the cursor attributes.

115. WHAT IS USE OF CURSOR WITH "FOR UPDATE OF" CLAUSE?

Ans: This Clause stop accessing of other users on the particular
columns used by the cursor until the COMMIT is issued.

116. WHAT IS AN EXCEPTION? HOW IT IS DIFFERENT FROM ERROR?

Ans: Whenever an error occurs Exception raises. Error is a bug whereas the Exception is a warning or error condition.

117. NAME SOME BUILT-IN EXCEPTIONS?

Ans:  Too_Many_Rows,  No_Data_Found,   Zero_Divide,    Not_Logged_On
         Storage_Error,    Value_Error etc.

118. HOW TO CREATE A USER-DEFINED EXCEPTION?

Ans: User-Defined Exception is created as follows:
      DECLARE
            <exception name> EXCEPTION;
            - - - - - - - - - ;
            - - - - - - - - -;
            BEGIN
            - - - - - - - - -;
            - - - - - - - - -;
            RAISE <exception name>;
            EXCEPTION
            WHEN <exception name> THEN
            - - - - - - - - -;
            - - - - - - - - -;
            END;

119. WHAT IS "OTHERS" EXCEPTION?

Ans: It is used to along with one or more exception handlers.
        This will handle all the errors not already handled in the block.

120. WHAT IS SCOPE OF EXCEPTION HANDLING IN NESTED BLOCKS?

Ans: Exception scope will be with in that block in which exception handler is written.

121. WHAT IS A SUB-PROGRAM?

Ans: A SUBPROGRAM IS A PL/SQL BLOCK, WHICH WILL BE INVOKED BY TAKING
PARAMATERS.

122. WHAT ARE DIFFERENT TYPES OF SUB-PROGRAMS?

Ans: THEY R TWO TYPES: 1) PROCEDURE 2) FUNCION.

123. HOW A PROCEDURE IS DIFFERENT FROM A FUNCTION?

Ans: Function has return key word and returns a value whereas a
Procedure doesn’t return any value.

124. WHAT ARE TYPES OF PARAMETERS THAT CAN BE PASSED TO FUNCTION OR PROCEDURE?

Ans: IN, IN OUT, OUT.

125. WHAT IS "IN OUT" PARAMETER?

Ans: A parameter, which gets value into the Procedure or Function and
takes the value out of the Procedure or
Function area, is called IN OUT parameter.

0 comments:

Post a Comment