1. The number of cascading triggers is limited by which data base
initialization parameter?
a. CASCADE_TRIGGER_CNT
b. OPEN_CURSORS ****
c. OPEN_TRIGGERS
d. OPEN_DB_TRIGGERS
2. Which type of package construct must be declared both within the
package specifications and package body?
a. All package variables.
b. Boolean variables.
c. Private procedures and functions.
d. Public procedures and functions. ****
3. Why do stored procedures and functions improve performance?
(choose two)
a. They reduce network round trips. ****
b. They postpone PL/SQL parsing until run time. ****
c. They allow the application to perform high speed processing
locally.
d. They reduce the number of calls to the database and decrease
network traffic by bundling commands.
e. They reduce the number of calls to the database and decrease
network traffic by using the local PL/SQL engine.
4. When creating stored procedures and functions, which construct
allows you to transfer values to and from the calling environment?
a. Local variables.
b. Formal arguments. ****
c. Boolean variables.
d. Substitution variables.
5. You need to remove the database trigger, BUSINESS_RULE. Which
command do you use to remove the trigger in the SQL*Plus
environment?
a. DROP TRIGGER business_rule; ****
b. DELETE TRIGGER business_rule;
c. REMOVE TRIGGER business_rule;
d. ALTER TRIGGER business_rule;
e. DELETE FROM USER_TRIGGER
WHERE TRIGGER_NAME= ‘BUSINESS_RULE’;
6. Which two tables are fused track object dependencies?
a. USER_DEPENDENSIES. ****
b. USER_IDEPTREE.
c. IDEPTREE. ****
d. USER_DEPTREE.
e. USER_DEPENDS.
7. The QUERY_PRODUCT procedure directly references the product
table. There is a NEW_PRODUCT_VIEW view created based on the NOT
NULL columns of the table. The ADD_PRODUCT procedure updates the
table indirectly by the way of NEW_PRODUCT_VIEW view. Under which
circumstances does the procedureADD_PRODUCT get invalidated but
automatically get complied when invoked?
a. When the NEW_PRODUCT_VIEW is dropped.
b. When rows of the product table are updated through SQL Plus.
c. When the internal logic of the QUERY_PRODUCT procedure is
modified.
d. When a new column that can contain null values is added to the
product table. ****
e. When a new procedure is created that updates rows in the product
table directly.
8. You need to recompile several program units you have recently
modified through a PL/SQL program. Which statement is true?
a. You cannot recompile program units using a PL/SQL program.
b. You can use the DBMS_DDL. REOMPILE package procedure to recompile
the program units.
c. You can use the DBMS_ALTER. COMPILE packaged procedure to
recompile the program units.
d. You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to
recompile the program units. ****
e. You can use the DBMS_SQL.ALTER_COMPILE packaged procedure to
recompile the program units.
9. Which type of argument passes a value from a calling environment?
a. VARCHER2
b. BOOLEAN
c. OUT ****
d. IN
10. You need to create a trigger on the EMP table that monitors
every row that is changed and places this information into the
AUDIT_TABLE. Which type of trigger do you create?
a. Statement-level trigger on the EMP table.
b. For each row trigger on the EMP table. ****
c. Statement-level trigger on the AUDIT_TABLE table.
d. For each row statement level trigger on the EMP table.
e For each row trigger on the AUDIT_TABLE table.
11. In order for you to create a run package, MAINTAIN_DATA, which
privilege do you need?
a. EXECUTE privilege on the MAINTAIN_DATA package. ****
b. INVOKE privilege on the MAINTAIN_DATA package.
c. EXECUTE privilege on the program units in the MAINTAIN_DATA
package.
d. Object privilege on all of the objects that the MAINTAIN_DATA
package is accessing.
e. Execute privilege on the program units inside the MAINTAIN_DATA
package and execute privilege on the MAINTAIN_DATA package.
12. You have created a script file EMP_PROC.SQL that holds text to
create a procedure, PROCESS_EMP. You have compiled the procedure for
the SQL Plus environment by running the script file EMP_PROC.SQL.
What happens if there are syntax errors in the procedure
PROCESS_EMP?
a. The errors are stored in the EMP_PROC.ERR file.
b. The errors are displayed to the screen when the script file is
run.
c. The errors are stored in the PROCEDURE_ERRORS data dictionary
view.
d. You need to issue the SHOWERRORS command in the SQL Plus
environment to see the errors. ****
e. You need to issue the DISPLAY ERRORS command in the SQL Plus
environment to see the errors.
13. Which statement about local dependent objects is true?
a. They are on different nodes.
b. They are in a different database.
c. They are on the same node in the same database. ****
d. They are on the same node in a different database.
14. You need to create a stored procedure that deletes rows from a
table. The name of the table from which the rows are to be deleted
is unknown until run time. Which method do you implement while
creating such a procedure?
a. Use SQL command DELETE in the procedure to delete the rows.
b. Use DBMS_SQL packaged routines in the procedure to delete the
rows. ****
c. Use DBMS_DML packaged routines in the procedure to delete the
rows.
d. Use DBMSDELETE packaged routines in the procedure to delete the
rows.
e. YOU cannot have a delete statement without providing a table name
before compile time.
15. Under which situation do you create a server-side procedure?
a. When the procedure contains no SQL statements.
b. When the procedure contains no PL/SQL commands.
c. When the procedure needs to be used by many client applications
accessing several remote databases.
d. When the procedure needs to be used by many users accessing the
same schema objects on a local database. ****
16. Examine this procedure:
CREATE OR REPLACE PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHER2)
IS
BEGIN
INSERT INTO PLAYER(ID,LAST_NAME).
VALUES(V_ID,V_LAST_NAME);
COMMIT;
END;
This procedure must invoke the UPD-STAT procedure and pass a
parameter. Which statement will successfully invoke this procedure?
a. EXECUTE UPD_BAT_STAT(V_ID);
b. UPD_BAT_STAT(V_ID); ****
c. RUN UPD_BAT_STAT(V_ID);
d. START UPD_BAT_STAT(V_ID);
17. Match the purity levels to their correct definitions:
Terms
RNTS
RNPS
WNDS
WNPS
Definitions
The function cannot modify the database tables.
The function cannot change the values of the package variables.
The function cannot query database tables.
The function cannot reference the value of public packaged
variables.
18. 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;
SELECTS HITS/AT_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 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);
19. A programmer develops a procedure, ACCOUNT_TRANSACTION, and has
left your company. You are assigned a task to modify this procedure.
You want to find all the program units invoking the
ACCOUNT_TRANSACTION procedure. How can you find this information?
a. Query the USER_SOURCE data dictionary view.
b. Query the USER_PROCEDURES data dictionary view.
c. Query the USER_DEPENDENCIES data dictionary views. ****
d. Set the SQL Plus environment variable trade code=true and run the
ACCOUNT_TRANSACTION procedure.
e. Set the SQL Plus environment variable DEPENDENCIES=TRUE and run
the Account_Transaction procedure.
20. Examine this package.
CREATE OR REPLACE PACKAGE BB_PACK
IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID NUMBER,V_LAST_NAME
VARCHER2,V_SALARY NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER,V_AB_IN NUMBER DEFAULT4,V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS+V_AB,
HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID;
COMMIT;
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER,V_LAST_NAME VARCHER2,V_SALARY NUMBER)
IS
BEGIN
INSERT INTO PLAYER(ID,LAST_NAME,SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD PLAYER;
END BB_PACK;
Which statement successfully assigns $75000000 to the
V_MAX_TEAM_SALARY variable from within a stand alone procedure?
a. V_MAX_TEAM_SALARY := 75000000;
b. BB_PACK.ADD_PLAYER. V_MAX_TEAM_SALARY := 75000000;
c. BB_PACK.V_MAX_TEAM_SALARY := 75000000; ****
d. This variable cannot be assigned a value from outside the
package.
21. Which two statements about the overloading feature of packages
are true?
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 parameters. ****
22. Examine this package:
CREATE OR REPLACE manag emps
IS
Tax_rate CONSTANT NUMBER(5,2):= . 28:,
V_id NUMBER;
PROCEDURE insert_emp(p-deptno NUMBER,p-sal NUMBER);
PROCEDURE delete_emp;
PROCEDURE update_emp:
FUNCTION calc_tax(o_sal NUMBER)
RETURN NUMBER;
END manag_emps;
/
CREATE REPLACE PACKAGE BODY manage_emps
IS
BEGIN
Update emp.
SET sal=|sal+p-raise_amt)+sal
WHERE empno= v_id;
END;
PROCEDURE insert_emp
(p_deptno NUMBER, p-sal NUMBER)
IS
BEGIN
INSERT INTO emp(empno, deptno,sal)
VALUES(v_id, p_deptno, p_sal);
END insert emp;
PROCEDURE delete_emp
IS
BEGIN
DELETE FROM emp
WHERE empno=v_id
END delete_emp;
PROCEDURE. Update_emp.
IS
V_sal NUMBER (10,2);
V_raise NUMBER(10,2);
BEGIN
SELECT Sal
INTO v_sal
FROM emp
WHERE empno=v_id;
IF v_sal<500>
V_raise:=0. 05;
ELSIF v_sal<1000>
V_raise:=0. 07;
ELSE
V_raise:=0. 04
END IF;
Update_sal(v_raise);
END update_emp.
FUNCTION calc_tax
(p_sal NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_sal* tax-rate;
END calc_tax;
END MANAGE_emp;
/
What is the name of the private procedure in the package?
a. CALC_TAX
b. INSERT_EMP
c. UPDATE_SAL ****
d. DELETE_EMP
e. UPDATE_EMP
f. MANAGE_EMP
23. Examine the code:
CREATE OR REPLACE TRIGGER update_emp
AFTER UPDATE ON emp
BEGIN
INSERT INTO audit_table (who, audited)
VALUES(USER, SYSDATE);
END;
You issue an update command on the EMP table that results in
changing ten rows. How many rows are inserted into the AUDIT_TABLE?
a. 1 ****
b. 10
c. none
d. Value equal to the number of rows in the EMP table
24. All users currently have INSERT privileges on the PLAYER table.
You want only your users to insert into this table using the
ADD_PLAYER procedure. Which two actions must you take?
a. GRANT SELECT ON ADD_PLAYER TO PUBLIC;
b. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC; ****
c. GRANT INSERT ON PLAYER TO PUBLIC;
d. GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;
e. REVOKE INSERT ON PLAYER FROM PUBLIC; ****
25. Which oracle supply package allows you to run jobs that use
defined times?
a. DBMS_JOB ****
b. DBMS_RUN
c. DBMS_PIPE
d. DBMS_SQL
26. You need to drop a table from within a stored procedure. How do
you implement this?
a. You cannot drop a table from a stored procedure.
b. Use the DROP command in the procedure to drop the table.
c. Use the DBMS_DDL packaged routines in the procedure to drop the
table.
d. Use the DBMS_SQL packaged routines in the procedure to drop the
table. ****
e. Use the DBMS_DROP packaged routines in the procedure to drop the
table.
27. Which data dictionary view gives you the names and the source
code of all the procedures you have created?
a. USER_SOURCE ****
b. USER_OBJECTS
c. USER_PROCEDURES
d. USER_SUBPROGRAMS
28. Examine this package:
CREATE OR REPLACE PACKAGE BB_PACK
IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME)
VARCHAR2(V_SALARY NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
V_PLAYER_AVG NUMBER(4,3);
PROCEDURE UPD_PLAYER_STAT
V_ID IN NUMBER, V_AB IN NUMBER DEFAULT4, V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET ADD_BAT=ADD_BATS+V_AB,
HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID;
COMMIT;
VALIDATE_PLAYER_STAT(V_ID);
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME, VARCHAR2, V_SALARY IN NUMBER);
IS
BEGIN
INSERT INTO PLAYER (ID, LAST_NAME, SALARY)
VALUES(V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK;
Which kind of packaged variable is V_MAX_TEAM_SALARY?
a. PRIVATE
b. PUBLIC ****
c. IN
d. OUT
29. Examine this trigger:
CREATE OR REPLACE TRIGGER UPD_TEAM_SALARY
AFTER INSERT OR UPDATE OR DELETE ON PLAYER
FOR EACH ROW
BEGIN
UPDATE TEAM
SET TOT_SALARY=TOT_SALARY+:NEW SALARY.
WHERE ID=:NEW:TEAM_ID;
You will be adding additional code later but for now you want the
current block to fire when updating the salary column. Which
solution should you use to verify that the user is performing an
update on the salary column?
a. ROW_UPDATE(‘SALARY’)
b. UPDATING(‘SALARY’) ****
c. CHANGING(‘SALARY’)
d. COLUMN_UPDATE(‘SALARY’)
30. Examine this package:
CREATE OR REPLACE PACKAGE discounts IS
G_ID NUMBER:=7839;
DISCOUNT_RATE NUMBER O. 00;
PROCEDURE DISPLAY_PRICE (V_PRICE NUMBER);
END DISCOUNTS;
/
CREATE OR REPLACE PACKAGE BODY discounts
IS
PROCEDURE DISPLAY_PRICE (V_PRICE_NUMBER)
IS
BEGIN DBMS_OUTPUT.PUT_LINE(‘DISCOUNTED||2_4
(V_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
is 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 the first time in a session. ****
31. Examine this package:
CREATE OR REPLACE PACKAGE BB_PACK
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME
VARCHAR2, V_SALARY NUMBER);
DB_PACK;/ CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
V_WHERE_AVG NUMBER(4,3);
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER, V_AVG IN NUMBER DEFAULT 4,V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS=AT_BATS+V_AB,
HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID;
COMMIT;
VALIDATE_PLAYER_STAT(V_ID);
END UPD_PLAYER_STAT;
PROCEDURE ADD-PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER)
IS
BEGIN
INSERT INTO PLAYER(ID, LAST_NAME, SALARY)
VALUES(V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK;
An outside procedure VALIDATE_PLAYER_STAT is executed from this
package. What will happen when this procedure changes?
a. The package specification is dropped.
b. The package specification is invalidated.
c. The package is invalid to begin with.
d. The package body is invalidated ****
