Q 1 - Which of the following is the correct syntax for creating an explicit cursor?

A - CURSOR cursor_name IS select_statement;

B - CREATE CURSOR cursor_name IS select_statement;

C - CREATE CURSOR cursor_name AS select_statement;

D - CURSOR cursor_name AS select_statement;

Q 2 - Which of the following code will open a cursor named cur_employee?

A - OPEN cur_employee;

B - OPEN CURSOR cur_employee;

C - FETCH cur_employee;

D - FETCH CURSOR cur_employee;

Q 3 - The following code tries to fetch some information from all the rows in a table named customers for use in a PL/SQL block. What is wrong in the following code?

DECLARE
   c_id customers.id%type;
   c_name customers.name%type;
   c_addr customers.address%type;
   CURSOR c_customers is
      SELECT id, name, address FROM customers;
BEGIN
   LOOP
      FETCH c_customers into c_id, c_name, c_addr;
      EXIT WHEN c_customers%notfound;
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
   END LOOP;
   CLOSE c_customers;
END;

A - It need not use a cursor.

B - The cursor is not opened.

C - It will not print information from all the rows.

D - There is nothing wrong in the code.

Q 4 - Which of the following is not true about PL/SQL records?

A - A PL/SQL record is a data structure that can hold data items of different kinds.

B - Records consist of different fields, similar to a row of a database table.

C - You can create table-based and cursor-based records by using the %ROWTYPE attribute.

D - None of the above.

Q 5 - Which of the following is not among the types of PL/SQL records?

A - Table-based

B - View-based

C - Cursor-based records

D - User-defined records

Q 6 - Which of the following code correctly create a record named book with two field title and author?

A - TYPE book IS RECORD

(title  varchar(50),
    author  varchar(50),
    );

B - RECORD book

(title  varchar(50),
    author  varchar(50),
    );

C - CREATE RECORD book

(title  varchar(50),
    author  varchar(50),
    );

D - CREATE TYPE book

(title  varchar(50),
    author  varchar(50),
    );

Q 7 - Which of the following code will successfully declare an exception named emp_exception1 in a PL/SQL block?

A - EXCEPTION emp_exception1;

B - emp_exception1 EXCEPTION;

C - CREATE EXCEPTION emp_exception1;

D - CREATE emp_exception1 AS EXCEPTION;

Q 8 - Consider the exception declared as −

emp_exception1 EXCEPTION;

Which of the following statement will correctly call the exception in a PL/SQL block?

A - IF c_id <= 0 THEN ex_invalid_id;

B - IF c_id <= 0 THEN CALL ex_invalid_id;

C - IF c_id <= 0 THEN RAISE ex_invalid_id;

D - IF c_id <= 0 THEN EXCEPTION ex_invalid_id;

Q 9 - The pre-defined exception CASE_NOT_FOUND is raised when

A - None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.

B - PL/SQL has an internal problem.

C - A cursor fetches value in a variable having incompatible data type.

D - None of the above.

Q 10 - The pre-defined exception NO_DATA_FOUND is raised when

A - A null object is automatically assigned a value.

B - A SELECT INTO statement returns no rows.

C - PL/SQL has an internal problem.

D - PL/SQL ran out of memory or memory was corrupted.

Q 11 - The pre-defined exception TOO_MANY_ROWS is raised when

A - PL/SQL ran out of memory or memory was corrupted.

B - A cursor fetches value in a variable having incompatible data type.

C - SELECT INTO statement returns more than one row.

D - None of the above.

Q 12 - Which of the following is not true about PL/SQL triggers?

A - Triggers are stored programs.

B - They are automatically executed or fired when some events occur.

C - Triggers could be defined on the table, view, schema, or database with which the event is associated.

D - None of the above.

Q 13 - Triggers are written to be executed in response to any of the following events −

A - A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

B - A database definition (DDL) statement (CREATE, ALTER, or DROP).

C - A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

D - All of the above.

Q 14 - Which of the following is not a benefit of a database trigger?

A - Enforcing referential integrity

B - Event logging and storing information on table access

C - Allowing invalid transactions

D - Generating some derived column values automatically

Q 15 - Observe the syntax given below −

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

The INSTEAD OF clause is used for creating trigger on a −

A - View

B - Cursor

C - Table

D - Index

Q 16 - Observe the syntax given below −

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

The {INSERT [OR] | UPDATE [OR] | DELETE} clause specifies a

A - DDL operation.

B - DML operation.

C - None of the above.

D - Both of the above.

Q 17 - Observe the syntax given below −

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

The optional [FOR EACH ROW] clause specifies

A - A table with index.

B - A table with primary key.

C - A row level trigger.

D - A table with a unique key.

Q 18 - Observe the syntax given below −

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

Which of the following holds true for the WHEN clause?

A - This provides a condition for rows for which the trigger would fire and this clause is valid only for row level triggers.

B - This provides a condition for rows for which the trigger would fire and this clause is valid only for table level triggers.

C - This provides a condition for rows for which the trigger would fire and this clause is valid only for view based triggers.

D - All of the above.

Q 19 - Observe the syntax given below −

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

Which of the following holds true for the [REFERENCING OLD AS o NEW AS n] clause?

A - This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.

B - OLD and NEW references are not available for table level triggers.

C - You can use them for record level triggers.

D - All of the above.

Q 20 - Which of the following is true for querying a table in the same trigger?

A - The AFTER keyword should be used, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

B - The BEFORE keyword should be used, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

C - None of the points in a. and b.

D - Both of the points in a. and b.

Q 21 - All objects placed in a package specification are called

A - Public objects.

B - Private objects.

C - None of the above.

D - Both of the above.

Q 22 - Any subprogram not in the package specification but coded in the package body is called a

A - Public object.

B - Private object.

C - None of the above.

D - Both of the above.

Q 23 - Which of the following is not true about PL/SQL packages?

A - PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.

B - A package has two parts: Package specification and Package body or definition.

C - Both the parts are mandatory.

D - None of the above.

Q 24 - Which of the following is not true about PL/SQL package specifications?

A - The specification is the interface to the package.

B - It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package.

C - It contains all information about the content of the package and the code for the subprograms.

D - None of the above.

Q 25 - Which of the following is true about PL/SQL package body?

A - The package body has the codes for various methods declared in the package specification and other private declarations.

B - It is created using the CREATE PACKAGE Statement.

C - The codes, methods and types declared in package body are not hidden from code outside the package.

D - All of the above.