NEW
Font size
WorksheetsPL/SQL Brain Bowl Championship!
Total questions: 50
Worksheet time: 3hrs 30mins
After a cursor has been closed, it can be opened again in the same PL/SQL block.
True
False
For which type of SQL statement must you use an explicit cursor?
Data Definition Language (DDL) statements
DML statements that process more than one row
Queries that return a single row
Queries that return more than one row
What is wrong with this code?
Nothing is wrong; all the rows will be FETCHed and displayed
The CLOSE statement should be coded after END LOOP;
The EXIT WHEN... statement should be coded outside the loop
The OPEN statement should be inside the loop
Which of the following is a benefit of using a cursor FOR loop?
The exception handling is done automatically
Because there is less code, the loop executes faster
%ROWCOUNT increments automatically each time a row is FETCHed
The OPEN, CLOSE, FETCH and EXIT from the loop are done automatically
How many explicit cursors can be declared and used in a single PL/SQL block?
Only one
One or two
Up to eight cursors
As many as needed
Which of the following is a good reason to use two cursors in a single PL/SQL block?
When two tables are related to each other (often by a foreign key), and we want to produce a multilevel report using data from both tables
To allow one cursor to be opened twice at the same time
To allow rows to be locked as they are FETCHed
To speed up the execution of the PL/SQL block
How many attributes can be accessed with v_dept_info_rec in this code?
One
Two
Three
Four
An attempt to insert a null value into a NOT NULL table column raises an ORA-01400 exception. How can you code an exception handler to trap this exception?
Declare a variable as follows: e_null_excep EXCEPTION := -01400; Then test for WHEN e_null_excep in the exception section
Declare a variable e_null_excep of type EXCEPTION, associate it with ORA-01400 using a PRAGMA directive, and test for WHEN e_null_excep in the exception section
Declare a variable e_null_excep of type VARCHAR2, associate it with ORA-01400 using a PRAGMA directive, and test for WHEN e_null_excep in the exception section
Test for WHEN ORA-1400 in the exception section
An attempt to update an employee's salary to a negative value will violate a check constraint and raise an ORA-02290 exception. Which of the following is a correct definition of a handler for this exception?
DECLARE e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(-02290, e_sal_excep);
DECLARE e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_sal_excep, -02290);
DECLARE e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_sal_excep, 02290);
DECLARE e_sal_excep EXCEPTION; PRAGMA_EXCEPTION_INIT(e_sal_exception, -02290);
Department_id 99 does not exist. What will be displayed when this code is executed?
ORA-20201: Department does not exist
ORA-01403: No Data Found
ORA-01403: No Data Found ORA-20201: Department does not exist
This exception handler will successfully insert the Oracle error number and error message into a log table whenever an Oracle Server error occurs. (Assume that err_log_table has been created with suitable columns and datatypes)
True
False
What will happen when this code is executed?
The code will execute successfully and 'Outer Raised' will be displayed
The code will fail to compile because e_inner_excep cannot be referenced in the outer block
The code will fail to compile because e_inner_excep was declared but never RAISEd
The code will propagate the e_outer_excep back to the calling environment
There are three employees in department 90. What will be displayed when this code is executed?
An unhandled exception will be propagated back to the calling environment
Message 1
Message 1 Message 3 Message 4
Message 1 Message 4
User-defined exceptions must be declared explicitly by the programmer, but then are raised automatically by the Oracle Server.
True
False
No employees are in department_id 99. What output will be displayed when this code is executed?
Department 99 is empty
No employees found
No employees found - Department 99 is empty
The block will fail because you cannot explicitly RAISE a predefined Oracle Server error such as NO_DATA_FOUND
Which of the following is the actual parameter?
p_param
'Smith'
v_param
None of these
A programmer creates a PL/SQL subprogram which is compiled and stored in the database. Two separate users then execute an application which invokes this subprogram four times. How many times must the subprogram be recompiled?
Zero times
One time
Two times
Four times
Eight times
Which statement is true regarding this subprogram?
The subprogram cannot do a COMMIT
The subprogram will fail because the RETURN is not specified
The subprogram's success depends on the calling program
The subprogram's success is independent of the calling program
Examine this code (the code of CHILD2 is not shown). Employee_id 9999 does not exist. What happens when PARENT is executed?
CHILD1 ends abruptly, and PARENT handles the exception successfully and ends - CHILD2 does not execute
CHILD1 ends abruptly, and then PARENT also ends abruptly with an unhandled exception
CHILD1 handles the exception successfully and ends. PARENT continues to execute and invokes CHILD2
PARENT fails to compile because you cannot have the same exception handler in two separate subprograms
PARENT handles the exception, and then CHILD1 resumes execution
A function must have at least one IN parameter, and must return exactly one value.
True
False
What is wrong with this code?
P_PARAM must be declared AFTER the RETURN clause
P_PARAM must have a default value
RETURN (p_param > 10); is wrong because you cannot return an expression
The datatype of the IN parameter cannot have a precision or scale. It must be NUMBER, not NUMBER(4)
The NUMBER datatype must have a scale as well as a precision
What will be displayed when this code is executed?
8
12
20
varB
Nothing, the block will fail with an error
A Scalar data type holds a(n) ____ value.
Image
Large
Multi
Single
Which of these is NOT a valid cursor declaration?
CURSOR emp_curs IS SELECT salary FROM employees ORDER BY salary DESC;
CURSOR emp_curs IS SELECT salary FROM employees WHERE last_name LIKE 'S%';
CURSOR emp_curs IS SELECT salary INTO v_salary FROM employees;
CURSOR emp_dept_curs IS SELECT e.salary, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id;
If you use the %TYPE attribute, you can avoid hard-coding the column name.
True
False
What happens when this block of code finish running?
You get an error; you cannot COMMIT twice in a row
You have nothing new; the last ROLLBACK undid the INSERTs
You have the rows added twice; there are four new rows
You have the two new rows added
There are three employees in department 90. What will be displayed when this code is executed?
NO 3
YES 1
YES 3
Nothing will be displayed - the block will fail because you cannot use implicit cursor attributes directly in a call to DBMS_OUTPUT.PUT_LINE
Given this first section of code - which statement will always return exactly one value?
SELECT salary INTO v_result FROM employees;
SELECT salary INTO v_result FROM employees WHERE department_id = 80;
SELECT salary INTO v_result FROM employees WHERE last_name ='Smith';
SELECT SUM(salary) INTO v_result FROM employees;
What is the value of GAME at the end of this block of code?
False
NULL
'lost'
'won'
What will be displayed when this code is executed?
Equal
Undefined
Unequal
Nothing will be displayed because V_MESSAGE is set to NULL
How many times will the SELECT statement execute in this code?
Once
Twice
An infinite number of times because the EXIT condition will never be true
Never (the SELECT will not execute at all)
Which of these is NOT a valid cursor declaration?
CURSOR emp_curs IS SELECT salary FROM employees ORDER BY salary DESC;
CURSOR emp_curs IS SELECT salary FROM employees WHERE last_name LIKE 'S%';
CURSOR emp_curs IS SELECT salary INTO v_salary FROM employees;
CURSOR emp_dept_curs IS SELECT e.salary, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id;
Which of the following statements successfully opens the cursor and fetches the first row of the active set in this code?
OPEN emp_curs; FETCH emp_curs;
OPEN emp_curs; FETCH emp_curs INTO v_last_name, v_salary;
OPEN emp_curs; FETCH emp_curs INTO v_salary, v_last_name;
OPEN emp_curs; FETCH FIRST emp_curs INTO v_last_name, v_salary;
To display the salary of an employee, what must be coded at Point A in this code?
employees.salary
emp_curs.salary
emp_rec.salary
emp_rec.salary IN emp_curs
salary
This line of code is correct.
True
False
Procedure ins_emp accepts an employee_id as an IN parameter and attempts to insert a row with that employee_id into the EMPLOYEES table. Ins_emp does not contain an exception section. A second procedure is created as shown.
When call_ins_emp is executed, (assuming Auto-commit is turned on), which rows will be inserted into the EMPLOYEES table?
99 only
99 and 999
999 only
No rows will be inserted
What is wrong with this code?
Nothing is wrong, the trigger will execute correctly
The trigger body is reading the same table (employees) that the triggering event is updating
You can never use SELECT inside a DML trigger
You cannot use a DECLARE statement in a trigger
You must use RAISE_APPLICATION_ERROR in a BEFORE trigger
A database trigger is a PL/SQL stored subprogram which is explicitly invoked just like a procedure or a function.
True
False
Which of the following could NOT be done by a database trigger?
Enforcing a complex business rule
Enforcing a complex database security check
Ensuring that a student never arrives late for a class
Keeping a log of how many rows have been inserted into a table
Recalculating the total salary bill for a department whenever an employee's salary is changed
You can use a trigger to prevent rows from being deleted from the EMPLOYEES table on Mondays.
True
False
User AYSEGUL successfully creates this trigger. User AYSEGUL then tries to drop the LOCATIONS table. What will happen?
An error message is displayed because you cannot drop a table that is associated with a trigger
Both the table and the trigger are dropped
The table is dropped and the trigger is disabled
The trigger is dropped but the table is not dropped
A DML statement trigger fires only once for each triggering DML statement, while a row trigger fires once for each row processed by the triggering statement.
True
False
Which of the following is the correct syntax for creating a DML trigger associated with the EMPLOYEES table? The trigger must fire whenever an employee's JOB_ID is updated, but not if a different column is updated.
CREATE TRIGGER job_upd_trigg AFTER UPDATE OF job_id ON employees
BEGIN ...
CREATE TRIGGER job_upd_trigg AFTER UPDATE ON employees.job_id BEGIN ...
CREATE TRIGGER job_upd_trigg AFTER UPDATE ON employees(job_id) BEGIN ...
CREATE TRIGGER job_upd_trigg WHENEVER UPDATE OF job_id IN employees
BEGIN ...
What is wrong with this code?
Nothing is wrong, this trigger will compile and execute successfully
The last line should be: END loc_trigg;
The second line should be: BEFORE DELETE OF locations
You cannot use RAISE_APPLICATION_ERROR inside a trigger
You cannot use ROLLBACK inside a trigger
Examine this code. To create a row trigger, what code should be included at Line A?
FOR ALL ROWS
FOR EACH ROW
FOR EACH AND EVERY ROW
FOR EVERY ROW
Nothing is needed because DML triggers are row triggers by default
PL/SQL procedure A invokes procedure B, which in turn invokes procedure C, which references table T. If table T is dropped, which of the following statements is true?
A, B and C are all invalid
A, B and C are all still valid
B and C are invalid, but A is still valid
C is invalid but A and B are still valid
Examine this trigger. It should raise an application error if a user tries to update an employee's last name. It should allow updates to all other columns of the EMPLOYEES table. What should be coded at line A?
IF UPDATE('LAST_NAME') THEN
IF UPDATING('LAST_NAME') THEN
IF UPDATING LAST_NAME THEN
IF UPDATING THEN
You need to create a trigger that will fire whenever an employee's salary or job_id is updated, but not when any other column of the EMPLOYEES table is updated. Which of the following is the correct syntax to do this?
CREATE TRIGGER emp_upd_trigg AFTER UPDATE OF salary, job_id ON employees BEGIN ...
CREATE TRIGGER emp_upd_trigg AFTER UPDATE OF salary OR job_id ON employees BEGIN ...
CREATE TRIGGER emp_upd_trigg AFTER UPDATE OF (salary, job_id) ON employees BEGIN ...
CREATE TRIGGER emp_upd_trigg AFTER UPDATE ON employees (salary, job_id) BEGIN ...
A business rule states that an employee's salary must be between 4000 and 30000. We could enforce this rule using a CHECK constraint, but it is better to use a database trigger.
True
False
The following variable declaration is correct:
DECLARE test NUMBER(5);
True
False
