Database-Oracle [3i Infotech Placement]: Sample Questions 25 - 26 of 132

Glide to success with Doorsteptutor material for competitive exams : get questions, notes, tests, video lectures and more- for all subjects of your exam.

Question 25




A column defined as PRIMARY KEY can have NULL՚s




Understanding of Primary Key Constrain
  • Column defined as PRIMARY KEY cannot be null because primary key and unique key can both be used for uniquely identifying a row and unique key can have null value once.
  • PRIMARY KEY indicates a column or group of columns used as a unique identifier for rows in the table.
  • A unique constraint does not by itself it is provide a unique identifier because it does not exclude null value.
  • Primary key should be UNIQUE because a primary key identifies rows in a table so 2 different rows should not have the same key.
  • Use for PRIMARY KEY may be as foreign key in other table.
  • A primary key is cannot be null.

Question 26




Which of the following is not correct about Cursor?


Choice (4)


Cursor holds temporary results


Cursor is used for retrieving multiple rows


Cursor is a named Private SQL area


SQL uses implicit Cursors to retrieve rows




Define Cursor and Its Types


  • Used for retrieving set of rows.
  • Essentially formed by assigning a name to a SELECT statement and manipulate the information within SQL statement.
  • Contains information on a select statement and rows of data accessed by it.
  • Holds more than one row, but it can process only one row at a time.
  • Multiple rows that the cursor holds form the active set.

Two types of cursors:

  • Implicit Cursors
  • Explicit Cursor
Two Types of Cursors

Implicit Cursors

  • Automatically generated by oracle when an SQL statement is executed.
  • Created to process the statement when DML statements like INSERT, UPDATE, and DELETE etc. are executed.

Provide some attribute:

  • % FOUND
    • Return value is TRUE if DML statements like INSERT, DELET and Update at least one or more rows and return one or more rows.
    • Return value is TRUE if DML statement like INSERT, DELETE and UPDATE affect no row, or a SELECT INTO statement returns no row.
    • It is a opposite of % FOUND.
  • % ISOPEN
    • Returns FALSE for implicit cursor, because the SQL cursor automatically closes after execution.
    • Returns the number of rows affected by DML statement like INSERT, UPDATE, DELETE.
Provide Some Attribute

Explicit Cursors

  • Defined by programmers to gain more control on the context area.
  • Declaration section of the PL/SQL block.
  • Created on a SELECT statement which returns more than one row.


CURSOR cursor_name IS select_statement; ;

Steps of Creating Explicit cursor:

Declare the cursor:

  • Define name of cursor with a name and associated with SELECT statementSyntax:

    CURSOR name IS

    SELECT statement;

  • Open the Cursor

  • Allocate memory for the cursor to fetch the rows returned by the SQL statement. Syntax:

    OPEN cursor_name;

  • Fetch the Cursor

  • Access one row at a time. We can fetch row from the above-opened cursor. Syntax:

    FETCH cursor_name INTO variable_list;

  • Close the Cursor

  • Release the allocated memory


Close cursor_name;

Developed by: