develop pl/sql program units

1.Which two statements about packages are true? (Choose two.)
A.Packages can be nested.
B.You can pass parameters to packages.
C.A package is loaded into memory each time it is invoked.
D.The contents of packages can be shared by many applications.
E.You can achieve information hiding by making package constructs private.
Correct:D E
2.Which code can you use to ensure that the salary is neither increased by more than 10% at a time nor is it ever decreased?
A.ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);
B.CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary nor increase by more than 10%'); END;
C.CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary nor increase by more than 10%'); END;
D.CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OF sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary nor increase by more than 10%'); END;
Correct:B
3.The procedure ADD_PRODUCT is defined within a package specification as follows: PROCEDURE ADD_PRODUCT (p_prodno NUMBER, p_prodname VARCHAR2); Which procedure declaration cannot be added to the package specification?
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_price NUMBER, p_description VARCHAR2);
Correct:D
4.The programmer who developed a procedure ACCOUNT_TRANSACTION left the organization. You are assigned the task of modifying 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 view.
D.Set the SQL*Plus environment variable TRACECODE=TRUE and run the ACCOUNT_TRANSACTION procedure.
E.Set the SQL*Plus environment variable DEPENDENCIES=TRUE and run the ACCOUNT_TRANSACTION procedure.
Correct:C
5.Which part of a database trigger determines the number of times the trigger body executes?
A.trigger type
B.trigger body
C.trigger event
D.trigger timing
Correct:A
6.Which compiler directive is used to check the purity level of functions?
A.PRAGMA PURITY_LEVEL
B.PRAGMA SERIALLY_REUSEABLE
C.PRAGMA RESTRICT_REFERENCES
D.PRAGMA RESTRICT_PURITY_LEVEL
E.PRAGMA RESTRICT_FUNCTION_REFERENCE
Correct:C
7.Which system privilege must you have to manually recompile a stored procedure owned by another application developer?
A.ALTER PROCEDURE
B.ALTER ANY PROCEDURE
C.ALTER ALL PROCEDURE
D.COMPILE ANY PROCEDURE
Correct:B
8.You are creating a stored procedure in the SQL*Plus environment. The text of the procedure is stored in a script file. You run the script file to compile the procedure. What happens if the procedure contains syntax errors?
A.Neither the source code, nor the errors are stored in the database.
B.Both the source code and the compilation errors are stored in the database.
C.Compilation errors are appended to the script file that contains the source code.
D.The source code is stored in the database, and the errors are stored in an output file.
E.Only the compilation errors are written to the database, and source code remains in the script file.
Correct:B
9.Which procedure of the dbms_output supplied package would you use to append text to the current line of the output buffer?
A.GET
B.GET_LINE
C.PUT_TEXT_LINE
D.PUT
Correct:D
10.The number of cascading triggers is limited by which database initialization parameter?
A.CASCADE_TRIGGER_CNT
B.OPEN_CURSORS
C.OPEN_TRIGGERS
D.OPEN_DB_TRIGGERS
Correct:B
11.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 / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL*Plus?
A.SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
B.EXECUTE CALC_PLAYER_AVG(31);
C.CALC_PLAYER('Ruth');
D.CALC_PLAYER_AVG(31);
E.START CALC_PLAYER_AVG(31)
Correct:A
12.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 '|| TO_CHAR(p_price*NVL(discount_rate, 1))); END display_price; BEGIN discount_rate := 0.10; END discounts; / The SQL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure DISPLAY_PRICE from SQL*Plus with the command: EXECUTE discounts.display_price (100); What is the result?
A.Discounted 10
B.Discounted 100
C.Discounted 0.00
D.Discounted NULL
E.Discounted 0.10E.Discounted 0.10
Correct:A
13.You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema. You have granted the EXECUTE privilege to user A on this procedure. When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default?
A.SYS privileges
B.your privileges
C.public privileges
D.user A's privileges
E.User A cannot execute your procedure that has dynamic SQL.
Correct:B
14.When creating procedures, arguments should be placed before which keyword?
A.IS
B.BEGIN
C.DECLARE
D.PROCEDURE
Correct:A
15.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.RECOMPILE packaged 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.
Correct:D
16.Which two statements are true? (Choose two.)
A.A function must return a value.
B.A procedure must return a value.
C.A function executes as a PL/SQL statement.
D.A function is invoked as part of an expression.
E.A procedure must have a return data type specified in its declaration.
Correct:A D
17.The add_player, upd_player_stat, and upd_pitcher_stat procedures are grouped together in a package. A variable must be shared among only these procedures. Where should you declare this variable?
A.in the package body
B.in a database trigger
C.in the package specification
D.in each procedure's DECLARE section, using the exact same name in each
Correct:A
18.There is a CUSTOMER table in a schema that has a public synonym CUSTOMER and you are granted all object privileges on it. You have a procedure PROCESS_CUSTOMER that processes customer information that is in the public synonym CUSTOMER table. You have just created a new table called CUSTOMER within your schema. Which statement is true?
A.Creating the table has no effect and procedure PROCESS_CUSTOMER still accesses data from public synonym CUSTOMER table.
B.If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER is invalidated and gives compilation errors.
C.If the structure of your CUSTOMER table is entirely different from the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER successfully recompiles and accesses your CUSTOMER table.
D.If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER successfully recompiles when invoked and accesses your CUSTOMER table.
Correct:D
19.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
Correct:D
20.Which two programming constructs can be grouped within a package? (Choose two.)
A.cursor
B.constant
C.trigger
D.sequence
E.view
Correct:A B
21.Examine this procedure: CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT 'AUSTIN', V_NAME in VARCHAR2) IS BEGIN INSERT INTO TEAM (id, city, name) VALUES (v_id, v_city, v_name); COMMIT; END; Which two statements will successfully invoke this procedure in SQL*Plus? (Choose two.)
A.EXECUTE INSERT_TEAM;
B.EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');
C.EXECUTE INSERT_TEAM(3,'AUSTIN','LONGHORNS');
D.EXECUTE INSERT_TEAM (V_ID := 3, V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');
E.EXECUTE INSERT_TEAM (3,'LONGHORNS');
Correct:B C
22.Examine this package: CREATE OR REPLACE PACKAGE compile_this IS g_value VARCHAR2(100); PROCEDURE a; PROCEDURE b; END compile_this; / CREATE OR REPLACE PACKAGE BODY compile_this IS PROCEDURE a IS BEGIN g_value := 'Hello World'; END a; PROCEDURE b IS BEGIN c; DBMS_OUTPUT.PUT_LINE ('Procedure b calling c.'); END b; PROCEDURE c IS BEGIN b; DBMS_OUTPUT.PUT_LINE ('Procedure c calling b.'); END; END compile_this; / PROCEDURE C is a local construct to the package. What happens when this package is compiled?
A.It produces the output: Procedure b calling c.
B.It produces the output:B.It produces the output: Procedure c calling b.
C.It produces a compilation error because PROCEDURE C requires a forward declaration.
D.It produces a compilation error because PROCEDURE B requires a forward declaration.
E.It produces a compilation error because identifier G_VALUE is not declared in PROCEDURE A.
Correct:C
23.Examine the trigger: CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Emp_tab FOR EACH ROW DECLARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM Emp_tab; DBMS_OUTPUT.PUT_LINE(' There are now ' || n || 'employees.'); END; This trigger results in an error after this SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499; How do you correct the error?
A.Change the trigger type to a BEFORE DELETE.
B.Take out the COUNT function because it is not allowed in a trigger.
C.Remove the DBMS_OUPUT statement because it is not allowed in a trigger.
D.Change the trigger to a statement-level trigger by removing FOR EACH ROW.
Correct:D
24.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 PROCEDURE UPD_PLAYER_STAT (V_ID IN NUMBER, V_AB 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; 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; You have made a change to the body of the BB_PACK package. What happens when the stand alone procedure VALIDATE_PLAYER_STAT references this package?
A.VALIDATE_PLAYER_STAT cannot recompile and must be recreated.
B.VALIDATE_PLAYER_STAT is not invalidated.
C.VALIDATE_PLAYER_STAT is invalidated.
D.VALIDATE_PLAYER_STAT and BB_PACK are invalidated.
Correct:B
25.Examine this package: CREATE OR REPLACE PACKAGE manage_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 (p_sal NUMBER) RETURN NUMBER; END manage_emps; / CREATE OR REPLACE PACKAGE BODY manage_emps IS PROCEDURE update_sal (p_raise_amt NUMBER) 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 THEN v_raise := .05; ELSIF v_sal < 1000 THEN v_raise := .07; ELSE v_raise := .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_emps; / How many public procedures are in the MANAGE_EMPS package?
A.one
B.two
C.three
D.four
E.five
F.none
Correct:C
26.When creating procedures, local variables should be placed after which key word?
A.IS
B.BEGIN
C.DECLARE
D.PROCEDURE
Correct:A
27.You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title. What happens when you try to update a salary value in the EMP table?
A.The trigger fires successfully.
B.The trigger fails because it needs to be a row level AFTER UPDATE trigger.
C.The trigger fails because a SELECT statement on the table being updated is not allowed.
D.The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.
Correct:C
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 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 outside procedure changes?
A.The package specification is dropped.
B.The package specification is invalidated.
C.The package is invalidated.
D.The package body is invalidated.
Correct:D
29.All users currently have the INSERT privilege to the PLAYER table. You only want your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two.)
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;
Correct:B E
30.Which two statements about packages are true? (Choose two.)
A.Both the specification and body are required components of a package.
B.The package specification is optional, but the package body is required.
C.The package specification is required, but the package body is optional.
D.The specification and body of the package are stored together in the database.
E.The specification and body of the package are stored separately in the database.
Correct:C E