 |
| Working with Packages |
01. Examine this code:
CRATE OR REPLACE PACKAGE comm_package
IS
g_comm NUMBER := 10 ;
PROCEDURE reset_comm ( p_comm IN NUMBER ) ;
END comm._package ;/
User Jones executes the following code at 9:01 A.M.:
EXECUTE comm_package.g_comm := 15
User smith executes the following code at 9:05 A.M.:
EXECUTE comm_package.g_comm := 20
Which statement is true?
a. g_comm has a value of 15 at 9:06 A.M. for Smith.
b. g_comm has a value of 15 at 9:06 A.M. for Jones.
c. g_comm has a value of 20 at 9:06 A.M. for both Jones and Smith.
d.g_comm has a value of 15 at 9:03 A.M. for both Jones and Smith.
e. g_comm has a value of 10 at 9:06 A.M. for both Jones and Smith.
f. g_comm has a value of 10 at 9:03 A.M. for both Jones and Smith.
Answer: B
02. Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two)
a. It persists across transactions within a session.
b. It persists from session to session for the same user.
c. It does not persist across transaction within a session.
d. It persists from user to user when the package is invoked.
e. It does not persist from session to session for the same user.
Answer: A, E
03. Examine this package specification:
CREATE OR REPLACE PACKAGE concat_all
IS
v_string VARCHAR2 (100) ;
PROCEDURE combine ( p_num_val NUMBER ) ;
PROCEDURE combine ( p_dateval DATE ) ;
PROCEDURE combine ( p_char_val VARCHAR2, p_num_val NUMBER );
END concat_all;
/
Which overloaded combine procedure declaration can be added to this package specification?
a. PROCEDURE combine ;
b. PROCEDURE combine ( p_no NUMBER) ;
c. PROCEDURE combine ( p_val_1 VARCHAR2, p_val_2 NUMBER) ;
d. PROCEDURE concat_all ( p_num_val VARCHAR2, p_char_val NUMBER ) ;
Answer: A
04. Which two statements about the overloading feature of packages are true? (Choose two)
a. Only local or packaged sub programs can be overloaded.
b. Overloading allows different functions with the same name that differ only in their return types.
c. Overloading allows different subprograms with the same number, type and order of the parameter.
d. Overloading allows different subprograms with the same name and same number or type of the parameters.
e. Overloading allows different subprograms with the same name but different in either number or type or order of parameter.
Answer: A, E
05. The PROCEDURE add_product is defined within a package specifications as follows:
PROCEDURE add_product ( p_prodno NUMBER, p_prodname VARCHAR2 ) ;
Which procedure declaration can’t be added to package specifications?
a. PROCEDURE add_product ( p_order_date DATE ) ;
b. PROCEDURE add_product ( p_name VARCHAR2, p_ordered DATE ) ;
c. PROCEDURE add_product ( p_prodname VARCHAR2, p_price NUMBER ) ;
d. PROCEDURE add_product ( p_prize NUMBER, p_description VARCHAR2 ) ;
Answer: D
06. Which compiler directive to check the purity level of functions?
a. PRAGMA SECURITY_LEVEL.
b. PRAGMA SEARIALLY_REUSABLE.
c. PRAGMA RESTRICT_REFERRENCES.
d. PRAGMA RESTRICT_PURITY_LEVEL.
e. PRAGMA RESTRICT_FUNCTION_REFERRENCE.
Answer: C
07. Which statement about the forward declarations is true?
a. Forward declarations are not allowed in packages.
b. Forward declarations let you use mutually referential subprograms in a package.
c. A forward declaration means placing a subprogram declaration at the end of the package body.
d. Forward declaration in a package specification contains only the name of the sub program without the formal parameter list.
Answer: B
08. Which compiler directive must you use to specify the purity of a packaged function when creating a package?
a. PRAGMA EXCEPTION_INIT
b. PRAGMA PURITY_CHECK
c. PRAGMA PURITY_RESTRICT
d. PRAGMA RESTRICT_REFERENCES
Answer: D
09. Examine this code:
CRATE OR REPLACE PACKAGE comm_package
IS
g_comm NUMBER := 10 ;
PROCEDURE reset_comm ( p_comm IN NUMBER ) ;
END comm._package ;
/
User Jones executes the following code at 9:01 A.M.:
EXECUTE comm_package.g_comm := 15
User smith executes the following code at 9:05 A.M.:
EXECUTE comm_package.g_comm := 20
Which statement is true?
A. g_comm has a value of 15 at 9:06 A.M. for Smith.
B. g_comm has a value of 15 at 9:06 A.M. for Jones.
C. g_comm has a value of 20 at 9:06 A.M. for both Jones and Smith.
D. g_comm has a value of 15 at 9:03 A.M. for both Jones and Smith.
E. g_comm has a value of 10 at 9:06 A.M. for both Jones and Smith.
F. g_comm has a value of 10 at 9:03 A.M. for both Jones and Smith.
Answer: B
10. Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two)
A. It persists across transactions within a session.
B. It persists from session to session for the same user.
C. It does not persist across transaction within a session.
D. It persists from user to user when the package is invoked.
E. It does not persist from session to session for the same user.
Answer: A, E
11. Examine this package
CREATE OR REPLACE PACKAGE discounts IS
g_id NUMBER := 7839 ;
discount_rate NUMBER := 0.00 ;
PROCEDURE display_price ( p_price NUMBER ) ;
END discounts ;
/
CREATE OR REPLACE PACKAGE BODY discounts
IS
PROCEDURE display_price ( p_price_NUMBER )
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Discounted ‘||
( p_price * NVL(discount_rate, 1)))
END display_price ;
BEGIN
discount_rate := 0.10;
END discounts ;
/
Which statement is true?
A. The value of discount_rate always remain 0.00 in a session.
B. The value of discount_rate is set to 0.10 each time the package are invoked in a session.
C. The value of discount_rate is set to 1 each time the procedure display_price is invoked.
D. The value of discount_rate is set to 0.10 when the package is invoked for first time in a session.
Answer: D
12. Examine this package specification:
CREATE OR REPLACE PACKAGE concat_all
IS
v_string VARCHAR2(100) ;
PROCEDURE combine( p_num_val NUMBER ) ;
PROCEDURE combine ( p_dateval DATE ) ;
PROCEDURE combine( p_char_val VARCHAR2, p_num_val NUMBER );
END concat_all;
/
Which overloaded combine procedure declaration can be added to this package specification?
A. PROCEDURE combine ;
B. PROCEDURE combine ( p_no NUMBER) ;
C. PROCEDURE combine ( p_val_1 VARCHAR2, p_val_2 NUMBER) ;
D. PROCEDURE concat_all ( p_num_val VARCHAR2, p_char_val NUMBER ) ;
Answer: A
13. Examine this function
CREATE OR REPLACE FUNCTION calc_player_avg
( v_id IN player_bat_stat.player_id%TYPE )
RETURN NUMBER
IS
v_avg NUMBER;
BEGIN
SELECT hits/add_bats
INTO v_avg
FROM player_bat_stat
WHERE player_id = v_id ;
RETURN (v_avg) ;
END;
This function must be moved to a package.
Which additional statement must be added to the function to allow you to continue using the function in the group by the clause of a select statement?
A. PRAGMA RESTRICT_REFERENCES (calc_player_avg, WNDS, WNPS) ;
B. PRAGMA RESTRICT_REFERENCES (calc_player_avg, WNPS) ;
C. PRAGMA RESTRICT_REFERENCES (calc_player_avg, RNPS, WNPS) ;
D. PRAGMA RESTRICT_REFERENCES (calc_player_avg, ALLOW_GROUP_BY) ;
Answer: A
14. Which two statements about the overloading feature of packages are true? (Choose two)
A. Only local or packaged sub programs can be overloaded.
B. Overloading allows different functions with the same name that differ only in their return types.
C. Overloading allows different subprograms with the same number, type and order of the parameter.
D. Overloading allows different subprograms with the same name and same number or type of the parameters.
E. Overloading allows different subprograms with the same name but different in either number or type or order of parameter.
Answer: A, E
15. The PROCEDURE add_product is defined within a package specifications as follows:
PROCEDURE add_product ( p_prodno NUMBER, p_prodname VARCHAR2 ) ;
Which procedure declaration can’t be added to package specifications?
A. PROCEDURE add_product ( p_order_date DATE ) ;
B. PROCEDURE add_product ( p_name VARCHAR2, p_ordered DATE ) ;
C. PROCEDURE add_product ( p_prodname VARCHAR2, p_price NUMBER ) ;
D. PROCEDURE add_product ( p_prize NUMBER, p_description VARCHAR2 ) ;
Answer: D
16. Which compiler directive to check the purity level of functions?
A. PRAGMA SECURITY_LEVEL.
B. PRAGMA SEARIALLY_REUSABLE.
C. PRAGMA RESTRICT_REFERRENCES.
D. PRAGMA RESTRICT_PURITY_LEVEL.
E. PRAGMA RESTRICT_FUNCTION_REFERRENCE.
Answer: C
17. Which statement about the forward declarations is true?
A. Forward declarations are not allowed in packages.
B. Forward declarations let you use mutually referential subprograms in a package.
C. A forward declaration means placing a subprogram declaration at the end of the package body.
D. Forward declaration in a package specification contains only the name of the sub program without the formal parameter list.
Answer: B
18. Examine this package body:
CREATE OR REPLACE PACKAGE BODY forward_pack
IS
v_sum NUMBER ;
PROCEDURE calc_ord(. . . ) ;
PROCEDURE generate_summary(. . . )
IS
BEGIN
calc_ord(. . . ) ;
. . .
END calc_ord ;
END forward_pack ;
/
Which construct has a forward declaration?
A. v_sum
B. calc_ord
C. forward_pack
D. generate_summary
Answer: B
19. Which compiler directive must you use to specify the purity of a packaged function when creating a package?
A. PRAGMA EXCEPTION_INIT
B. PRAGMA PURITY_CHECK
C. PRAGMA PURITY_RESTRICT
D. PRAGMA RESTRICT_REFERENCES
Answer: D
20. Written a PL/SQL loop, you need to test if the current FETCH was successful. Which SQL cursor attribute would you use to accomplish this task?
A. SQL % ISOPEN
B. SQL % ROWCOUNT
C. SQL % FOUND
D. This task cannot be accomplished with a SQL cursor attribute.
E. A SQL cursor attribute cannot be used within a PL/SQL loop.
Answer: C
21. Which statement about implicit cursors is true?
A. Implicit cursors are declared implicitly only for DML statements.
B. Implicit cursors are declared implicitly for all the DML and SELECT statements.
C. Programmers need to close all the implicit cursors before the end of the PL/SQL program.
D. Programmers can declare implicit cursors by using the cursor type in the declaration section.
Answer: B
22. Evaluate this PL/SQL block:
DECLARE
v_result NUMBER(2);
BEGIN
DELETE
FROM employee
WHERE dept_id IN (10,20,30);
v_result := SQL%ROWCOUNT;
COMMIT;
END;
What will be the value of v_result if no rows are deleted?
A. 0
B. 1.30
C. True
D. Null
Answer: A
23. Which SELECT statement would you use in a PL/SQL block to query the employee table and retrieve the last name and salary of the employee whose ID is 3?
A. SELECT last_name,salary
FROM employee;
B. SELECT last_name,salary
FROM employee;
WHERE id=3;
C. SELECT last_name,salary
INTO v_last_name,v_salary
FROM employee
WHERE id=3;
D. SELECT l last_name,salary
FROM employee;
INTO v_last_na me,v_salary
WHERE id=3;
E. SELECT last_name,salary
INTO v_last_name,v_salary
WHERE id=3;
Answer: C
24. You need to create a PL/SQL program to insert records into employee table. Which block of code successfully uses the insert command?
A. DECLARE
v_hiredate DATE := SYSDATE:
BEGIN
INSERT INTO emp (empnp, ename, hiredate, deptno)
VALUES (empno_sequence.nextval, ‘&name’,v_hirerdate and deptno)
B. DECLARE
v_hiredate D ATE := SYSDATE:
BEGIN
INSERT INTO emp (empnp, ename, hiredate, deptno)
C. DECLARE
v_hiredate DATE := SYSDATE:
BEGIN
INSERT INTO emp (empnp, ename, hiredate)
VALUES (empno_sequence.nextval, name, v_hirerdate)
END:
D. DECLARE
v-hiredate D ATE := SYSDATE:
BEGIN
INSERT INTO emp (empnp,ename,heridate,deptno)
VALUES (empno_sequence.nextval, ‘&name’,v_herdate, &deptno)
Job=Clerk
END:
Answer: C
25. In the declarative section of a PL/SQL block, you created but did not initialize a number variable. When the block executes what will be the initial value of the variable?
A. 0.
B. Null.
C. It depends on the scale and precision of the variable.
D. The block will not execute because the variable was not initialized.
Answer: B
26. The employee table contains three columns:
BONUS NUMBER(7,2)
DEPT_ID NUMBER(9)
There are three departments and each department has at least one employee bonus values at least one employee. Bonus values are greater than 500;not all employee receive a bonus.
Evaluate this PL/SQL block:
DECLARE
v_bonus employee.bonus%TYPE := 300;
BEGIN
UPDATE employee
SET bonus = bonus + v_bonus
WHERE dept_id IN (10, 20, 30);
COMMIT;
END;
What will be the result?
A. All the employees will be given a 300 bonus.
B. A subset of 300 employees will be given a 300 bonus.
C. All employees will be given a 300 increase in bonus.
D. A subset of employees will be given a 300 increase in bonus.
Answer: D
27. Which PL/SQL section contains SQL statements to manipulate data in the database?
A. Header
B. Exception
C. Executable
D. Declarative
Answer: C
28. Which command will end the current transaction by making all the pending changes to the database permanent?
A. DELETE
B. UPDATE
C. COMMIT
D. ROLLBACK
Answer: C
29. Which types of commands are supported by PL/SQL?
A. DDL
B. DCL
C. DML
D. No commands are supported by PL/SQL.
Answer: C
30. Which cursor attribute evaluates to TRUE if the most recent SQL statement affects one or more rows?
A. SQL%FOUND
B. SQL%ISOPEN
C. SQL%ROWCOUNT
D. SQL%NOTFOUND
Answer: A
31. Which will a SELECT statement in a PL/SQL block raise an exception?
A. It retrieves only one row.
B. It retrieves more than one row.
C. The SELECT statement is missing a required clause.
D. The datatypes within the SELECT statement are inconsistent.
Answer: B
32. Which clause is required in a SELECT statement within a PL/SQL block?
A. INTO
B. WHERE
C. HAVING
D. GROUP BY
E. ORDER BY
Answer: A
33. Which characteristic applies to an implicit cursor?
A. Will process only one row
B. Will attempt only one fetch
C. Allows the programmer to control the number of fetches performed
D. Will perform only one fetch and will process all of the rows returned by the query
Answer: A
34. Which of the following statements is true about implicit cursor?
A. Implicit cursors are used for SQL statements that are not named.
B. Developers should use implicit cursors with great care.
C. Implicit cursors are used in cursor for loops to handle data processing.
D. Implicit cursors are no longer a feature in Oracle.
Answer: A
35. After executing an UPDATE statement, the developer codes a PL/SQL block to perform an operation based on SQL%ROWCOUNT.
A. A BOOLEAN value representing the success or failure of the update.
B. A numeric value representing the number of row updated.
C. A VARCHAR2 value identifying the name of the table updated.
D. A LONG value containing all data from the table.
Answer: B
36. Which three of the following are implicit cursor attributes? Choose three.
A. %FOUND
B. %TOO_MANY_ROWS
C. %NOTFOUND
D. %ROWCOUNT
E. %ROWTYPE
Answer: A, C, D
0 Comments