PLSQL MCQ

PLSQL FUNDEMENTALS MULTIPLE CHOICE QUESTIONS (31 QUIZZES)
1. PLSQL LANGUAGE FUNDEMENTALS

1.1 OVERVIEW OF PLSQL
1. Which of the following is true about PL/SQL programs?
ANS PL/SQL programs can exist with or without any SQL statements.
PL/SQL programs can exist only with any SQL statements.
PL/SQLprograms can exist only without any SQL statements.
SQL programs can exist only with PL/SQL statements.
2. What are composite variables in PL/SQL?
Native datatypes
ANS Variables having internal components
Scalar variables
User defined datatypes
3. What is a package in PL/SQL?
A package is a named PL/SQL unit stored in the database to perform action based on an event.
A package is an anonymous block in PL/SQL.
ANSA package is a schema object that groups logically related PL/SQL objects.
A package is a subprogram in the database.
4. How many parts of a PL/SQL block are optional?
3
ANS 2
1
0
5. What is an anonymous block in PL/SQL?
A PL/SQL unit without decalaration
A PL/SQL unit without a body to execute
A PL/SQL unit without an exception handler
ANSA PL/SQL unit without a name
6. Which command should be used to turn on the output of PL/SQL commands in SQL*Plus?
ANS set serveroutput on
showserveroutput on
set output on
set PL/SQL on
7. How does an execution block start and end in PL/SQL?
Starts with START and ends with END
ANS Starts with BEGIN and ends with END
Starts with START and ends with ; (semi colon)
Starts with BEGIN and ends with ; (semi colon)
8. How can a PL/SQL block be executed?
By using a semi colon at the end.
By using a colon at the end.
ANS By using a slash (/)at the end.
By pressing "Enter" .
1.2 LEXICAL UNITS AND DATATYPES
1. Which of the following is not a subtype of PLS_INTEGER datatype?
POSITIVEN
NATURALN
SIGN_TYPE
ANSSIMPLE_N_INTEGER
2. Which of the following is not true about keywords ?
Reserved words and keywords are identifiers that have special meaning in PL/SQL.
The difference between reserved words and keywords is that reserved words cannot be used as identifiers.
Keywords can be used as identifiers, but it is not recommended.
ANS Reserved keywords can be used as ordinary user-defined identifiers.
3. Which of the following is a not a user defined constrained datatype?
SUBTYPE Balance IS NUMBER;
ANSSUBTYPE Balance IS NUMBER(8,2);
SUBTYPE Name is VARCHAR2(10);
SUBTYPE Counter IS NATURAL;
4. Which of the following is not allowed in an identifier name?
Digits
The characters $, #, _,
ANS Punctuation or hyphens
Letters and Digits
5. What is an identifier in PL/SQL?
Identifier is a character, or character combination, that has a special meaning in PL/SQL.
ANS Identifiers provide a named reference to PL/SQL objects.
Identifier is a value that is neither represented by an literal nor calculated from other values.
Identifier is a string that is used to help other application developers understand the source text.
6. How are multi-line comments defined in PL/SQL?
By two hyphens (--)
ANS Starts with /* and ends with */
Starts with /$ and ends with $/
Starts with /^and ends with ^/
7. Which of the following is an example of Pre-defined identifier?
ANS The exception INVALID_NUMBER
EXCEPTION keyword
BEGIN and END keywords
lastName
8. What are the different types of identifiers?
Pre-defined and system defined identifier
ANS Pre-defined,user defined and reserved keywords
User defined and system defined identifier
Pre defined and Post defined identifier
1.3 ASSIGNING VALUES TO VARIABLES
1. What is the scope of a local declaration?
Subprogram
Session
ANS Block
Schema
2. Where can a variable beassigned values in PL/SQL?
Declaration block only
Executable block only
ANSDecalaration and Executable only
Exceutable and Exception block only
3. Which of the following are inherited by the referencing value from the referenced value while using a %TYPE attribute to
declare variables?
The referencing item inherits the Data type and size and Constraints always but never inherits the initial
value of the referenced item.
ANSThe referencing item inherits the Data type and size and Constraints (unless the referenced item is a column) but
never inherits the initial value of the referenced item.
The referencing item inherits the Data type , size , Constraints and the initial value of the referenced item.
The referencing item inherits the Data typeand size and Constraints (unless the referenced item is a
column) and the initial value of the referenced item.
4. How can values be assigned to a variable in PL/SQL?
Using assignnment operator only.
Using a subprogram only.
ANSUsing SELECT..INTO statement,a subprogram or an assignment operator.
Using SELECT..INTO statement, or an assignment operator only.
5. How should any declaration statement end in PL/SQL?
Using a colon (:)
Using a period (.)
ANSUsing a semi colon (;)
Using a slash (/)
6. Which of the following correctly describes the use of a %TYPE attribute?
ANSThe %TYPE attribute lets the user declare a data item of the same data type as a previously declared variable or
column.
The %TYPE attribute lets the user declare a data item of different data type as a previously declared variable
or column.
The %TYPE attribute lets the user declare a data item of the same data type as a previously declared row of
data.
The %TYPE attribute lets the user declare a data item of different data type as a previously declared row of
data.
7. While using SELECT INTO statements where are the database columns specified?
ANSAfter the SELECT and before INTO
After the SELECT and after INTO
After the SELECT and FROM clause and before INTO clause
After the SELECT ,FROM clause and INTO clause and before WHERE clause
8. What is scope and visibility of an identifier?
ANSThe scope of an identifier is the region of a PL/SQL unit from which the identifier can be referenced.
The scope of an identifier is the region of a PL/SQL unit from which the identifier can be referenced without
qualifying it.
The visibility of an identifier is the region of a PL/SQL unit from which the identifier can be referenced.
The visibility of an identifier is the region of a PL/SQL unit from which the identifier can be referenced by
qualifying it.
1.4 EXPRESSIONS IN PLSQL
1. Which of the following are correct results of a Boolean expression?
TRUE or FALSE
0 or 1
YES or NO
ANS TRUE, FALSE or NULL
2. Which one of these operators does not have the same precedence as the others?
BETWEEN
ANSAND
LIKE
IS NULL
3. What are the two types of case expressions?
Simple and Compound expression
Simple and Complex expression
ANSSimple and Searched expression
Simple and Multiple expression
4. Which of the following operators has the highest precedence?
BETWEEN
NOT
ANS**
| |
5. What happens if the none of the selector values match the selector in a simple case expression?
The CASE statement returns NULL.
An error is thrown.
ANSIt looks for an else statement. If no else clause is there, it returns NULL.
It looks for an else statement. If no else clause is there, it throws an error.
6. Where can a %be used in LIKE conditions in PL/SQL?
To match 1 character
ANSTo match 0 or more character
To match 1 or more character
To match 0 or 1 character
7. How is comparison made in Boolean character expression?
ANSComparisons are based on the binary values of each byte in the string.
Comparisons are based on the character values of each byte in the string.
Comparisons are based on the binary values of each character in the string.
Comparisons are based on the character values of each character in the string.
8. Which of the following operators does a logical expression use?
AND ,OR
AND, OR, NOR
ANSAND, OR, NOT
AND, OR ,NOR, NOT
1.5 SQL INSIDE PLSQL
1. How many rows can a SELECT query inside a PL/SQL execution block return?
One to Ten
ANS One only
One or many
One or Two
2. Which of the following statements does PL/SQL not support?
DML commands
ANSDDL commands like CREATE TABLE, ALTER TABLE
SELECT statement
Transaction statements like COMMIT, ROLLBACK
3. Which of the statements must be used inside a PL/SQLblock to remove data from a table?
DROP
MERGE
DROP
ANSDELETE
4. What can the VALUES clause of an INSERT statement have inside a PL/SQL block?
Column names and data
ANSPL/SQL variables or constants
Scalar PL/SQL variables only
PL/SQL constants only
5. What is a savepoint inside a PL/SQL block used for?
To rollback the entire transaction
To rollback until the savepoint and end the transaction
ANS To rollback until the savepoint only
To commit or rollback until the savepoint
6. Which of the following clause is not mandatory while using a SQL SELECT statement?
SELECT
INTO
FROM
ANS WHERE
7. Which of the DML statements can be used inside a PL/SQL block?
ANS INSERT, UPDATE, DELETE or MERGE
INSERT or UPDATE only
INSERT, UPDATE or DELETE
INSERT only
8. Using which of the following clauses is the MERGE condition specified?
INTO
USING
ANS ON
WHEN
1.6 RECORDS IN PLSQL
1. How can a user defined record be created in PL/SQL?
ANS Using TYPE statement
Using %ROWTYPE
Using%TYPE
Using CREATE TYPE statement
2. What is the initial value for a record variable declared with RECORD Type?
Value that is stored in the database
Zero for all fields
ANS NULL for all fields
NULL unless a different initial value is specified for it
3. Which of the following is not TRUE about using records for database inserts or updates?
If the VALUES clause of an INSERT statement contains a record variable, no other variable or value is
allowed in the clause.
ANS If the INTO subclause of aRETURNING clause contains a record variable, other variable are allowed in the
subclause.
Record variables are not allowed in a SELECT list, WHERE clause, or ORDER BY clause.
Record variables are allowed in the VALUES clause of an INSERT statement.
4. What is a %ROWTYPE attribute used for?
ANS To declare a record variable that represents a full or partial row of a database table.
To declare a record variable that represents a full row of a database table only.
To declare a record variable that represents a partial row of a database table only.
To declare a record variable from another record variable.
5. What are the conditions when a record variable is assigned to another record variable, the target variable is declared with a
RECORD type and the source variable is declared with %ROWTYPE?
An error is thrown
ANS The assignment is successful only when their fields match in number and order, and corresponding fields
have the same data type.
The assignment is successful as long as the fields in both source and target have the same datatype, even if
they do not match in number and order.
The assignment is successful only when their fields match in number, and corresponding fields have the
same data type. The order of the fields does not matter.
6. Which of the following about %ROWTYPE attribute is not true?
For every column of the full or partial row, the record has a field with the same name and data type.
If the structure of the row changes, then the structure of the record changes accordingly.
ANS The record fields inherit the constraints of the corresponding columns.
The record fields do not inherit the initial values of the corresponding columns.
7. Which of the following about a SELECT INTO statement is FALSE?
ANS The columns in select_list can be in any order as the record fields, as long as the number of fields matches.
For each column in select_list, the record variable must have a corresponding, type-compatible field.
The column names must precede the INTO keyword.
Any number of columns from the table can be fetched.
8. When can one record variable be assigned to another record variable?
ANS The fields of both variables match in number and order, and corresponding fields have the same data type.
The fields of both variables match in number and corresponding fields have the same data type.
The fields of both variables match in order, and corresponding fields have the same data type.
The fields of both variables match in number and order, but corresponding fields canhave different data
type.
1.END OF CHAPTER
1. Which of the TCL statements can be used inside a PL/SQL block?
COMMIT and ROLLBACK only
COMMIT only
ROLLBACK and SAVEPOINT only
COMMIT, ROLLBACK and SAVEPOINT
2. Which of the following statements does PL/SQL not support?
DML commands
DDL commands like CREATE TABLE, ALTER TABLE
SELECT statement
Transaction statements like COMMIT, ROLLBACK
3. What is an anonymous block in PL/SQL?
A PL/SQL unit without decalaration
A PL/SQL unit without a body to execute
A PL/SQL unit without an exception handler
A PL/SQL unit without a name
4. Which of the following operators does a logical expression use?
AND ,OR
AND, OR, NOR
AND, OR, NOT
AND, OR ,NOR, NOT
5. What is the result of a comparison expression?
Boolean only.
Any datatype depending on the expression.
BOOLEAN or NUMBER.
NUMBER or VARCHAR.
6. Where can a PL/SQL compilation and run-time system be installed?
Only in the database.
Only in any application development tool.
Only in the application server.
In the database or application development tool.
7. What is the initial value for a record variable declared with %ROWTYPE?
Value that is stored in the database
Zero for all fields
NULL for all fields
NULL unless a different initial value is specified for it
8. What is a package in PL/SQL?
A package is a named PL/SQL unit stored in the database to perform action based on an event.
A package is an anonymous block in PL/SQL.
A package is a schema object that groups logically related PL/SQL objects.
A package is a subprogram in the database.
9. Which of the following is not a subtype of PLS_INTEGER datatype?
POSITIVEN
NATURALN
SIGN_TYPE
SIMPLE_N_INTEGER
10. What are the mandatory fields while declaring a variable?
Name of the variable only
Name of the variable, datatype and a default value
Name of the variable and datatype only
Name of thevariable, datatype and NOT NULL constraint and default value
2. CONTROL STRUCTURES
2.1 CONDITIONAL STATEMENTS
1. In the following code when is the second ELSIF statement executed? IF condition-1 THEN statements-1 ELSIF condition-2
THEN statements-2 ELSIF condition-3 THEN statements-3 [ELSE ELSE statements] END IF;
Anytime. There is no particular order
When the first IF evaluates to FALSE
ANS When the first IF and the first ELSIF evaluates to FALSE
When the first IF and the first ELSIF evaluates to FALSE and ELSE also evaluates to FALSE
2. Which of the following is optional while using IF-THEN-ELSIF statements?
END IF
ANS ELSE
THEN
ELSIF
3. How many different types of Sequential control statements are there in PL/SQL?
1
ANS 2
3
4
4. Which of the following is false about an IF statement?
The condition of an IF statement can be a comparison expression or combination of multiple comparison
expressions.
In an IF statement, two variables of the same type or different types can be compared or two literals can be
compared.
Using an IF condition, only a portion of code can be executed depending on whether certain criteria are
met.
ANS An else statement cannot have another if statement inside.
5. How many ELSE clauses can an IF statement have?
ANS 0 or 1
always 1
1 or 2
1 to 10
6. Which of the following is equivalent to multiple IF-ELSIF statements?
LOOP statement
FOR statement
WHILE statement
ANS CASE statement
7. Which of the following is mandatory while using an IF statement?
ELSE clause
ELSIFclause
ANS END IF clause
Atleast one executable statement between IF and ENDIF
8. How many ELSIF clauses can an IF statement have?
0 or 1
ANS 0 or many
1 or 2
1 to 10
2.2 BASIC AND WHILE LOOP
1. Which of the following statements can be used to terminate a PL/SQL loop?
GOTO
ANS EXIT WHEN
CONTINUE WHEN
KILL
2. How many times does a WHILE Loop run?
Zero or one
Until the condition becomes TRUE
One or many
ANS Until the condition becomes FALSE.
3. Which of the following Loop statements can have EXIT and CONTINUE?
A while loop only
A while or a for loop only
ANS A while, for or basic loop
A while or a basic loop only
4. Which of the following statements can be used to skip some iterations of a Loop?
GOTO
EXIT WHEN
ANS CONTINUE WHEN
KILL
5. Where can EXIT and CONTINUE appear inside PL/SQL?
ANS Anywhere inside a loop but not outside a loop
Anywhere in a PL/SQL block, not necessary in a loop
Anywhere in the execution part of a PL/SQL block only
Anywhere in the execution and exception handling block of PL/SQL
6. Which of the following is not true about a WHILE Loop?
An EXIT statement terminates a WHILE Loop early
ANS The statements inside a WHILE Loop is always executed at least once
The condition of a WHILE loop is evaluated at the beginning of each iteration
The statements EXIT, EXIT WHEN, CONTINUE and CONTINUE WHEN has the same meaning inside a WHILE
Loop as it applied for a Simple Loop
7. Where is the control transferred while using a CONTINUE statement?
First statement after the end of the loop
ANS Next iteration of the current loop
First iteration of the current loop
Last iteration of the current loop
8. How many times does a BASIC Loop run?
Zero or one
ANS One or more
Zero or more
Only one always
2.3 FOR LOOP
1. When does a FOR Loop end?
Only when the index reaches the UPPER bound
ANS When the index reaches the UPPER bound or is terminated early using EXIT statement
When lower bound and upper bound are equal
Whenlower bound is greater than UPPER bound or is terminated early using EXIT statement
2. Which of the following correctly describes a FOR Loop?
The FOR LOOP statement runs one or more statements until the condition evaluates to FALSE.
ANS The FOR LOOP statement runs one or more statements while the loop index is in a specified range.
The FORLOOP statement runs one or more statements until an explicit EXIT statement terminates the loop.
The FOR LOOP statement runs one or more statements until the condition evaluates to TRUE.
3. What is the value of the FOR Loop index after the Loop runs?
0
1
ANS Undefined
-1
4. What is the datatype of the counter used in FOR Loop?
ANS INTEGER
Any Data type
INTEGER OR Character datatype
Any data type other than DATE
5. What can be the value of the lower and upper bounds of the FOR Loop?
Numeric literals only
ANS Numeric literal, Numeric variable or Numeric expression
Any Literal or expression
Numeric variables only
6. What is the initial value of index for a reverse for loop?
ANS UPPER bound
Lower bound
1
Upper bound-Lower bound/2
7. What happens in a REVERSE FOR Loop?
The output is printed in reverse order
Upper bound is always less than lower bound value
ANS Index is decremented for every iteration
Start and end bound values of the FOR Loop is non-numeric
8. What happens when the condition of the CONTINUE WHEN statement is not true in a FOR Loop?
ANS CONTINUE WHEN statement does nothing
CONTINUE WHEN statement throws an error
The loop is terminated
The current iteration is skipped
2.4 SEQUENTIAL CONTROL STATEMENTS
1. How does a GOTO statement transfer control?
Sequentially to the next statement always
Conditional or Unconditional
Conditionally always
ANS Unconditionally always
2. Which of the following is incorrect about GOTO statement?
ANS A GOTO statement can transfer control into an IF statement, CASE statement, LOOP statement, or sub-
block.
A GOTO statement cannot transfer control from one IF statement clause to another.
A GOTO statement cannot transfer control from one CASE statement WHEN clause to another.
A GOTO statement cannot transfer control out of a subprogram.
3. What is the scope of the label used in the GOTO statement?
The label should be defined in the same block or enclosing block as the GOTO statement
The label should be defined in the outer block as the GOTO statement
The label should be defined in the enclosing block or invoking block as the GOTO statement
ANS The label should be defined in the same scope as the GOTO statement
4. What happens when PL/SQL compiler hits a NULL statement?
ANS Control passes to the next statement
Control reaches the end of the code
Control goes to the next statement outside the loop
Control goes to the invoking code or user
5. Which of the following statements enable to branch logic from the normal flow of statements?
Loop statements
ANS Sequential control statements
Conditional control statements
Iterative statements
6. Which of the following statements is rarely used in PL/SQL?
WHILE statement
LOOP
ANS GOTO
Null Statement
7. Which of the following is not true about Sequential Control statements?
ANS Sequential control statements are crucial to PL/SQL programming.
Occasionally, GOTO statement simplifies logic enough to warrant its use.
NULL statement canimprove readability by making the meaning and action of conditional statements clear.
Sequential control statements are used to handle out of the ordinary requirements for sequential
processing.
8. What are the different types of sequential control statements?
GOTO & CONTINUE
ANS GOT & NULL
NULL & CONTINUE
GOTO & SKIP
2. END OF CHAPTER
1. What happens when PL/SQL compiler hits a NULL statement?
Control passes to the next statement
Control reaches the end of the code
Control goes to the next statement outside the loop
Control goes to the invoking code or user
2. Which of the following Loop statements can have EXIT and CONTINUE?
A while loop only
A while or a for loop only
A while, for or basic loop
A while or a basic loop only
3. What happens in a REVERSE FOR Loop?
The output is printed in reverse order
Upper bound is always less than lower bound value
Index is decremented for every iteration
Start and end bound values of the FOR Loop is non-numeric
4. Which of the following is incorrect about a label given in the GOTO statement?
The label must precede an executable statement.
The label can precede a PL/SQL block.
The label need not be unique in its scope.
The label can precede a NULL statement.
5. Which of the constructs can be used to evaluate a sequence of statements based on multiple conditions?
IF..THEN..ENDIF
IF..THEN.. ELSE...ENDIF END IF
IF..THEN..ELSEIF ..THEN..ELSE..ENDIF END IF
IF..THEN..ELSIF ..THEN..ENDIF
6. Which of the following is not true about a WHILE Loop?
An EXIT statement terminates a WHILE Loop early
The statements inside a WHILE Loop is always executed at least once
The condition of a WHILE loop is evaluated at the beginning of each iteration
The statements EXIT, EXIT WHEN, CONTINUE and CONTINUE WHEN has the same meaning inside a WHILE
Loop as it applied for a Simple Loop
7. What does a EXIT statement do in a FOR Loop?
Skip all further iterations of the loop
Terminate the loop
Skip a single iteration
Terminate the program
8. What happens if the condition following "CONTINUE WHEN" evaluates to FALSE?
An error is issued
The loop terminates
The current iteration is skipped
Nothing Happens
9. How many times does a WHILE Loop run?
Zero or one
Until the condition becomes TRUE
One or many
Until the condition becomes FALSE.
10. Which of the following statement is true for a NULL statement?
Null statement can improve readability by making the meaning and action of conditional statements clear.
Null statement simplifies logic enough to warrant its use.
Null Statement cannot transfer control from one IF statement clause to another.
Null Statement transfers control to a label or a block unconditionally.
3. CURSORS IN PLSQL
3.1 IMPLICIT CURSORS
1. Which of the following correctly defines a cursor?
ANS A cursor is a pointer to a temporary work area created in the system memory that stores information about
processing a specific SELECT or DML statement.
A cursor is a pointer to a permanent work area created in the system memory that stores information about
A cursor is a pointer to a temporary work area created in the system memory that stores information about
processing a specific DDL statement.
2. What is the name of the cursor written and maintained by a database user?
User cursor
ANS Explicit cursor
Implicit cursor
User-defined cursor
3. What is the syntax of an implicit cursor attribute ISOPEN?
ANS %ISOPEN
#ISOPEN
&ISOPEN
!ISOPEN
4. What does %FOUND attribute indicate?
It indicates that the cursor was found.
It indicates that the number of rows that got affected.
ANS It indicates whether any rows were affected.
It indicates whether any rows were not affected.
5. When is an implicit cursor used?
PL/SQL opens an implicit cursor every time a SELECT or DML statement is run and the statements are not
associated with any cursor variable.
PL/SQL opens an implicit cursor only every time a SELECT statement is run even if the statements are
associated with any explicit cursor.
PL/SQL opens an implicit cursor every time a DDL statement is run and the statements are not associated
with any explicit cursor.
ANS PL/SQL opens an implicit cursor every time a SELECT or DML statement is run and the statements are not
associated with any explicit cursor.
6. Which type of cursor does an implicit and explicit cursor belong to?
System cursor
ANS Session cursor
Database cursor
Operating System cursor
7. Which of the following operations of a cursor checks for the validity of a SQL statement?
OPEN
BIND
FETCH
ANS PARSE
8. How many rows can a cursor hold?
One only
Zero only
ANS One or more
One to ten
3.2 EXPLICIT CURSORS
1. When should a CLOSE statement be used in PL/SQL?
For both implicit and explicit cursors
ANS Only for explicit cursors
Only for implicit cursors
Only for cursor expressions
2. What can the value of the INTO clause be while fetching data in an explicit cursor?
PL/SQL scalar variables only
ANS PL/SQL variables or records
Collections or records only
Records only
3. Which of the following statements about opening and closing an explicit cursor is false?
An explicit cursor is closed with the CLOSE statement.
ANS A cursor once closed cannot be re-opened.
Open statement identifies the result set and positions the cursor before the first row of the result set.
Optionally arguments can be passed to the open statement to open a cursor
4. What is the datatype of the return value of SQL%ROWCOUNT attribute?
INTEGER
NUMBER
ANS PLS_INTEGER
BOOLEAN
5. When should a column fetched in an explicit cursor have an alias?
An alias should never be used
Always an alias should be used for columns in an explicit cursor
ANS When the query includes a virtual column or an expression and that is referenced in the query
The cursor is used to fetch into a record that was declared with %ROWTYPE
6. Which of the following statements about a cursor declaration and definition is true?
An explicit cursor should be declared and defined at the same time in the execution block.
ANS An explicit cursor can bedeclared first and then defined later or both can happen at the same time in the
declaration block.
An explicit cursor should be declared and defined at the same time in the declaration block.
Cursor declaration and definition are the same and is a single step
7. What is the value of %ROWCOUNT after the explicit cursor is opened but before the first row is fetched?
1
ANS Zero
-1
NULL
8. Which of the following SQLAttributecan be used to determine the exit condition of the loop while fetching data from an
explicit cursor?
%FOUND
%ISOPEN
%ROWCOUNT
ANS %NOTFOUND
3.3 RESULT SET PROCESSING WITH CURSORS
1. What is the scope of the index of the CURSOR FOR LOOP?
ANS Local to the loop
Global
Local to the loop and enclosing loops
Block scope (in the entire Pl/SQL block)
2. Why is the WHERE CURRENT OF CLAUSE used in Cursors?
The WHERE CURRENT OF clause allows the user to easily make changes to the least recently fetched row of
data.
The WHERE CURRENT OF clause allows the user to easily make changes to all the rows of a table.
ANS The WHERE CURRENT OF clause allows the user to easily make changes to the most recently fetched row of
data.
The WHERE CURRENT OF clause allows the user to easily make changes to the data not fetched by the
recent query.
3. What is an implicit CURSOR FOR LOOP statement?
SELECT statement specified outside the CURSOR FOR LOOP statement.
Cursor specified in the CURSOR FOR LOOP statement.
Cursor specified in any LOOP statement.
ANS SELECT statement specified inside the CURSOR FOR LOOP statement.
4. When should an alias be used in a CURSOR FOR LOOP?
Always
ANS Only when virtual columns are used
Never
When the number of tables are more than one
5. What can a CURSOR FOR LOOP use?
Explicit cursor only
Implicit cursor only
ANS Explicit or Implicit cursors
Cursor variables only
6. For which of the following statements can a CURRENT OF clause be used?
INSERT, DELETE or UPDATE
ANS DELETE or UPDATE
SELECT or INSERT
SELECT or UPDATE
7. How is the Loop index of a CURSOR FOR LOOP declared?
It is explicitly declared as a %TYPE record variable of the type that its cursor returns.
It is implicitly declared as a %TYPE record variable of the type that its cursor returns.
It is explicitly declared as a %ROWTYPE record variable of the type that its cursor returns.
ANS It is implicitly declared as a %ROWTYPE record variable of the type that its cursor returns.
8. What is the correct syntax of using FOR UPDATE clause in CURSORS?
ANS CURSOR cr_name IS SELECT column_names FROM tbl FOR UPDATE;
CURSOR cr_name IS FOR UPDATE SELECT column_names FROM tbl ;
CURSOR cr_name IS SELECT column_names FOR UPDATE FROmtbl;
CURSOR cr_name IS SELECT FOR UPDATE column_names FROM tbl ;
3.4 CURSOR VARIABLES AND EXPRESSIONS
1. Which of these is not a similarity between an explicit cursor and cursor variable?
The way cursor is closed
The way data is fetched
The value of cursor attributes
ANS The way cursor is opened
2. Which of the following is incorrect about cursor variables?
A cursor variable can be associated with different queries at different times in the program execution
A value can be assigned to a cursor variable and it can be used in an expression
ANS Parameters can be passed to a cursor variable.
Cursor variables can be used to reduce client-server network traffic
3. What is SYS_REFCURSOR?
A user defined REF CURSOR that is weakly typed
ANS A predefined REF CURSORthat is weakly typed
A predefined REF CURSOR that is strongly yped
A user defined REF CURSOR that is strongly typed
4. Which of these have same attributes?
Implicit and explicit cursors
ANS Implicit cursor, explicit cursor and cursor variables
Explicit cursors and cursor variables
Implicit cursors and cursor variable
5. Which of the following statements is FALSE?
Integer is a subtype of Number
ANS List<Integer>is a subtype of List<Number>
List<Integer>is not a subtype of List<Number>
Number is a subtype of Object
6. Which of the following types of cursor variables has a RETURN clause?
ANS Strongly typed
Weakly typed
Both strongly and Weakly Typed
Neither. There is no RETURN clause in any cursor variable
7. Which of the following statement for opening a cursor variable is correct?
OPEN cursor_nameAsselect_statement
OPEN cursor_name
ANS OPEN cursor_name FOR select_statement
OPEN cursor_name IS select_statement
8. In which of the following places can a cursor expression be used?
With an implicit cursor
In view definitions
In any form of SELECT statement
ANS In a SELECT statement that is not a subquery.
3. END OF CHAPTER
1. Which of the following correctly defines a cursor?
2. How many attributes are there for a cursor variable?
2
1
4
3
3. How does a CURSOR FOR LOOP statement end?
Closes implicitly after the size of the record variable is reached.
Closes implicitly when an error is raised.
Closed explicitly by a EXIT statement inside the loop
When there are no more rows to fetch, the CURSOR FOR LOOP statement closes the cursor.
4. How can virtual columns be accessed in a CURSOR FOR LOOP?
Using the column names
Using aliases
They cannot be accessed
Using table names dot column names
5. For which of the following statement is it not recommended to use SQL%NOTFOUND attribute?
DELETE
UPDATE
SELECT INTO
INSERT
6. Which of the following statement about passing a parameter to an explicit cursor is incorrect?
Cursor parameters can be assigned default values.
An explicit cursor that has formal parameters can be created, and then different actual parameters can be
passed to the cursor each time it is opened.
Formal and actual parameters of the cursor must always have the same name.
A parameter in an explicit cursor avoids scoping problems
7. When can an explicit cursor be opened?
After definition before declaration
After closing and before definition
After declaration and definition
After declaration and before definition
8. When should an alias be used in a CURSOR FOR LOOP?
Always
Only when virtual columns are used
Never
When the number of tables are more than one
9. How many rows can a cursor hold?
One only
Zero only
One or more
One to ten
10. What is FOR UPDATE cursor?
SELECT FOR UPDATE associated with an implicit cursor
SELECT FOR UPDATE associated with a cursor variable
SELECT FOR UPDATE associated with an explicit cursor
SELECT FOR UPDATE associated with a dynamic cursor
4. EXCEPTION HANDLING
4.1 EXCEPTIONS IN PLSQL
1. How can an exception name be associated with a error code of a internally defined exception?
Using PRAGMA EXEC_INIT
ANS Using PRAGMA EXECPTION_INIT
Using PRAGMA EXEC_INITIALIZE
Using PRAGMA EXECPTION_INITIALIZE
2. Which of the following is the correct syntax for exception handlers?
IF ex_nameTHENstmt
ANS WHEN ex_name THEN stmt
WHERE ex_name THEN stmt
CASE ex_name THEN stmt
3. What are internally defined exceptions?
ANS The Oracle ORA errors
Exceptions which are already defined in the STANDARD package
Exceptions defined by a user
Any error that occurs in PL/SQL?
4. How can a user defined exception be raised?
Using RAISE statement only
Using RAISE statement or RAISE_APPLICATIOn_ERROR function
Using INVOKE statement or RAISE statement
ANS Using RAISE statement or RAISE_APPLICATIOn_ERROR procedure
5. What are exceptions in PL/SQL?
ANS Runtime errors
Runtime warnings
Compile time errors
Compile time warnings
6. Which of the following exception is globally available?
Internal ,User-defined and Pre-defined exceptions
ANS Pre-defined exceptions only
Internal and pre-defined exceptions
User defined exceptions only
7. What happens after an exception handler runs?
The program exits
Control transfers to the last statement of the last block
ANS Control transfers to the next statement of the enclosing block
Control transfers to the last statement of the current block
8. Where are exceptions used in PL/SQL?
Only in an anonymous block
Only in the body of a subprogram
Only in a package
ANS Only in an anonymous block and the body of a subprogram
4.2 EXCEPTION PROPAGATION
1. What happens if astored subprogram exits with an unhandled exception?
PL/SQL rolls back database changes made by the subprogram.
ANS PL/SQL does not roll back database changes made by the subprogram.
PL/SQL commits database changes made by the subprogram.
PL/SQL removes the subprogram from the database.
2. Which function returns the error message associated with the most recently raised error exception?
ANS SQLERRM
SQL_ERRM
SQLERRMSG
SQL_MSG
3. What happens if an exception raised in a block has no exceptionhandler for it?
Exception reproduces itself in successive nested blocks.
Exception reproduces itself in successive invoking blocks.
ANS Exception reproduces itself in successive enclosing blocks.
Exception reproduces itself in successive enclosing programs.
4. What is an unhandled exception?
ANS An exception raised in the program is not handled by an exception section in either the current or enclosing
PL/SQL blocks.
An exception raised in the program is not handled by any section in the current PL/SQL block.
An exception raised in the program is not handled by an exception section in any of the enclosing PL/SQL
blocks.
An exception raised in the program is not handled by an execution section in either the current or enclosing
PL/SQL blocks.
5. What does an unhandled exception do?
Crashes the database server.
Prints a warning and continues.
Control goes to the invoker without any effect.
ANS Halts the execution of the host program.
6. What happens if a subprogram which has OUT and IN OUT formal parameter exits with an unhandled exception?
The actual parameters for OUT and IN OUT formal parameters passed by reference retain the values that
they had before the subprogram invocation.
The values of actual parameters for OUT and IN OUT formal parameters passed by value are changed from
what they had before the subprogram invocation.
ANS The actual parameters for OUT and IN OUT formal parameters passed by value retain the values that they
had before the subprogram invocation.
The values of actual parameters for OUT and IN OUT formal parameters passed by reference are changed
from what they had before the subprogram invocation.
7. Which of the following functions gives the error code of the most recently occurred exception?
SQLERRCODE
SQLERROR
ERRCODE
ANS SQLCODE
8. What does PL/SQL do with an unhandled exception?
Returns it to database
Returns it to invoker always
Returns it to host environment always
ANS Returns it to invoker or host environment
4. END OF CHAPTER
1. What is an unhandled exception?
An exception raised in the program is not handled by an exception section in either the current or enclosing
PL/SQL blocks.
An exception raised in the program is not handled by any section in the current PL/SQL block.
An exception raised in the program is not handled by an exception section in any of the enclosing PL/SQL
blocks.
An exception raised in the program is not handled by an execution section in either the current or enclosing
PL/SQL blocks.
2. Which of the following exception is globally available?
Internal ,User-defined and Pre-defined exceptions
Pre-defined exceptions only
Internal and pre-defined exceptions
User defined exceptions only
3. What happens if an exception A is raised and there are no handlers for it in the current block?
An error is thrown
The program exits abruptly
Nothing happens and a compile time warning is issued
The exception propagates
4. What happens if an exception is raised in a declaration part of a PL/SQL block?
To the enclosing block or to the invoker or host environment if there is no enclosing block.
Always to the invoker or host environment
To the inner most block
An exception cannot be raised in a declaration block
5. What is exception propagation?
A raised exception not being handled.
An exception not being raised implicitly or explicitly.
An exception reproduces itself in successive enclosing blocks until either a block has a handler for it or there
is no enclosing block.
A raised exception handled only by invoker.
6. What does PL/SQL do with an unhandled exception?
Returns it to database
Returns it to invoker always
Returns it to host environment always
Returns it to invoker or host environment
7. How can unhandled exceptions be avoided in PL/SQL?
By using "OTHERS" handler
By using "ALL" handler
By using "ALLOTHERS" handler
By using "OTHER" handler
8. What happens if a subprogram which has OUT and IN OUT formal parameter exits with an unhandled exception?
The actual parameters for OUT and IN OUT formal parameters passed by reference retain the values that
they had before the subprogram invocation.
The values of actual parameters for OUT and IN OUT formal parameters passed by value are changed from
what they had before the subprogram invocation.
The actual parameters for OUT and IN OUT formal parameters passed by value retain the values that they
had before the subprogram invocation.
The values of actual parameters for OUT and IN OUT formal parameters passed by reference arechanged
from what they had before the subprogram invocation.
9. What happens after an exception handler runs?
The program exits
Control transfers to the last statement of the last block
Control transfers to the next statement of the enclosing block
Control transfers to the last statement of the current block
10. What is the effect of this statement? RAISE;
A runtime error is thrown
Re-raises the previous exception
Re-raises the current exception
Does not compile since RAISE should be followed by the name of the exception
5. SUBPROGRAMS IN PLSQL
5.1 OVERVIEW OF SUBPROGRAMS
1. Which part of a subprogram heading is optional?
Name of the subprogram
Declaration part of a subprogram
ANS Parameters
Exception handling part
2. Which of the following is not a subprogram?
Procedure
Function
ANS Package
Anonymous PL/SQL block
3. Where can a subprogram be created?
Inside a PL/SQL block or a package only.
ANS Inside a PL/SQL block, inside a package, or at schema level.
Inside a package only.
Inside a PL/SQL block only.
4. Which of the following subprogram is stored in the database?
ANS Standalone subprogram
Nested subprogram in an anonymous block
Package subprogram
Standalone and nested subprogram
5. When should forward declaration be used in procedures?
Always for nested procedures
ANS If nested subprograms in the same PL/SQL block invoke each other
For packaged subprograms always
For two standalone subprograms invoking each other
6. What is a standalone subprogram?
ANS A subprogram created at the schema level
A subprogram created at the database level
A subprogram created at the user level
A subprogram created at the session level
7. Which of the following part of a PL/SQL subprogram is mandatory?
Executable and exception handling part only
Declaration, Executable and Exception handling part
Declaration and Executable part
ANS Executable part only
8. Which of the following differentiates a declaration part of a subprogram from an anonymous block?
ANS The declaration part of a subprogram never begins with DECLARE keyword.
Declaration part of a subprogram is not mandatory whereas that of an anonymous block is mandatory.
Declaration part of a subprogram can be written after the execution part whereas this is not possible in an
anonymous block.
The declaration part of an anonymous block always begins with the DECLARE keyword whereas declaration
part of a subprogram begins with DEFINE.
5.2 FUNCTIONS IN PLSQL
1. How many RETURN statements can a function have?
Zero or more
Zero or one
Always one only
ANS One or more
2. Which of the following is incorrect about functions?
A function is a module that returns a value.
ANS A function is a standalone executable statement.
A function can be said to have a datatype.
A function can be used in place of an expression in a PL/SQL statement.
3. What is a standalone function?
It is the function created in memory
It is the function inside another PL/SQL lock
ANS It is the function that is stored in the database
It is the function that cannot have any parameters
4. Which of the following is not a difference between procedure and function?
Functions can be called from SQL whereas procedures cannot be called.
A function can be used in place of an expression in a PL/SQL statement whereas a procedure cant be used
so.
The return statement is mandatory for a function which is not for a procedure
ANS Functions are used for executing business logic and computation whereas a procedure is not.
5. What does a return statement in a function do?
ANS It returns control to the calling program and returns the results of the function.
It returns control to the calling program only if there are no results to show.
It only returnsthe results of the function if any.
It terminates the function without returning any results and returns control to the calling program.
6. What should be done to change the definition of an existing function?
DROP the function and create it again with CREATE function syntax.
Use ALTER function to re-create the function
A function once created cannot be changed later
ANS Use CREATE or REPLACE to overwrite the existing function.
7. Where are functions normally used?
ANS To perform computation
To do some business logic
To transfer data
To take IN OUT parameters
8. Which of the following is incorrect about a RETURN statement?
A function can have more than one RETURN statement.
ANS All the RETURN statements is executed each time the function is called.
The RETURN statement can accept any expression for evaluation and return.
When a RETURN statement is processed, the function terminates immediately and returns control to the
calling PL/SQL block
5.3 SUBPROGRAM PARAMETERS
1. Which of thefollowing subprograms can use parameters?
ANS Procedures and Functions
Procedures only
Functions only
Packages only
2. What is the method by which parameters are passed when the actual and formal parameters refer to the same memory
location?
ANS By Reference
By Value
By copy
By default values
3. Which are the optional parts of a formal parameter declaration?
Name, Datatype, Mode and default value
Datatype, Mode and default value
ANS Mode and default value
Default value only
4. Whichof the following is incorrect about actual parameters in a procedure?
The actual parameters are the values or expressions placed in the parameter list of the actual call to the
function or procedure.
When invoking the procedure or function, the actual parameters are specified whose values are to be
assigned to the formal parameters.
Corresponding actual and formal parameters must have compatible data types.
ANS Same names should be given for formal and actual subprogram parameter always.
5. When does the compiler implicitly convert the data type of the actual parameter to the data type of the formal parameter?
When passed by reference
Always, when passed by any method
It never converts implicitly and has to be explicitly done by user
ANS Whenpassed by value
6. What are the two different types of values for a parameter?
Default and System given
ANS Default and user given
System given and user given
Compile time and run time values
7. What happens when the data type of the actual parameter is not the same as the data type of the formal parameter?
An error is thrown.
The parameter is skipped and not passed.
ANS Compiler performs an implicit conversion and if possible no error is thrown.
The PL/SQL code abruptly terminates.
8. Howis the formal parameter treated when the mode of the parameter is IN OUT?
Formal parameter acts like an uninitialized value.
Formal parameter is initialized to the default value of its type
ANS Formal parameter acts like an initialized variable
Formal parameter acts like a constant.
5.4 OVERLOADED SUBPROGRAMS
1. What happens when we execute two subprograms that cannot be overloaded?
ANS Compile time error
Compile time warning
Run time error
Run time warning
2. How is a mixed notation used for matching formal and actual parameters?
First two parameters is by position and others are by names
Any number of parameters can be passed by position and the rest are passed by names
ANS First parameter is by position and others are by names
Last parameter is by position and others are by names
3. What happens when we specify parameters using named notations in wrong order?
ANS There is no wrong order while using named notations
This can cause problems that are hard to detect.
These problems are detected using PL/SQL compiler
Run time errors are thrown when such a problem occurs
4. What is the result of overloading of these two procedures? PROCEDURE s (p INTEGER) IS PROCEDURE s (p REAL) IS
Successful Error because formal parameters differ only in mode Error because the name of the procedures are same Error
because formal parameters differ only in subtype
Successful
Error because formal parameters differ only in mode
Error because the name of the procedures are same
ANS Error because formal parameters differ only in subtype
5. What are overloaded subprograms?
ANS Two or more subprograms that can have the same name with different parameter lists.
Two subprograms that can have different name with different parameter lists.
Two or more subprograms that can have the same name with same parameter lists.
Two or more subprograms that can have different name with same parameter lists.
6. Where can overloaded subprograms be used in PL/SQL?
Anywhere in PL/SQL
Inside an anonymous block only
ANS Inside the declaration section of a PL/SQL block or inside a package
Inside a package only
7. Which of the following subprograms cannot be overloaded?
Nested subprogram
ANS Standalone subprogram
Package subprogram
Nested and Package subprogram
8. What is the statement to remove a procedure from a database?
ALTER PROCEDURE
ANS DROP PROCEDURE
DELETE PROCEDURE
TRUNCATE procedure.
5. END OF CHAPTER
1. Which of the following is incorrect about a nested subprogram?
A subprogram created inside a PL/SQL block is a nested subprogram.
A nested subprogram is always stored in the database.
A nested subprogram can be part of a standalone or package subprogram.
A nested subprogram can be inside another subprogram.
2. How should a procedure be called?
As part of an expression.
In a SQL statement.
Inside another PL/SQL block by assigning the result to another variable.
As a standalone statement.
3. How is a function used in PL/SQL?
As a standalone executable statement
As part of an expression
As an unnamed PL/SQL block
As a package
4. What should be different between any overloaded subprograms?
Subprograms can be overloaded if their formal parameters differ in name or number only
Subprograms can be overloaded if their formal parameters differ in name, number, order, or data type
family.
Subprograms can be overloaded if their formal parameters differ in name or data type family only.
Subprograms can be overloaded if their formal parameters differ in number, order, or data type family only
5. Which of the following subprogram is stored in the database?
Standalone subprogram
Nested subprogram in an anonymous block
Package subprogram
Standalone and nested subprogram
6. Which of the followingis incorrect about actual parameters in a procedure?
The actual parameters are the values or expressions placed in the parameter list of the actual call to the
function or procedure.
When invoking the procedure or function, the actual parameters are specified whose values are to be
assigned to the formal parameters.
Corresponding actual and formal parameters must have compatible data types.
Same names should be given for formal and actual subprogram parameter always.
7. Which part of a subprogram heading is optional?
Name of the subprogram
Declaration part of a subprogram
Parameters
Exception handling part
8. How is the actual parameters passed when the parameter is passed in IN mode?
By value always
By reference only
By value or by reference
No parameter can be passed into the subprogram in the IN mode.
9. What does the keyword "REPLACE" do while creating a procedure?
Overwrites the definition of a nested procedure.
Drops the existing procedure and creates it again.
It overwrites the definition of a standalone procedure if it exists, else creates the procedure.
REPLACE should not be used while creating a procedure but can be used separately later after a standalone
procedure is created.
10. Where a forward declared procedure should be defined?
In the same block
In the same or enclosing block
In the same or nested block
In the same or invoker block
6. COLLECTIONS IN PLSQL
6.1 ASSOCIATIVE ARRAYS
1. What are the types of composite datatypes?
Records and Nested table
Collections and BOOLEAN
Collections, PLS_INTEGER and BOOLEAN
ANS Collections and Records
2. Which of the following statement is true for a collection?
ANS In a collection, the internal components always have the same data type.
In a collection, the internal components always have different data type
In a collection, the internal components may have the same data type or different datatype.
All collection except records have the same datatype.
3. How are the elements of a collection variable accessed?
variable_name[index]
ANS variable_name(index)
variable_name.index
variable_name{index}
4. What is the clause that differentiates creating an associative array from other collections?
ANS INDEX BY
INDEXED BY
INDEX OF
INDEX WITH
5. What are the different types of composite datatypes in PL/SQL?
Associative array or Nested tables only
Associative array, Varray and Nested tables
ANS Associative array, Varray ,Nested tables and Records
Associative array and Records only
6. Which of the following function gives the number of elements in an associative array?
MAX
MAXIMUM
ANS COUNT
TOTAL
7. What is the state of an uninitialized associative array variable?
Error
NULL
Undefined
ANS Empty
8. Which of the following can be used to create a collection variable?
ANS %TYPE
%ROWTYPE
%DECLARE
%ISTYPE
6.2 VARRAYS
1. Where should a varraybe used?
As a small look-up table
ANS When the maximum number of elements of the collection is known
When the elements of the collection are accessed randomly
For huge tables where there are lot of DML activity
2. What is the lower bound of a varray index?
ANS 1
0
-1
There is no fixed lower bound
3. What is the upper bound of a varray index?
Maximum number of elements defined
ANS current number of elements in the array
Unlimited
Maximum number of elements defined - Current number of elements in the array
4. What is the best method to access element of a varray?
By first obtanining the index of the elements
Random manner
ANS Sequentially
Using a pointer to locate the place of the element
5. What does the integer indicate in the syntax below? TYPE arr_name IS VARRAY(integer) OF arr_type
The lower bound of the varray
The upper bound of the varray
The current elements in the array
ANS The maximum number of elements that the array can store
ANS The maximum number of elements that the array can store
7. What are the methods used to navigate across a varray in the forward and reverse directions?
BEFORE and AFTER
BEFORE and NEXT
FIRST and LAST
ANS PRIOR and NEXT
8. How can a varray variable be assigned values?
ANS Collection constructor or assignment statement or through a subprogram
Through a subprogram only
Collection constructor or assignment statement only
Collection constructor or subprogram only
6.3 NESTED TABLES
1. While using a collection constructor to assign values, what happens If the parameter list of the collection constructor is
empty?
Throws an error
Nothing happens
ANS Returns an empty collection.
Returns a NULL collection.
2. How can a nested table be traversed?
Using COUNT and LIMIT methods
Using FIRST and LAST methods
ANS Using FIRST and NEXT methods
Using FIRST and COUNT methods
3. Where are nested tables available?
Only in database
Only in PL/SQL
ANS In PL/SQL and in database
In SQL and PL/SQL
4. Which of the following is not the property of a nested table?
Homogenous
ANS Bounded
One dimensional
Initially dense
5. Where should a nested table be used?
As a small look-up table
When the maximum number of elements of the collection is known
When the elements of the collection are accessed sequentially
ANS When index values are not consecutive and Some elements must be deleted or updated but not all
elements simultaneously
6. What is a multi-dimensional nested table?
ANS A multidimensional nested table can be modeled with a nested table whose elements are either nested
table or associative array or varray.
A multidimensional nested table can be modeled with a nested table whose elements are nested table only.
A multidimensional nested table can be modeled with a nested table whose elements are either associative
array or varray only.
A multidimensional nested table can be modeled with a nested table whose elements are records only.
7. What is the state of an uninitialized nested table variable?
Empty
Undefined
ANS Null collection
Error
8. What is the main difference in the syntax of creating a nested table from that of an associative array?
ANS No INDEX BY clause in Nested table
No INDEX BY clause in Associative array
No INDEXED BY clause in Nested table
No INDEX clause in Associative array
6.4 COLLECTIONS METHODS
1. What is the only collection that does not return NULL value for LIMIT method?
Associative array
ANS Varray
Nested table
Records
2. Where can a Varray be stored?
At package or at the schema level.
PL/SQL block or at the schema level.
PL/SQL block or at the package level.
ANS PL/SQL block or package or at the schema level
3. What is the result of EXISTS(n)if n is out of range?
ANS FALSE
Error
NULL
TRUE
4. Which of the following collections can be stored in the database?
Associative array and Varray
ANS Varray and nested table
Associative array and nested table
Associative array,Varray and nested table
5. Which of the following collections have an unitialized status as empty?
ANS Associative array
Varray
Nested table
Records
6. What does DELETE(m,n) do when applied on a collection variable?
Deletes the mth and nth element of the collection.
Deletes element before mth place and after nth place
ANS Deletes elements in the range of m to n
Throws an error. This form is not supported for DELETE.
7. Which of the following is not a characteristics of EXTEND method?
EXTEND operates on the internal size of a collection.
If DELETE deletes an element but keeps a placeholder for it, then EXTEND considers the element to exist.
ANS EXTEND adds elements to the beginning of a collection
There are three forms of EXTEND procedure
8. What are the various forms of EXISTS method?
EXISTS only
EXISTS ,EXISTS(m.n) and EXISTS(n)
EXISTS(n) only
ANS EXISTS and EXISTS(n)
6.5 BULK OPERATIONS IN PLSQL
1. What can be present in the body of a FORALL statement ?
One or more DML statements
ANS A single DML statement
A single SELECT or DML statement
One or more SELECT statement
2. When is "INDICES OF collection_name" used in a FORALL statement?
ANS To use sparse nested tables
To access any collection element
To use densenested table
Never. It should be sued with BULK COLLECT only
3. How many times does a PL/SQL engine access the database to fetch 100 rows using BULK SQL?
Twice
ANS Once
Hundred times
One to 10 times depending on the batch size
4. Where can a BULK COLLECT clause be used?
SELECT INTO statement and FETCH statement
RETURNING INTO clause and SELECT INTO statement
FORALL statement and SELECT INTO statement
ANS SELECT INTO statement,RETURNING INTO statement and FETCH statement
5. Which of the following in incorrect regarding the FORALL statement?
FORALL sends DML statements from PL/SQL to SQL in batches rather than one at a time.
FORALL tells the PL/SQL runtime engine to bulk bind into the SQL statement all the elements of one or more
collections before sending anything to the SQL engine
ANS FORALL statement is a FOR LOOP
FORALL statement contains an iteration scheme
6. What does the lower bound and upper bound of FORALL statement indicate?
They are numbers implicitly assigned by the PL/SQL engine
They must specify a valid range of consecutive index numbers for the variables referenced in the SQL
statement.
They must be constant numbers explicitly assigned
ANS They must specify a valid range of consecutive index numbers for the collection referenced in the SQL
statement.
7. Which of the following is the correct syntax to use BULK COLLECT with SELECT INTO statement?
ANS SELECT column_names BULK COLLECT INTO collection_type FROM table_name
SELECT column_names INTO BULK COLLECT collection_type FROM table_name
SELECT BULK column_names COLLECT INTO collection_type FROM table_name
SELECT BULK COLLECT column_names INTO collection_type FROM table_name
8. Into which of the following type of variables does all BULK operations in PL/SQL fetch data?
Scalar
ANS Collection
Records
User defined types
6. END OF CHAPTER
1. What is the ideal place where an associative array should be used?
When elements of a collection are accessed sequentially.
Small Look up tables
During delete or update of some elements, but not when all elements will be done simultaneously
Huge tables with data getting modified frequently
The maximum number of elements that the array can store
3. Which of the following is not true about DELETE procedure?
A Deleted element cannot be restored.
There are three forms of DELETE procedure.
The deleted elements are included in the internal size of the collection.
The only form of DELETE valid for Varray is a simple DELETE.
4. What does the PRIOR function return if there is no preceding element in the collection?
NULL
Error
0
Nothing
5. Which of the following can be used to create a collection variable?
%TYPE
%ROWTYPE
%DECLARE
%ISTYPE
6. What is the result of EXISTS(n)if n is out of range?
FALSE
Error
NULL
TRUE
7. What are the various forms of EXISTS method?
EXISTS only
EXISTS ,EXISTS(m.n) and EXISTS(n)
EXISTS(n) only
EXISTS and EXISTS(n)
8. Where can a Varray be stored?
At package or at the schema level.
PL/SQL block or at the schema level.
PL/SQL block or at the package level.
PL/SQL block or package or at the schema level
9. What are the types of composite datatypes?
Records and Nested table
Collections and BOOLEAN
Collections, PLS_INTEGER and BOOLEAN
Collections and Records
10. Which of the following is not the property of a nested table?
Homogenous
Bounded
One dimensional
Initially dense

PLSQL MCQ

Uploaded by

Copyright:

Available Formats

PLSQL MCQ

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

PLSQL MCQ

Uploaded by

Copyright:

Available Formats

PLSQL FUNDEMENTALS MULTIPLE CHOICE QUESTIONS (31 QUIZZES)

1. PLSQL LANGUAGE FUNDEMENTALS

You might also like