FA2 - Sheet1 (1) Flashcards
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.
EXCEPTION 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
False
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
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
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
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
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?
()
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
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
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 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 _______.
EXCEPTION HANDLER
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
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
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
PL/SQL records contain one or more components/fields of any _______ or composite type.
SCALAR
In explicit cursor, you need to fetch each row one at a time.
Group of answer choices
True
False
t
The _______ is an optional exception-handling clause that traps any exceptions that have not been explicitly handled.
OTHERS
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
Non-predefined exceptions has a standard Oracle error number (ORA-#####) and error message, but not a predefined name.
True
The RAISE_APPLICATION_ERROR can be used in executable and exception section.
Group of answer choices
True
False
True
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
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
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
The NOWAIT keyword is mandatory in the FOR UPDATE clause.
Group of answer choices
True
False
False
The term RAISE in exceptions is the same as handling any error by including corresponding exception handler.
Group of answer choices
True
False
There are 2 parameters needed in the pragma declaration of an exception.
Group of answer choices
True
False
True
The declared non-predefined exception is raised ____________.
Group of answer choices
EXPLICITLY
IMPLICITLY
IMPLICITLY
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
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
The RAISE keyword is used by non-predefined exception.
Group of answer choices
True
False
False
The ______ returns character data containing the message associated with the error number.
Group of answer choices
SQLERRM
SQLCODE
SQLERRM
If you omit the NOWAIT keyword, then the Oracle server waits indefinitely until the _____ are available.
ROWS
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
TYPE and ________ are composite structures.
RECORDS
PL/SQL records contain one or more components/fields of any SCALAR or ______ type.
COMPOSITE
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
The OTHERS is mandatory exception-handling clause that traps any exceptions that have not been explicitly handled.
Group of answer choices
True
False
False
PL/SQL records contain one or more components/fields of any scalar or composite type.
Group of answer choices
True
False
t
You must include the FOR UPDATE clause in the cursor query so that the rows are locked on _____.
OPEN
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
Always add exception handlers whenever there is a possibility of an error occurring.
Group of answer choices
True
False
True
The %ROWTYPE can be used to declare a record based on another record.
Group of answer choices
True
False
True
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
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
You must include the ________ clause in the cursor query so that the rows are locked on OPEN.
FOR UPDATE
