FA2 - Sheet1 (1) Flashcards
(121 cards)
1
Q
Start with the _____ keyword to define a user-defined record structure.
A
TYPE
2
Q
Given:
DECLARE
v_emp_record \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_;
What is missing in the declaration section if we want to declare v_emp_record as type record of EMPLOYEES table? __________
A
EMPLOYEES%ROWTYPE
3
Q
The OPEN will populate the cursor’s active set with the result of the SELECT statement in the cursor’s definition?
True
False
A
True
4
Q
The FETCH statement positions the cursor pointer at the first row.
Group of answer choices
True
False
A
False
5
Q
What is missing in the given exception syntax? ____
EXCEPTION
WHEN exception1 [OR exception2 . . .]
statement1; statement2;
A
THEN
6
Q
Always add ________ whenever there is a possibility of an error occurring.
A
EXCEPTION HANDLERS
7
Q
Handle named exceptions whenever possible, instead of using ______ in exception handlers.
A
OTHERS
8
Q
Error in PL/SQL is known as ____________.
A
EXCEPTION
9
Q
Each ________ is consists of a WHEN clause, which specifies an exception name.
A
EXCEPTION HANDLER
10
Q
When an exception is raised, control immediately shifts to the exception section and the appropriate handler in the exception section is executed.
True
False
A
t
11
Q
The term TRAP in exceptions is the same as handling any error by including corresponding exception handler.
Group of answer choices
True
False
A
True
12
Q
A block always terminates when PL/SQL raises an exception.
Group of answer choices
True
False
A
True
13
Q
An exception handler for a particular exception must contain only one statement.
Group of answer choices
True
False
A
False
14
Q
The RAISE keyword is used in user-defined exception for error notification.
Group of answer choices
True
False
A
True
15
Q
The PRAGMA clause is used in predefined exception to tell the compiler to associate an exception name with a specific Oracle error number.
Group of answer choices
True
False
A
False
16
Q
User-defined exceptions are declared within the declarative section and are raised explicitly.
Group of answer choices
True
False
A
t
17
Q
Non-predefined errors are raised explicitly.
Group of answer choices
True
False
A
18
Q
The RAISE statement can be used to raise either user-defined or non-predefined exception.
Group of answer choices
True
False
A
19
Q
The user-defined exceptions are declared within the declarative section and are raised implicitly.
Group of answer choices
True
False
A
False
20
Q
What is the first step in handing non-predefined exception?
Group of answer choices
Exception name declaration
Pragma declaration
A
Exception name declaration
21
Q
The RAISE statement can be used to raise either ________ or predefined exception.
Group of answer choices
USER-DEFINED
NON-PREDEFINED
A
USER-DEFINED
22
Q
The ________ clause is used in non-predefined exception to tell the compiler to associate an exception name with a specific Oracle error
Group of answer choices
PRAGMA EXCEPTION_INIT
SQLERRM
RAISE_APPLICATION_ERROR
SQLCODE
A
PRAGMA EXCEPTION_INIT
23
Q
Two methods for raising an exception:
Group of answer choices
Implicit, Explicit
Predefined, Non-predefined
A
Implicit, Explicit
24
Q
You can use the ________________ procedure to return user-defined error messages from stored subprograms.
Group of answer choices
RAISE_APPLICATION_ERROR
PRAGMA EXCEPTION_INIT
SQLCODE
SQLERRM
A
RAISE_APPLICATION_ERROR
25
The following declaration is a PL/SQL syntax for defining a record.
Record_name table_name%rowtype;
Group of answer choices
True
False
t
26
Type and record declared in the outer block are visible only in the outer block.
Group of answer choices
True
False
False
27
A user-defined PL/SQL record contains one or more fields of scalar data type.
Group of answer choices
True
False
28
The cursor defined in the code below is ____
DECLARE
CURSOR cur_emps IS
SELECT employee_id, last_name, salary FROM employees
WHERE department_id = 30;
Group of answer choices
IMPLICIT CURSOR
Explicit Cursor
Explicit Cursor
29
The given code below declares an explicit cursor. What will cause an error in the code?
DECLARE
CURSOR cur_depts
SELECT * FROM departments WHERE location_id = 1700
ORDER BY department_name;
Group of answer choices
WHERE location_id = 1700
SELECT *
ORDER BY department_name;
IS
IS
30
The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter.
1. DECLARE
2. CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
3. SELECT country_name, area FROM ____ WHERE region_id = p_region_id;
4. country_rec country_curs%ROWTYPE;
5. BEGIN
6. OPEN country_curs(____);
7. LOOP
8. FETCH country_curs INTO ______;
9. EXIT WHEN ________%NOTFOUND;
10. DBMS_OUTPUT.PUT_LINE('Name: ' | | country_rec.country_name | | ' Area: ' | | 11. country_rec.area);
12. END LOOP;
13. CLOSE ______;
14. END;
What is missing in line #3?
countries
31
If you omit the ______ keyword, then the Oracle server waits indefinitely until the rows are available.
NOWAIT
32
DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' | | country_rec.country_name | | ' Area: ' | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;
What is the parameter mode of the formal parameter?_____
IN
33
The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter. The block is tested using region 5 (South America).
DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' | | country_rec.country_name | | ' Area: ' | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;
The missing element in in line 6 is Region_id.
False
34
The NOWAIT keyword is optional in the FOR UPDATE clause.
Group of answer choices
True
False
True
35
What symbol is used to terminate the TYPE statement?
DECLARE
TYPE person_dept IS . . . . . . . .
;
36
What symbol is used to enclose the elements of the TYPE structure?
()
37
The OPEN statement positions the cursor pointer at the first row.
True
False
True
38
The __________ keyword is used in user-defined exception for error notification.
RAISE
39
Code that defines the recovery actions to be performed when execution-time errors occur.
EXCEPTION HANDLER
40
The following statements are examples of ________________.
Entering an expiration date that has passed
Selecting more than one row into a single variable
Receiving “no rows returned” from a select statement
EXCEPTION
41
The ___________ contains the exceptions handlers.
EXCEPTION SECTION
42
The EXECUTE keyword is used in user-defined exception for error notification.
Group of answer choices
True
False
False
43
When code does not work as expected, PL/SQL raises an exception handler.
Group of answer choices
True
False
False
44
The OTHERS is an optional exception-handling clause that traps any exceptions that have not been explicitly handled.
Group of answer choices
True
False
True
45
Names for predefined exceptions must be declared in the declaration section.
Group of answer choices
True
False
46
The SQLCODE function returns the numeric value for the error code.
Group of answer choices
True
False
True
47
You can use the RAISE_APPLICATION_ERROR procedure to return user-defined error messages from stored subprograms.
Group of answer choices
True
False
True
48
Pragma declaration is used in declaring user-defined exceptions.
Group of answer choices
True
False
False
49
What is the first parameter of the PRAGMA EXCEPTION_INIT function?
Group of answer choices
Oracle error number
EXCEPTION NAME
Oracle error number
50
Each exception handler is consists of a _____ clause, which specifies an exception name.
Group of answer choices
IF
CONDITION
Not in the options
WHEN
WHEN
51
In trapping a user-defined exception, these steps must be followed: DECLARE -> RAISE -> __________.
Group of answer choices
Reference
Identify Exception
Not in the options
Exception Handling
Reference
52
In non-predefined exception, you must reference the ________ within a WHEN clause in the exception-handling section.
Group of answer choices
Exception name
Declared exception name
Oracle associated error#
All the options are possible
Declared exception name
53
PL/SQL record is a composite data type, you can refer to the whole record by its name and/or to individual fields by their names.
Group of answer choices
True
False
True
54
The given syntax in declaring a user-define record is correct.
TYPE type_name IS RECORD
(field_declaration[,field_declaration]...);
identifier type_name ;
Group of answer choices
True
False
True
55
COMPOSITE
True
56
1. DECLARE
2. CURSOR cur_emps IS
3. SELECT employee_id, last_name, salary FROM employees WHERE department_id =30;
4. v_empno employees.employee_id%TYPE;
5. v_lname employees.last_name%TYPE;
6. v_sal employees.salary%TYPE;
7. BEGIN
8. OPEN cur_emps;
9. LOOP
10. FETCH cur_emps INTO v_empno, v_lname;
11. EXIT WHEN cur_emps%notfound;
12. DBMS_OUTPUT.PUT_LINE( v_empno | | ' ' | | v_lname);
13. END LOOP;
14. END
which line number(s) contains error?
Line 2
Line 4
No error
Line 3
Line 3
57
TYPE and Records are ________ structures.
COMPOSITE
58
A ________ allows us to declare a variable as a record based on a particular table’s structure.
%ROWTYPE
59
Use the %ISOPEN cursor attribute before performing a fetch to test whether the cursor is open.
Group of answer choices
True
False
True
60
Each exception handler is consists of a _____ clause, which specifies an exception name.
WHEN
61
The term ________ in exceptions is the same as handling any error by including corresponding exception handler.
TRAP
62
Exception handlers are code that defines the recovery actions to be performed when execution-time errors occur.
Group of answer choices
True
False
True
63
When an exception is raised, the rest of the execution section of the PL/SQL block is not executed.
Group of answer choices
True
False
True
64
You can use the EXCEPTION_INIT procedure to return user-defined error messages from stored subprograms.
Group of answer choices
True
False
False
65
Pragma declaration is used in declaring non-predefined exceptions.
Group of answer choices
True
False
t
66
The NO_DATA_FOUND is an example of:
Group of answer choices
Non-predefined exception
Predefined exception
Predefined exception
67
The RAISE_APPLICATION_ERROR can be used in:
Group of answer choices
Declaration and Exception section
Executable and Exception section
Executable section
Exception section
Executable and Exception section
68
The oracle error number, at the PRAGMA EXCEPTION_INIT function, starts with _____.
Group of answer choices
0
HYPEN
1
UNDERSCORE
HYPEN
69
The given syntax in declaring a user-define record is incorrect.
TYPE type_name IS RECORD
(field_declaration[,field_declaration]...);
identifier type_name ;
Group of answer choices
True
False
False
70
The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter. The block is tested using region 5 (South America).
DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN _______;
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' | | country_rec.country_name | | ' Area: ' | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;
How many parameter is needed to be passed from the calling environment? _____
1
71
You must include the FOR UPDATE clause in the cursor query so that the rows are _____ on OPEN.
LOCKED
72
Refer to the code below. The missing element in line 9 is COUNTRY_REC.
DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN _______;
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN ______%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' | | country_rec.country_name | | ' Area: ' | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;
True
False
73
You must include the FOR UPDATE clause in the cursor query so that the rows are locked on OPEN.
Group of answer choices
True
False
True
74
Given the declaration below:
DECLARE
TYPE person_dept IS _________
first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
department_name departments.department_name%TYPE;
What is missing in the TYPE section?__________
RECORD
75
The FETCH will populate the cursor’s active set with the result of the SELECT statement in the cursor’s definition?
Group of answer choices
True
False
76
1. DECLARE
2. CURSOR cur_emps IS
3. SELECT employee_id, last_name, salary FROM employees WHERE department_id =30;
4. v_empno employees.employee_id%TYPE;
5. v_lname employees.last_name%TYPE;
6. v_sal employees.salary%TYPE;
7. BEGIN
8. OPEN cur_emps;
9. LOOP
10. FETCH cur_emps INTO v_empno, v_lname;
11. EXIT WHEN cur_emps%NOTFOUND;
12. DBMS_OUTPUT.PUT_LINE( v_empno | | ' ' | | v_lname);
13. END LOOP;
14. END;
The cursor declaration is correct.
Group of answer choices
True
False
t
77
The exception section begins with the keyword _______.
EXCEPTION
78
In exception section, the WHEN clause if followed by _______.
EXCEPTION HANDLER
79
Each exception handler is consists of a WHEN clause, which specifies an exception name.
Group of answer choices
True
False
t
80
Exception section is mandatory in PL/SQL block.
Group of answer choices
True
False
False
81
The following statements are examples of exception handler.
Entering an expiration date that has passed
Selecting more than one row into a single variablE
Receiving “no rows returned” from a select statement
Group of answer choices
True
False
False
82
The RAISE statement can be used to raise either user-defined or predefined exception.
Group of answer choices
True
False
83
The _______________ is used in non-predefined exception to tell the compiler to associate an exception name with a specific Oracle error
Group of answer choices
PRAGMA EXCEPTION_INIT
EXCEPTION_INIT
PRAGMA EXCEPTION INIT
EXCEPTION INIT
PRAGMA EXCEPTION_INIT
84
The ___________ and ___________ are the two types of Oracle Server Errors.
Group of answer choices
Predefined, Non-predefined
Implicit, Explicit
Predefined, Non-predefined
85
The RAISE statement can be used to raise either user-defined or ________ exception.
Group of answer choices
PREDEFINED
NON-PREDEFINED
PREDEFINED
86
Types and records are composite structures that can be declared anywhere that scalar variables can be declared.
Group of answer choices
True
False
t
87
DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(1);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' | | country_rec.country_name | | ' Area: ' | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;
What is the counter variable?
NONE
88
The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter. Test the block using region 5 (South America).
DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(_);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' | | country_rec.country_name | | ' Area: ' | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;
What is missing in line#6?
5
89
When we declare a cursor FOR UPDATE, each row is locked as we open the cursor and prevent other users from reading the rows.
Group of answer choices
True
False
False
90
The block below will display the country name and the area of each country in a chosen region. The region_id should be passed to the cursor as a parameter. Test the block using region 5 (South America).
DECLARE
CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
SELECT country_name, area FROM countries WHERE region_id = p_region_id;
country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs(_);
LOOP
FETCH country_curs INTO country_rec;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' | | country_rec.country_name | | ' Area: ' | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;
The missing element in in line 6 is 1.
True
False
False
91
PL/SQL records contain one or more components/fields of any _______ or composite type.
SCALAR
92
In explicit cursor, you need to fetch each row one at a time.
Group of answer choices
True
False
t
93
The _______ is an optional exception-handling clause that traps any exceptions that have not been explicitly handled.
OTHERS
94
The following statements are examples of exception.
Entering an expiration date that has passed
Selecting more than one row into a single variable
Receiving “no rows returned” from a select statement
Group of answer choices
True
False
t
95
Non-predefined exceptions has a standard Oracle error number (ORA-#####) and error message, but not a predefined name.
True
96
The RAISE_APPLICATION_ERROR can be used in executable and exception section.
Group of answer choices
True
False
True
97
What are/is the type of exception can be raised using the RAISE statement?
Group of answer choices
NON-PREDEFINED
Non-predefined, User-defined
User-defined
Pre-defined, Non-predefined
Pre-defined, User-defined
Quiz
Non-predefined, User-defined
98
In explicit cursor operations, the set of rows returned by a multiple-row query is called ________.
Group of answer choices
Active set
Record set
Cursor
Active Cursor
Active set
99
You must include the FOR UPDATE clause in the cursor query so that the rows are unlocked on OPEN.
Group of answer choices
True
False
False
100
The NOWAIT keyword is mandatory in the FOR UPDATE clause.
Group of answer choices
True
False
False
101
The term RAISE in exceptions is the same as handling any error by including corresponding exception handler.
Group of answer choices
True
False
102
There are 2 parameters needed in the pragma declaration of an exception.
Group of answer choices
True
False
True
103
The declared non-predefined exception is raised ____________.
Group of answer choices
EXPLICITLY
IMPLICITLY
IMPLICITLY
104
The ________ clause is used in user-defined exception to tell the compiler to associate an exception name with a specific Oracle error
Group of answer choices
PRAGMA EXCEPTION_INIT
Not in the options
RAISE_APPLICATION_ERROR
SQLCODE, SQLERRM
PRAGMA EXCEPTION_INIT
105
The type and the record declared in the outer block are visible within the outer block and the inner block.
Group of answer choices
True
False
t
106
The RAISE keyword is used by non-predefined exception.
Group of answer choices
True
False
False
107
The ______ returns character data containing the message associated with the error number.
Group of answer choices
SQLERRM
SQLCODE
SQLERRM
108
If you omit the NOWAIT keyword, then the Oracle server waits indefinitely until the _____ are available.
ROWS
109
The __________ clause is used in conjunction with the FOR UPDATE clause to refer to the most recently fetched row in an explicit cursor.
WHERE CURRENT OF
110
TYPE and ________ are composite structures.
RECORDS
111
PL/SQL records contain one or more components/fields of any SCALAR or ______ type.
COMPOSITE
112
An exception occurs when an error is discovered during the execution of a program that disrupts the normal operation of the program.
Group of answer choices
True
False
True
113
The OTHERS is mandatory exception-handling clause that traps any exceptions that have not been explicitly handled.
Group of answer choices
True
False
False
114
PL/SQL records contain one or more components/fields of any scalar or composite type.
Group of answer choices
True
False
t
115
You must include the FOR UPDATE clause in the cursor query so that the rows are locked on _____.
OPEN
116
In explicit cursor operations, the set of rows returned by a multiple-row query is called ACTIVE SET.
Group of answer choices
True
False
True
117
Always add exception handlers whenever there is a possibility of an error occurring.
Group of answer choices
True
False
True
118
The %ROWTYPE can be used to declare a record based on another record.
Group of answer choices
True
False
True
119
When we declare a cursor FOR UPDATE, each row is locked as we open the cursor but does not prevent other users from reading the rows.
Group of answer choices
True
False
True
120
PL/SQL record is a _____ data type, you can refer to the whole record by its name and/or to individual fields by their names.
COMPOSITE
121
You must include the ________ clause in the cursor query so that the rows are locked on OPEN.
FOR UPDATE
