MIDTERMS - Sheet1 Flashcards
The ______ clause is used in DML statement to specify variable(s) that will hold the value(s) that SQL returns from the SELECT clause.
INTO
The use of INTO clause in PL/SQL select statement is to specify the name of ________.
VARIABLE
The INTO clause occurs between the SELECT and _______ clauses.
FROM
The DML statement: INSERT, DELETE, ________ make changes to the database.
UPDATE
Which SQL statements cannot be used directly in PL/SQL?
Group of answer choices
DDL, DCL
DML
TCL
DML, TCL
DDL, DCL
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
No error
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
Variable declaration
The memory area allocated by Oracle server to store the SQL statement and the data that it uses in known as:
Cursor
The use of CASE statement to test conditions such as <, >, >=, <= is know as case expression.
False
Applying the logical operator NOT to a null yields FALSE.
False
The type of loop where the statement inside the loop must execute at least once.
BASIC
In BASIC and WHILE loop, the iteration will terminate when the counter is > than the upper bound.
Group of answer choices
True
False
In BASIC and WHILE loop, initialization of counter variable is necessary.
t
The ______ is a Boolean attribute that evaluates to TRUE if the most recent SQL statement did not return even one row.
SQL%NOTFOUND
The DDL and _______ SQL statements cannot be used directly in PL/SQL.
DCL
The _______cursor are automatically defined by Oracle.
IMPLICIT
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;
Query returns more than 1 row
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
MERGE
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;
V_SALARY
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;
No error
Consider the given code fragment below. The condition will return a false value.
A:=null; B:=null;
If A = B then . . . . .
t
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
t
In for loop, counter must be explicitly declared.
Group of answer choices
True
False
False
In BASIC and WHILE loop, the iteration will terminate when the counter is < than the upper bound.
False
The INTO clause occurs between the _______ and FROM clauses.
SELECT
The memory area allocated by Oracle server to store the SQL statement and the data that it uses in known as __________.
IMPLICIT CURSOR
Which DML statement make changes to the database?
Group of answer choices
INSERT
DELETE
All the options
UPDATE
All the options
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
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
Consider the given code fragment below. The condition will return a true value.
A:=null; B:=null;
If A = B then . . . . .
False
Applying the logical operator NOT to a null yields NULL.
Group of answer choices
True
False
True
In for loop, counter variable is declared _______.
IMPLICITLY
Without the EXIT statement, the loop would never end (an infinite loop).
True
All counter variables must be declared at the declaration section.
Group of answer choices
True
False
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
The _________ SQL Rule: queries must return only one row.
EMBEDDED
The _____ is an integer value that represents the number of rows affected by the most recent SQL statement.
SQL%ROWCOUNT
The _____ statement selects rows from one table to update and/or insert into another table.
MERGE
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
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
A counter variable is an expression that returns true, false, or null.
Group of answer choices
True
False
False
What is missing in the given WHILE loop syntax? ________
WHILE condition
statement1;
statement2;
. . .
END ;
LOOP
A loop structure must have an exit clause.
Group of answer choices
True
False
True
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
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
A ________ is used in order to come out of from the outer loop within an inner loop.
LOOP LABELS
The lower and upper bound of a for loop must be a numeric literals.
Group of answer choices
True
False
False
The _________ cursors are defined by the PL/SQL programmer.
EXPLICIT
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
Control structures are used to change the logical flow of statements within the PL/SQL block.
Group of answer choices
True
False
True
Use the ______ loop when the statement inside the loop must execute at least once.
BASIC
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
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
Use a DO..WHILE loop when the statement inside the loop must execute at least once.
Group of answer choices
True
False
False
Start with the _____ keyword to define a user-defined record structure.
TYPE
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
The OPEN will populate the cursor’s active set with the result of the SELECT statement in the cursor’s definition?
True
False
True
The FETCH statement positions the cursor pointer at the first row.
Group of answer choices
True
False
False
What is missing in the given exception syntax? ____
EXCEPTION
WHEN exception1 [OR exception2 . . .]
statement1; statement2;
THEN
Always add ________ whenever there is a possibility of an error occurring.
EXCEPTION HANDLERS
Handle named exceptions whenever possible, instead of using ______ in exception handlers.
OTHERS
Error in PL/SQL is known as ____________.
EXCEPTION
Each ________ is consists of a WHEN clause, which specifies an exception name.
HANDLER
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
The term TRAP in exceptions is the same as handling any error by including corresponding exception handler.
Group of answer choices
True
False
True
A block always terminates when PL/SQL raises an exception.
Group of answer choices
True
False
True
An exception handler for a particular exception must contain only one statement.
Group of answer choices
True
False
True
The RAISE keyword is used in user-defined exception for error notification.
Group of answer choices
True
False
True
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
User-defined exceptions are declared within the declarative section and are raised explicitly.
Group of answer choices
True
False
t
Non-predefined errors are raised explicitly.
Group of answer choices
True
False
The RAISE statement can be used to raise either user-defined or non-predefined exception.
Group of answer choices
True
False
t
The user-defined exceptions are declared within the declarative section and are raised implicitly.
Group of answer choices
True
False
False
What is the first step in handing non-predefined exception?
Group of answer choices
Exception name declaration
Pragma declaration
Exception name declaration
The RAISE statement can be used to raise either ________ or predefined exception.
Group of answer choices
USER-DEFINED
NON-PREDEFINED
USER-DEFINED
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
Two methods for raising an exception:
Group of answer choices
Implicit, Explicit
Predefined, Non-predefined
Implicit, Explicit
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
The following declaration is a PL/SQL syntax for defining a record.
Record_name table_name%rowtype;
Group of answer choices
True
False
t
Type and record declared in the outer block are visible only in the outer block.
Group of answer choices
True
False
False
A user-defined PL/SQL record contains one or more fields of scalar data type.
Group of answer choices
True
False
True
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
Given the code below:
- DECLARE
- CURSOR cur_emps IS
- SELECT employee_id, last_name, salary FROM employees WHERE department_id = 30;
- v_empno employees.employee_id%TYPE;
- v_lname employees. last_name%TYPE;
- v_sal employees.salary%TYPE;
- BEGIN
- OPEN cur_emps;
- LOOP
- FETCH cur_emps INTO v_empno, v_lname;
- EXIT WHEN cur_emps%notfound;
- DBMS_OUTPUT.PUT_LINE( v_empno | | ‘ ‘ | | v_lname);
- END LOOP;
- END;
is line #12 valid or invalid
Valid
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
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.
- DECLARE
- CURSOR country_curs(p_region_id countries.region_id%TYPE) IS
- SELECT country_name, area FROM ____ WHERE region_id = p_region_id;
- country_rec country_curs%ROWTYPE;
- BEGIN
- OPEN country_curs(____);
- LOOP
- FETCH country_curs INTO ______;
- EXIT WHEN ________%NOTFOUND;
- DBMS_OUTPUT.PUT_LINE(‘Name: ‘ | | country_rec.country_name | | ‘ Area: ‘ | | 11. country_rec.area);
- END LOOP;
- CLOSE ______;
- END;
What is missing in line #3?
countries
If you omit the ______ keyword, then the Oracle server waits indefinitely until the rows are available.
NOWAIT
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
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
The NOWAIT keyword is optional in the FOR UPDATE clause.
Group of answer choices
True
False
True
32/35
What symbol is used to terminate the TYPE statement?
DECLARE
TYPE person_dept IS . . . . . . . .
;
What symbol is used to enclose the elements of the TYPE structure?
()
Given the code below:
The closing of cursor is missing
True
False
True
The OPEN statement positions the cursor pointer at the first row.
True
False
True
The __________ keyword is used in user-defined exception for error notification.
RAISE
Code that defines the recovery actions to be performed when execution-time errors occur.
EXCEPTION HANDLER
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
The ___________ contains the exceptions handlers.
EXCEPTION SECTION
The EXECUTE keyword is used in user-defined exception for error notification.
Group of answer choices
True
False
False
When code does not work as expected, PL/SQL raises an exception handler.
Group of answer choices
True
False
False
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
Names for predefined exceptions must be declared in the declaration section.
Group of answer choices
True
False
The SQLCODE function returns the numeric value for the error code.
Group of answer choices
True
False
True
You can use the RAISE_APPLICATION_ERROR procedure to return user-defined error messages from stored subprograms.
Group of answer choices
True
False
True
Pragma declaration is used in declaring user-defined exceptions.
Group of answer choices
True
False
False
What is the first parameter of the PRAGMA EXCEPTION_INIT function?
Group of answer choices
Oracle error number
EXCEPTION NAME
Oracle error number
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
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
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
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
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
COMPOSITE
True
- DECLARE
- CURSOR cur_emps IS
- SELECT employee_id, last_name, salary FROM employees WHERE department_id =30;
- v_empno employees.employee_id%TYPE;
- v_lname employees.last_name%TYPE;
- v_sal employees.salary%TYPE;
- BEGIN
- OPEN cur_emps;
- LOOP
- FETCH cur_emps INTO v_empno, v_lname;
- EXIT WHEN cur_emps%notfound;
- DBMS_OUTPUT.PUT_LINE( v_empno | | ‘ ‘ | | v_lname);
- END LOOP;
- END
which line number(s) contains error?
Line 2
Line 4
No error
Line 3
Line 3
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
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 is missing in line 2 and 3?
INTO
IS
Nothing is missing
RECORD
IS
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
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
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
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
TYPE and Records are ________ structures.
COMPOSITE
A ________ allows us to declare a variable as a record based on a particular table’s structure.
%ROWTYPE
Use the %ISOPEN cursor attribute before performing a fetch to test whether the cursor is open.
Group of answer choices
True
False
True
Each exception handler is consists of a _____ clause, which specifies an exception name.
WHEN
The term ________ in exceptions is the same as handling any error by including corresponding exception handler.
TRAP
Exception handlers are code that defines the recovery actions to be performed when execution-time errors occur.
Group of answer choices
True
False
True
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
You can use the EXCEPTION_INIT procedure to return user-defined error messages from stored subprograms.
Group of answer choices
True
False
False
Pragma declaration is used in declaring non-predefined exceptions.
Group of answer choices
True
False
t
The NO_DATA_FOUND is an example of:
Group of answer choices
Non-predefined exception
Predefined exception
Predefined exception
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
The oracle error number, at the PRAGMA EXCEPTION_INIT function, starts with _____.
Group of answer choices
0
HYPEN
1
UNDERSCORE
HYPEN
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
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
- DECLARE
- CURSOR cur_emps IS
- SELECT employee_id, last_name, salary FROM employees WHERE department_id =30;
- v_empno employees.employee_id%TYPE;
- v_lname employees.last_name%TYPE;
- v_sal employees.salary%TYPE;
- BEGIN
- OPEN cur_emps;
- LOOP
- FETCH cur_emps INTO v_empno, v_lname;
- EXIT WHEN cur_emps%_____;
- DBMS_OUTPUT.PUT_LINE( v_empno | | ‘ ‘ | | v_lname);
- END LOOP;
- END;
What is missing in line#11?
Group of answer choices
FOUND
NOTFOUND
NOTFOUND
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
Given the code below, assume that the cursor contains 20 rows. How many rows will be fetched from the cursor?
- 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;
Group of answer choices
0
5
6
4
5
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
You must include the FOR UPDATE clause in the cursor query so that the rows are _____ on OPEN.
LOCKED
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
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
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
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
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
- DECLARE
- CURSOR cur_emps IS
- SELECT employee_id, last_name, salary FROM employees WHERE department_id =30;
- v_empno employees.employee_id%TYPE;
- v_lname employees.last_name%TYPE;
- v_sal employees.salary%TYPE;
- BEGIN
- OPEN cur_emps;
- LOOP
- FETCH cur_emps INTO v_empno, v_lname;
- EXIT WHEN cur_emps%NOTFOUND;
- DBMS_OUTPUT.PUT_LINE( v_empno | | ‘ ‘ | | v_lname);
- END LOOP;
- END;
The cursor declaration is correct.
Group of answer choices
True
False
t
The exception section begins with the keyword _______.
EXCEPTION
In exception section, the WHEN clause if followed by _______.
THEN
Each exception handler is consists of a WHEN clause, which specifies an exception name.
Group of answer choices
True
False
t
Exception section is mandatory in PL/SQL block.
Group of answer choices
True
False
False
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
The RAISE statement can be used to raise either user-defined or predefined exception.
Group of answer choices
True
False
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
The ___________ and ___________ are the two types of Oracle Server Errors.
Group of answer choices
Predefined, Non-predefined
Implicit, Explicit
Predefined, Non-predefined
The RAISE statement can be used to raise either user-defined or ________ exception.
Group of answer choices
PREDEFINED
NON-PREDEFINED
NON-PREDEFINED
Types and records are composite structures that can be declared anywhere that scalar variables can be declared.
Group of answer choices
True
False
t
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
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
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
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
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
