MIDTERMS - Sheet1 Flashcards
(159 cards)
1
Q
The ______ clause is used in DML statement to specify variable(s) that will hold the value(s) that SQL returns from the SELECT clause.
A
INTO
2
Q
The use of INTO clause in PL/SQL select statement is to specify the name of ________.
A
VARIABLE
3
Q
The INTO clause occurs between the SELECT and _______ clauses.
A
FROM
4
Q
The DML statement: INSERT, DELETE, ________ make changes to the database.
A
UPDATE
5
Q
Which SQL statements cannot be used directly in PL/SQL?
Group of answer choices
DDL, DCL
DML
TCL
DML, TCL
A
DDL, DCL
6
Q
What could be the possible error of the given code below?
DECLARE
v_sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE copy_emp
SET salary = salary + v_sal_increase
WHERE job_id = ‘ST_CLERK’;
END;
Group of answer choices
WHERE section
Variable declaration
No error
SET section
A
No error
7
Q
What could be the possible error of the given code below?
DECLARE
v_sal_increase employees.salary%TYPE = 800;
BEGIN
UPDATE copy_emp
SET salary = salary + v_sal_increase
WHERE job_id = ‘ST_CLERK’;
END;
Group of answer choices
SET section
No error
WHERE section
Variable declaration
A
Variable declaration
8
Q
The memory area allocated by Oracle server to store the SQL statement and the data that it uses in known as:
A
Cursor
9
Q
The use of CASE statement to test conditions such as <, >, >=, <= is know as case expression.
A
False
10
Q
Applying the logical operator NOT to a null yields FALSE.
A
False
11
Q
The type of loop where the statement inside the loop must execute at least once.
A
BASIC
12
Q
In BASIC and WHILE loop, the iteration will terminate when the counter is > than the upper bound.
Group of answer choices
True
False
A
13
Q
In BASIC and WHILE loop, initialization of counter variable is necessary.
A
t
14
Q
The ______ is a Boolean attribute that evaluates to TRUE if the most recent SQL statement did not return even one row.
A
SQL%NOTFOUND
15
Q
The DDL and _______ SQL statements cannot be used directly in PL/SQL.
A
DCL
16
Q
The _______cursor are automatically defined by Oracle.
A
IMPLICIT
17
Q
What could be the possible error of the given code below?
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees;
DBMS_OUTPUT.PUT_LINE(‘ Salary is : ‘ || v_salary);
END;
A
Query returns more than 1 row
18
Q
The _______ statement selects rows from one table to update and/or insert into another table.
Group of answer choices
DML
COMBINE
MERGE
SELECT, INSERT, UPDATE
A
MERGE
19
Q
What is missing in the given code below?
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO ________
FROM employees where employee_id = 100;
DBMS_OUTPUT.PUT_LINE(‘ Salary is : ‘ || v_salary);
END;
A
V_SALARY
20
Q
What could be the possible error of the given code below?
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary)
INTO v_sum_sal FROM employees WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE(‘Dep #60 Salary Total: ‘ || v_sum_sal);
END;
A
No error
21
Q
Consider the given code fragment below. The condition will return a false value.
A:=null; B:=null;
If A = B then . . . . .
A
t
22
Q
The BASIC LOOP control structures are statements that enable you to execute statements in a PL/SQL block repeatedly.
Group of answer choices
True
False
A
t
23
Q
In for loop, counter must be explicitly declared.
Group of answer choices
True
False
A
False
24
Q
In BASIC and WHILE loop, the iteration will terminate when the counter is < than the upper bound.
A
False
25
The INTO clause occurs between the _______ and FROM clauses.
SELECT
26
The memory area allocated by Oracle server to store the SQL statement and the data that it uses in known as __________.
IMPLICIT CURSOR
27
Which DML statement make changes to the database?
Group of answer choices
INSERT
DELETE
All the options
UPDATE
All the options
28
The code below must print the number of rows updated, what is the missing part of the code?
Declare
v_cal_increase employees.salary%type := 800;
begin
update copy_emp
set salary = salary + v_sal_increase
where job_id = ‘ST_CLERK’;
dbms_output.put_line(_____________ || ‘ rows updated.’);
end;
SQL%ROWCOUNT
29
The code below must print the number of rows updated. Which part of the code must be changed to satisfy the output?
Declare
v_sal_increase employees.salary%type := 800;
begin
update copy_emp
set salary = salary + v_sal_increase
where job_id = ‘ST_CLERK’;
dbms_output.put_line(SQL%FOUND || ‘ rows updated.’);
end;
SQL%FOUND
30
Consider the given code fragment below. The condition will return a true value.
A:=null; B:=null;
If A = B then . . . . .
False
31
Applying the logical operator NOT to a null yields NULL.
Group of answer choices
True
False
True
32
In for loop, counter variable is declared _______.
IMPLICITLY
33
Without the EXIT statement, the loop would never end (an infinite loop).
True
34
All counter variables must be declared at the declaration section.
Group of answer choices
True
False
35
An EXIT statement is used in order to come out of from the outer loop within an inner loop.
Group of answer choices
True
False
False
36
The _________ SQL Rule: queries must return only one row.
EMBEDDED
37
The _____ is an integer value that represents the number of rows affected by the most recent SQL statement.
SQL%ROWCOUNT
38
The _____ statement selects rows from one table to update and/or insert into another table.
MERGE
39
The ______ are automatically declared variables that allow you to evaluate what happened when a cursor was last used.
Group of answer choices
Explicit attributes
Cursor attributes
Attributes
Implicit attributes
Cursor attributes
40
What could be the possible error of the given code below?
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees where employee_id = 100;
DBMS_OUTPUT.PUT_LINE(' Salary is : ' || v_salary);
END;
Group of answer choices
VARIABLE ERROR
Query returns more than 1 row
TABLE ERROR
NO ERROR
NO ERROR
41
A counter variable is an expression that returns true, false, or null.
Group of answer choices
True
False
False
42
What is missing in the given WHILE loop syntax? ________
WHILE condition
statement1;
statement2;
. . .
END ;
LOOP
43
A loop structure must have an exit clause.
Group of answer choices
True
False
True
44
What could be the possible error of the given code below?
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) FROM employees WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE('Dep #60 Salary Total: ' || v_sum_sal);
END;
Group of answer choices
Group function error
Query returns more than 1 row
Variable error
Missing INTO clause
Missing INTO clause
45
Is there something missing in the given code?
DECLARE
v_myage NUMBER := 31;
BEGIN
IF v_myage < 11
DBMS_OUTPUT.PUT_LINE('I am a child');
END IF;
END;
Group of answer choices
True
False
True
46
A ________ is used in order to come out of from the outer loop within an inner loop.
LOOP LABELS
47
The lower and upper bound of a for loop must be a numeric literals.
Group of answer choices
True
False
False
48
The _________ cursors are defined by the PL/SQL programmer.
EXPLICIT
49
What is the output of the given code below?
DECLARE
v_deptno copy_emp.department_id%TYPE := 50;
BEGIN
DELETE FROM copy_emp
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows deleted.');
END;
Print the number of rows deleted
50
Control structures are used to change the logical flow of statements within the PL/SQL block.
Group of answer choices
True
False
True
51
Use the ______ loop when the statement inside the loop must execute at least once.
BASIC
52
The use of INTO clause in PL/SQL select statement is to specify the name of: ____________.
Group of answer choices
VARIABLE
CURSOR
TABLE
VIEW
VARIABLE
53
The code below must print the number of rows deleted. What is missing in the given code to satisfy the output?
DECLARE
v_deptno copy_emp.department_id%TYPE := 50;
BEGIN
DELETE FROM copy_emp
WHERE _____________ = v_deptno;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows deleted.');
END;
Group of answer choices
DEPARTMENT_ID
DEPARTMENTS
JOBS
JOB_ID
DEPARTMENT_ID
54
Use a DO..WHILE loop when the statement inside the loop must execute at least once.
Group of answer choices
True
False
False
55
Start with the _____ keyword to define a user-defined record structure.
TYPE
56
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? __________
EMPLOYEES%ROWTYPE
57
The OPEN will populate the cursor’s active set with the result of the SELECT statement in the cursor’s definition?
True
False
True
58
The FETCH statement positions the cursor pointer at the first row.
Group of answer choices
True
False
False
59
What is missing in the given exception syntax? ____
EXCEPTION
WHEN exception1 [OR exception2 . . .]
statement1; statement2;
THEN
60
Always add ________ whenever there is a possibility of an error occurring.
EXCEPTION HANDLERS
61
Handle named exceptions whenever possible, instead of using ______ in exception handlers.
OTHERS
62
Error in PL/SQL is known as ____________.
EXCEPTION
63
Each ________ is consists of a WHEN clause, which specifies an exception name.
HANDLER
64
When an exception is raised, control immediately shifts to the exception section and the appropriate handler in the exception section is executed.
True
False
t
65
The term TRAP in exceptions is the same as handling any error by including corresponding exception handler.
Group of answer choices
True
False
True
66
A block always terminates when PL/SQL raises an exception.
Group of answer choices
True
False
True
67
An exception handler for a particular exception must contain only one statement.
Group of answer choices
True
False
True
68
The RAISE keyword is used in user-defined exception for error notification.
Group of answer choices
True
False
True
69
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
False
70
User-defined exceptions are declared within the declarative section and are raised explicitly.
Group of answer choices
True
False
t
71
Non-predefined errors are raised explicitly.
Group of answer choices
True
False
72
The RAISE statement can be used to raise either user-defined or non-predefined exception.
Group of answer choices
True
False
t
73
The user-defined exceptions are declared within the declarative section and are raised implicitly.
Group of answer choices
True
False
False
74
What is the first step in handing non-predefined exception?
Group of answer choices
Exception name declaration
Pragma declaration
Exception name declaration
75
The RAISE statement can be used to raise either ________ or predefined exception.
Group of answer choices
USER-DEFINED
NON-PREDEFINED
USER-DEFINED
76
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
PRAGMA EXCEPTION_INIT
77
Two methods for raising an exception:
Group of answer choices
Implicit, Explicit
Predefined, Non-predefined
Implicit, Explicit
78
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
RAISE_APPLICATION_ERROR
79
The following declaration is a PL/SQL syntax for defining a record.
Record_name table_name%rowtype;
Group of answer choices
True
False
t
80
Type and record declared in the outer block are visible only in the outer block.
Group of answer choices
True
False
False
81
A user-defined PL/SQL record contains one or more fields of scalar data type.
Group of answer choices
True
False
True
82
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
83
Given the code below:
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;
is line #12 valid or invalid
Valid
84
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
85
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
86
If you omit the ______ keyword, then the Oracle server waits indefinitely until the rows are available.
NOWAIT
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 parameter mode of the formal parameter?_____
IN
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. 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
89
The NOWAIT keyword is optional in the FOR UPDATE clause.
Group of answer choices
True
False
True
90
32/35
91
What symbol is used to terminate the TYPE statement?
DECLARE
TYPE person_dept IS . . . . . . . .
;
92
What symbol is used to enclose the elements of the TYPE structure?
()
93
Given the code below:
The closing of cursor is missing
True
False
True
94
The OPEN statement positions the cursor pointer at the first row.
True
False
True
95
The __________ keyword is used in user-defined exception for error notification.
RAISE
96
Code that defines the recovery actions to be performed when execution-time errors occur.
EXCEPTION HANDLER
97
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
98
The ___________ contains the exceptions handlers.
EXCEPTION SECTION
99
The EXECUTE keyword is used in user-defined exception for error notification.
Group of answer choices
True
False
False
100
When code does not work as expected, PL/SQL raises an exception handler.
Group of answer choices
True
False
False
101
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
102
Names for predefined exceptions must be declared in the declaration section.
Group of answer choices
True
False
103
The SQLCODE function returns the numeric value for the error code.
Group of answer choices
True
False
True
104
You can use the RAISE_APPLICATION_ERROR procedure to return user-defined error messages from stored subprograms.
Group of answer choices
True
False
True
105
Pragma declaration is used in declaring user-defined exceptions.
Group of answer choices
True
False
False
106
What is the first parameter of the PRAGMA EXCEPTION_INIT function?
Group of answer choices
Oracle error number
EXCEPTION NAME
Oracle error number
107
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
108
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
109
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
110
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
111
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
112
COMPOSITE
True
113
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
114
Given the code below:
DECLARE
CURSOR cur_emps
SELECT employee_id, last_name FROM employees;
BEGIN
FOR v_emp_record IN cur_emps LOOP
EXIT WHEN cur_emps%ROWCOUNT > 5;
DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ' ' || v_emp_record.last_name);
END LOOP;
END;
What step is missing in the given code?
Group of answer choices
CLOSE
FETCH
Nothing is missing
Open cursor
Nothing is missing
115
Given the code below:
1. DECLARE
2. CURSOR cur_emps
3. SELECT employee_id, last_name FROM employees;
4. BEGIN
5. FOR cur_emps IN v_emp_record LOOP
6. EXIT WHEN cur_emps%ROWCOUNT > 5;
7. DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ' ' || v_emp_record.last_name);
8. END LOOP;
9. END;
What is missing in line 2 and 3?
INTO
IS
Nothing is missing
RECORD
IS
116
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 _____;
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 #8
country_rec
117
DECLARE
CURSOR country_curs(_____ 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 missing in line#2?
p_region_id
118
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 _____%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 missing in line #4
country_curs
119
Refer to the code below. The missing element in line 8 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 country_curs(1);
LOOP
FETCH country_curs INTO _____;
EXIT WHEN country_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' | | country_rec.country_name | | ' Area: ' | | country_rec.area);
END LOOP;
CLOSE country_curs;
END;
True
False
True
120
TYPE and Records are ________ structures.
COMPOSITE
121
A ________ allows us to declare a variable as a record based on a particular table’s structure.
%ROWTYPE
122
Use the %ISOPEN cursor attribute before performing a fetch to test whether the cursor is open.
Group of answer choices
True
False
True
123
Each exception handler is consists of a _____ clause, which specifies an exception name.
WHEN
124
The term ________ in exceptions is the same as handling any error by including corresponding exception handler.
TRAP
125
Exception handlers are code that defines the recovery actions to be performed when execution-time errors occur.
Group of answer choices
True
False
True
126
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
127
You can use the EXCEPTION_INIT procedure to return user-defined error messages from stored subprograms.
Group of answer choices
True
False
False
128
Pragma declaration is used in declaring non-predefined exceptions.
Group of answer choices
True
False
t
129
The NO_DATA_FOUND is an example of:
Group of answer choices
Non-predefined exception
Predefined exception
Predefined exception
130
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
131
The oracle error number, at the PRAGMA EXCEPTION_INIT function, starts with _____.
Group of answer choices
0
HYPEN
1
UNDERSCORE
HYPEN
132
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
133
Given the code below, EMPLOYEES table must be specified at the DBMS_OUTPUT section.
DECLARE
v_emp_record employees2%rowtype;
BEGIN
SELECT * INTO v_emp_record FROM employees2 WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Email for ' || _____________.first_name || ' ' || ____________.last_name);
END;
Group of answer choices
True
False
True
134
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%_____;
12. DBMS_OUTPUT.PUT_LINE( v_empno | | ' ' | | v_lname);
13. END LOOP;
14. END;
What is missing in line#11?
Group of answer choices
FOUND
NOTFOUND
NOTFOUND
135
Given the code below:
DECLARE
CURSOR cur_emps
SELECT employee_id, last_name FROM employees;
BEGIN
FOR cur_emps IN v_emp_record LOOP
EXIT WHEN cur_emps%ROWCOUNT > 5;
DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ' ' || v_emp_record.last_name);
END LOOP;
END;
What will cause an error in the FOR section?
Group of answer choices
No error
cur_emps IN v_emp_record
Counter declaration
Remove LOOP
cur_emps IN v_emp_record
136
Given the code below, assume that the cursor contains 20 rows. How many rows will be fetched from the cursor?
1. DECLARE
2. CURSOR cur_emps
3. SELECT employee_id, last_name FROM employees;
4. BEGIN
5. FOR cur_emps IN v_emp_record LOOP
6. EXIT WHEN cur_emps%ROWCOUNT > 5;
7. DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id || ' ' || v_emp_record.last_name);
8. END LOOP;
9. END;
Group of answer choices
0
5
6
4
5
137
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
138
You must include the FOR UPDATE clause in the cursor query so that the rows are _____ on OPEN.
LOCKED
139
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
140
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
141
Given the code below:
DECLARE
v_emp_record employees%rowtype;
BEGIN
SELECT * INTO v_emp_record FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Email for ' || _____________.first_name ||
' ' || ____________.last_name);
END;
What is missing in the DBMS_OUTPUT section?
v_emp_record
142
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
143
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
144
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
145
The exception section begins with the keyword _______.
EXCEPTION
146
In exception section, the WHEN clause if followed by _______.
THEN
147
Each exception handler is consists of a WHEN clause, which specifies an exception name.
Group of answer choices
True
False
t
148
Exception section is mandatory in PL/SQL block.
Group of answer choices
True
False
False
149
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
150
The RAISE statement can be used to raise either user-defined or predefined exception.
Group of answer choices
True
False
151
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
152
The ___________ and ___________ are the two types of Oracle Server Errors.
Group of answer choices
Predefined, Non-predefined
Implicit, Explicit
Predefined, Non-predefined
153
The RAISE statement can be used to raise either user-defined or ________ exception.
Group of answer choices
PREDEFINED
NON-PREDEFINED
NON-PREDEFINED
154
Types and records are composite structures that can be declared anywhere that scalar variables can be declared.
Group of answer choices
True
False
t
155
Given the code below, EMPLOYEES%ROWTYPE is missing in the declaration section to declare v_emp_record as type record of EMPLOYEES table.
DECLARE
v_emp_record _______________;
Group of answer choices
True
False
t
156
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
157
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
158
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
159
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
