1. What is missing from the following cursor declaration?
CURSOR emp_curs
IS
SELECT * FROM departments
WHERE location_id = p_loc_id;
Mark for Review
(1) Points
A parameter is missing. The parameter should be coded as: (p_loc_id NUMBER) (*)
Nothing is wrong; the cursor declaration is correct.
The declaration is invalid. You cannot reference a cursor parameter in a WHERE clause.
A parameter is missing. The parameter should be coded as: (p_loc_id IN NUMBER)
Correct Correct
2. You want to use explicit cursors to fetch and display all the countries in a specific region. There are 19 rows in the WF_WORLD_REGIONS table. You want to use a different region each time the cursor is opened. How many cursors should you declare? Mark for Review
(1) Points
20 cursors, in case an extra row is inserted into WF_WORLD_REGIONS later.
19 cursors in 19 PL/SQL blocks (one in each block).
19 cursors, all in the same PL/SQL block.
None of the these.
One cursor with a parameter in the WHERE clause. (*)
Correct Correct
3. You have declared the following cursor: CURSOR country_curs IS
SELECT country_id, country_name
FROM wf_countries
FOR UPDATE WAIT 10; Another user updates a row in WF_COUNTRIES but does not COMMIT the update. What will happen when you OPEN country_curs; ? Mark for Review
(1) Points
A LOCKED_ROWS exception is raised immediately.
Your block fails because you should have coded: FOR UPDATE WAIT (10);
Your session waits for 10 seconds, and then returns control to your block so that it can continue to execute. (*)
Your session waits indefinitely until the other user COMMITs.
The other user's transaction is automatically rolled back.
Correct Correct
4. You declare a cursor as a join of two tables: CURSOR emp_dept_curs IS
SELECT last_name, salary, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
-- Point A -- ; You want to lock fetched rows from EMPLOYEES, but NOT lock fetched rows from DEPARTMENTS. Which of the following is correct at Point A? Mark for Review
(1) Points
FOR UPDATE
FOR UPDATE of salary (*)
FOR UPDATE (last_name)
FOR UPDATE OF employees
Correct Correct
5. Examine the following code:
DECLARE
CURSOR region_cur IS
SELECT * FROM wf_world_regions;
v_region_rec region_cur%ROWTYPE;
CURSOR country_cur (p_region_id NUMBER) IS
SELECT * FROM wf_countries
WHERE region_id = p_region_id;
v_country_rec country_cur%ROWTYPE;
BEGIN
OPEN region_cur;
LOOP
FETCH region_cur INTO v_region_rec;
EXIT WHEN region_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
(v_region_rec.region_name);
-- Line A --
LOOP
FETCH country_cur INTO v_country_rec;
EXIT WHEN country_cur%NOTFOUND;
......
What would you code at Line A?
Mark for Review
(1) Points
OPEN country_cur (v_region_rec.region_id); (*)
OPEN country_cur (p_region_id);
OPEN country_cur (wf_world_regions.region_id);
OPEN country_cur;
OPEN country_cur (region_cur.region_id);
Correct Correct
6. Assume that table BIGDEPTS contains 100 rows, and table BIGEMPS contains 1000 rows, with 10 employees in each department. Consider the following code:
DECLARE
CURSOR bigdept_cur IS
SELECT * FROM bigdepts;
CURSOR bigemp_cur IS
SELECT * FROM bigemps;
BEGIN
FOR dept_rec IN bigdept_cur LOOP
DBMS_OUTPUT.PUT_LINE (dept_rec.department_name);
FOR emp_rec IN bigemp_cur LOOP
IF emp_rec.department_id=dept_rec.department_id
THEN DBMS_OUTPUT.PUT_LINE (emp_rec.last_name);
END IF;
END LOOP;
END LOOP;
END;
Why is this code inefficient?
Mark for Review
(1) Points
It reads 1000 employee rows every time BIGEMP_CUR is OPENed, and then ignores 990 of them. (*)
It is doing a Cartesian Product, joining every employee with every department and displaying 1100 lines of output.
It is using cursor FOR loops, which are less efficient than OPENing and CLOSEing the cursors explicitly.
It is using two cursors when one cursor is enough.
It locks both tables unnecessarily.
Correct Correct
7. How must you reference one field which is part of a PL/SQL record? Mark for Review
(1) Points
It cannot be done.
field_name.record_name
field_name OF record_name
record_name(field_name)
record_name.field_name (*)
Correct Correct
8. Which of the following statements about the %ISOPEN cursor attribute is true? Mark for Review
(1) Points
You can issue the %ISOPEN cursor attribute only when more than one record is returned.
You can issue the %ISOPEN cursor attribute only when a cursor is open.
If a cursor is open, then the value of %ISOPEN is false.
You can issue the %ISOPEN cursor attribute when a cursor is open or closed. (*)
Correct Correct
9. Which of the following explicit cursor attributes evaluates to TRUE if the most recent FETCH returns a row? Mark for Review
(1) Points
%FOUND (*)
%ROWCOUNT
%NOTFOUND
%ROWTYPE
Correct Correct
10. A cursor FOR loop using a subquery can shorten code length when compared to an explicit cursor declaration. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
11. There are no employees in department_id 75.
Which of the following is NOT a valid cursor FOR loop with a subquery? Mark for Review
(1) Points
FOR emp_rec IN
(SELECT last_name, salary FROM employees ORDER BY last_name) LOOP ...
FOR emp_rec IN
(SELECT * FROM employees WHERE department_id = 75) LOOP ...
FOR emp_rec IN
(SELECT last_name, salary FROM employees) LOOP ...
None of these. They are all valid. (*)
FOR emp_rec IN
(SELECT * FROM employees) LOOP ...
Correct Correct
12. When using a cursor FOR loop, OPEN, CLOSE, and FETCH statements should not be explicitly coded. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
13. Which of these constructs can be used to fetch multiple rows from a cursor's active set? Mark for Review
(1) Points
An IF .... ELSE statement
A CASE statement
A basic loop which includes FETCH and EXIT WHEN statements (*)
A basic loop which includes OPEN, FETCH, and CLOSE statements
Correct Correct
14. Which of the following best describes the difference between implicit and explicit cursors? Mark for Review
(1) Points
Implicit cursor are named by the PL/SQL programmer, while explicit cursors are always named SQL.
Implicit cursors store rows on disk, while explicit cursors store rows in memory.
Implicit cursors are used for SELECT statements, while explicit cursors are used for DML statements.
Implicit cursors are defined automatically by Oracle, while explicit cursors must be declared by the PL/SQL programmer. (*)
Correct Correct
15. There are 8 countries in REGION_ID 13 (Central America). What will happen when the following code is executed?
DECLARE
CURSOR country_curs IS SELECT country_name FROM wf_countries
WHERE region_id = 13;
v_country_name wf_countries.country_name%TYPE;
BEGIN
OPEN country_curs;
WHILE country_curs%FOUND
LOOP
FETCH country_curs INTO v_country_name;
DBMS_OUTPUT.PUT_LINE(v_country_name);
END LOOP;
CLOSE country_curs;
END;
Mark for Review
(1) Points
The block will execute, but no rows will be displayed. (*)
None of the these.
The block will fail because you can not use a WHILE loop with an explicit cursor.
Eight rows will be fetched and displayed successfully.
The last seven rows will be fetched and displayed.
Correct Correct
16. Which one of the following explicit cursor declarations is NOT valid? Mark for Review
(1) Points
CURSOR country_curs IS SELECT country_name INTO v_country_name FROM wf_countries; (*)
CURSOR country_curs IS SELECT country_name FROM wf_countries ORDER BY population DESC;
CURSOR country_curs IS SELECT country_name, region_name FROM wf_countries c, wf_world_regions r WHERE c.region_id = r.region_id;
CURSOR country_curs IS SELECT country_name FROM wf_countries WHERE region_id IN (SELECT region_id FROM wf_world_regions WHERE LOWER(region_name) LIKE '%asia%');
Correct Correct
Section 6
(Answer all questions in this section)
17. Which of the following methods can be used to reference elements of an INDEX BY table? (Choose three.) Mark for Review
(1) Points
(Choose all correct answers)
EXISTS (*)
PREVIOUS
DROP
COUNT (*)
FIRST (*)
Correct Correct
18. You can store a whole record in a single variable using %ROWTYPE or by creating your own record structure as a type and then declaring a variable of that type. Mark for Review
(1) Points
True (*)
False
Correct Correct
Section 7
(Answer all questions in this section)
19. How can you retrieve the error code and error message of any Oracle Server exception? Mark for Review
(1) Points
By using the functions SQLCODE and SQLERRM (*)
By defining an EXCEPTION variable and using PRAGMA EXCEPTION_INIT
By using RAISE_APPLICATION_ERROR
By using the functions SQLCODE and SQLERR
Correct Correct
20. Which one of the following events would implicitly raise an exception? Mark for Review
(1) Points
A SELECT statement returns exactly one row.
An UPDATE statement modifies no rows.
A database constraint is violated. (*)
The PL/SQL programmer mis-spells the word BEGIN as BEGAN.
Correct Correct
21. Which of the following best describes a user-defined exception? Mark for Review
(1) Points
An error which is not automatically raised by the Oracle server (*)
A predefined Oracle Server error such as NO_DATA_FOUND
Any error which has an Oracle error number of the form ORA-nnnnn
A non-predefined Oracle Server error such as ORA-01400
Correct Correct
22. Non-predefined Oracle Server errors (associated with Oracle error numbers by PRAGMA EXCEPTION_INIT) can be declared and raised in inner blocks and handled in outer blocks. True or False? Mark for Review
(1) Points
True
False (*)
Correct Correct
23. What will happen when the following code is executed?
DECLARE
e_excep1 EXCEPTION;
e_excep2 EXCEPTION;
BEGIN
RAISE e_excep1;
EXCEPTION
WHEN e_excep1 THEN
BEGIN
RAISE e_excep2;
END;
END;
Mark for Review
(1) Points
It will fail to compile because you cannot declare more than one exception in the same block.
It will fail to compile because you cannot have a subblock inside an exception section.
It will fail to compile because e_excep1 is out of scope in the subblock.
It will compile successfully and return an unhandled e_excep2 to the calling environment. (*)
Correct Correct
24. Which of the following EXCEPTION sections is constructed correctly? (Choose three.) Mark for Review
(1) Points
(Choose all correct answers)
EXCEPTION
WHEN NO_DATA_FOUND THEN statement_1;
WHEN OTHERS THEN statement_2;
END;
(*)
EXCEPTION
WHEN OTHERS THEN statement_1;
WHEN NO_DATA_FOUND THEN statement_2;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN statement_1;
WHEN NO_DATA_FOUND THEN statement_2;
WHEN OTHERS THEN statement_3;
END;
EXCEPTION
WHEN TOO_MANY_ROWS THEN statement_1;
END;
(*)
EXCEPTION
WHEN OTHERS THEN statement_1;
END;
(*)
Incorrect Incorrect. Refer to Section 7 Lesson 1.
25. Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block? Mark for Review
(1) Points
A SELECT statement returns more than one row.
None of these.
Any other kind of exception that can occur within the block
A SELECT statement returns no rows.
All of these. (*)
Correct Correct
While a PL/SQL block is executing, more than one exception can occur at the same time. True or False? Mark for Review
(1) Points
TRUE
FALSE (*)
Correct Correct
27. No employees are in department_id 99. What output will be displayed when the following code is executed?
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM employees WHERE department_id = 99;
IF v_count = 0 THEN
RAISE NO_DATA_FOUND;
DBMS_OUTPUT.PUT_LINE('No employees found');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Department 99 is empty');
END;
Mark for Review
(1) Points
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
Department 99 is empty (*)
Correct Correct
28. The following three steps must be performed to use a user-defined exception: - Raise the exception - Handle the exception - Declare the exception In what sequence must these steps be performed? Mark for Review
(1) Points
Raise, Handle, Declare
Handle, Raise, Declare
Declare, Raise, Handle (*)
The steps can be performed in any order.
Correct Correct
29. Department-id 99 does not exist. What will be displayed when the following code is executed?
DECLARE
v_deptname departments.department_name%TYPE;
BEGIN
SELECT department_name INTO v_deptname
FROM departments WHERE department_id = 99;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20201,'Department does not exist');
END;
Mark for Review
(1) Points
ORA-01403: No Data Found ORA-20201: Department does not exist
ORA-20201: Department does not exist (*)
None of these.
ORA-01403: No Data Found
Correct Correct
Section 8
(Answer all questions in this section)
30. Which of the following keywords MUST be included in every PL/SQL procedure definition? (Choose two.) Mark for Review
(1) Points
(Choose all correct answers)
END (*)
REPLACE
BEGIN (*)
EXCEPTION
DECLARE
Correct Correct
31. A programmer wants to create a PL/SQL procedure named MY_PROC. What will happen when the following code is executed?
CREATE OR REPLACE PROCEDURE my_proc IS
v_empid employees.empid%TYPE;
BEGIN
SELECT employee_id INTO v_empid FROM employees
WHERE region_id = 999;
DBMS_OUTPUT.PUT_LINE('The salary is: ' || v_salary);
Mark for Review
(1) Points
The statement will raise a NO_DATA_FOUND exception because region_id 999 does not exist.
The statement will fail because the last line of code should be END my_proc; (*)
The statement will fail because you cannot declare variables such as v_empid inside a procedure.
Correct Correct
32. The following are the steps involved in creating, and later modifying and re-creating, a PL/SQL procedure in Application Express. In what sequence should these steps be performed?
Retrieve the saved code from "Saved SQL" in SQL Commands
Execute the code to create the procedure
Execute the code to re-create the procedure
Click on the "Save" button and save the procedure code
Modify the code in the SQL Commands window
Type the procedure code in the SQL Commands window
Mark for Review
(1) Points
F,B,D,A,E,C (*)
E,D,F,C,A,B
F,B,C,D,E,A
F,B,D,E,A,C
F,C,A,B,E,D
Correct Correct
33. You have created procedure MYPROC with a single parameter PARM1 NUMBER. Now you want to add a second parameter to the procedure. Which of the following will change the procedure successfully? Mark for Review
(1) Points
REPLACE PROCEDURE myproc
(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ...
CREATE OR REPLACE PROCEDURE myproc
(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ... (*)
The procedure cannot be modified. Once a procedure has been created, the number of parameters cannot be changed.
ALTER PROCEDURE myproc ADD (parm2 NUMBER);
CREATE OR REPLACE PROCEDURE myproc
(parm1 NUMBER, parm2 NUMBER);
(You do not need to repeat the detailed code of the procedure, only the header)
Correct Correct
34. A procedure will execute faster if it has at least one parameter. Mark for Review
(1) Points
True
False (*)
Correct Correct
35. Suppose you set up a parameter with an explicit IN mode. What is true about that parameter? Mark for Review
(1) Points
It must be the same type as the matching OUT parameter.
It must have a DEFAULT value.
It inherits its type from the matching OUT parameter.
It cannot have a DEFAULT value.
It acts like a constant (its value cannot be changed inside the subprogram). (*)
Correct Correct
36. What are the types of parameter modes? Mark for Review
(1) Points
LOCAL, GLOBAL, BOTH
IN, OUT, IN OUT (*)
CHARACTER, NUMBER, DATE, BOOLEAN
CONSTANT, VARIABLE, DEFAULT
Correct Correct
Section 9
(Answer all questions in this section)
37. The following code shows the dependencies between three procedures:
CREATE PROCEDURE parent
IS BEGIN
child1;
child2;
END parent;
You now try to execute:
DROP PROCEDURE child2;
What happens?
Mark for Review
(1) Points
You cannot drop CHILD2 because PARENT is dependent on it.
CHILD2 is dropped successfully. PARENT is marked INVALID, but CHILD1 is still valid. (*)
The database automatically drops CHILD1 as well.
The database automatically drops PARENT as well.
CHILD2 is dropped successfully. PARENT and CHILD1 are both marked INVALID.
Correct Correct
38. Which Data Dictionary view can be used to display the detailed code of a procedure in your schema? Mark for Review
(1) Points
USER_SUBPROGRAMS
USER_OBJECTS
None of these
USER_SOURCE (*)
USER_PROCEDURES
Correct Correct
39. You want to create a function which can be used in a SQL statement. Which one of the following can be coded within your function? Mark for Review
(1) Points
An OUT parameter
COMMIT;
One or more IN parameters (*)
RETURN BOOLEAN
Correct Correct
40. The following function has been created:
CREATE OR REPLACE FUNCTION upd_dept
(p_dept_id IN departments.department_id%TYPE)
RETURN NUMBER IS
BEGIN
UPDATE departments
SET department_name = 'Accounting'
WHERE department_id = p_dept_id;
RETURN p_dept_id;
END;
Which of the following will execute successfully?
Mark for Review
(1) Points
DELETE FROM departments
WHERE department_id = upd_dept(department_id);
SELECT upd_dept(80)
FROM dual;
DELETE FROM employees
WHERE department_id = upd_dept(80);
(*)
SELECT upd_dept(department_id)
FROM employees;
41. Which of the following is a legal location for a function call in a SQL statement? (Choose 3) Mark for Review
(1) Points
(Choose all correct answers)
VALUES clause of an INSERT statement (*)
WHERE clause in a DELETE statement (*)
CREATE TABLE statement
The ORDER BY and GROUP BY clauses of a query (*)
Correct Correct
42. Why will this function not compile correctly?
CREATE FUNCTION bad_one
IS BEGIN
RETURN NULL;
END bad_one;
Mark for Review
(1) Points
The body of the function must contain at least one executable statement (as well as RETURN).
You must declare the type of the RETURN before the IS. (*)
You must code CREATE OR REPLACE, not CREATE.
You cannot RETURN a NULL.
You must have at least one IN parameter.
Correct Correct
43. Based on the following function definition:
Create function annual_comp
(sal employees.salary%type,
comm_pct IN employees.commission%type)
...
Which one of the following is an incorrect call for annual_comp?
Mark for Review
(1) Points
Execute dbms_output.put_line(annual_comp (1000,.2));
Select employee_id, annual_comp(salary)
from employees; (*)
Declare
Ann_comp number (6,2);
Begin
...
Ann_comp := annual_comp(1000,.2);
...
End;
Select employee_id, annual_comp(salary, commission_pct)
from employees;
Correct Correct
44. Which of the following is found in a function and not a procedure? Mark for Review
(1) Points
IN parameters
An exception section
Return statement in the header (*)
Local variables in the IS/AS section
Correct Correct
45. You want to see the names of all the columns in a table in your schema. You want to query the Dictionary instead of using the DESCRIBE command. Which Dictionary view should you query? Mark for Review
(1) Points
USER_TAB_COLS (*)
USER_COLUMNS
USER_TABLES
USER_OBJECTS
Incorrect Incorrect. Refer to Section 9 Lesson 3.
46. A user executes the following statement:
CREATE INDEX fn_index ON employees(first_name);
What output will the following statement now display:
SELECT index_name
FROM user_indexes
WHERE index_name LIKE 'fn%';
Mark for Review
(1) Points
No output will be displayed (*)
FN_INDEX
fn_index FN_INDEX
fn_index
Correct Correct
47. How do you specify that you want a procedure MYPROCA to use "Definer's Rights"? Mark for Review
(1) Points
ALTER PROCEDURE myproca TO DEFINER;
CREATE OR REPLACE PROCEDURE myproca
AUTHID OWNER IS...
GRANT DEFINER TO myprocA;
CREATE OR REPLACE PROCEDURE myproca
AUTHID CURRENT_USER IS...
Definer's Rights are the default, therefore no extra code or commands are needed. (*)
Correct Correct
48. Which statement is true regarding the following subprogram?
PROCEDURE at_proc IS
PRAGMA AUTONOMOUS_TRANSACTION;
dept_id NUMBER := 90;
BEGIN
UPDATE ...
INSERT ...
END at_proc;
Mark for Review
(1) Points
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. (*)
The subprogram cannot do a COMMIT.
Correct Correct
49. User COLLEEN owns an EMPLOYEES table and wants to allow user AYSE to create indexes on the table. Which object privilege must Colleen grant to Ayse? Mark for Review
(1) Points
ALTER on EMPLOYEES
SELECT on EMPLOYEES
None of these
INDEX on EMPLOYEES (*)
CREATE on EMPLOYEES
Correct Correct
50. JOE's schema contains a COUNTRIES table. The following commands are executed by JOE and TOM:
(JOE): GRANT SELECT ON countries TO tom WITH GRANT OPTION;
(TOM): GRANT SELECT on joe.countries TO dick WITH GRANT OPTION;
Now, JOE executes:
REVOKE SELECT ON countries FROM tom;
What happens to the grant to DICK?
Mark for Review
(1) Points
Nothing. DICK's privilege is preserved even though TOM lost his privilege.
DICK also loses his SELECT privilege. (*)
The REVOKE statement fails because JOE must remove the SELECT privilege from both users at the same time.
The REVOKE statement fails because only the Database Administrator (not JOE) can revoke privileges.
Correct Correct
No comments:
Post a Comment