1Z0-101 - Develop PL/SQL Program Units

Go back to Oracle

Example Questions

Which system privileges must you have to manually recompile a stored procedure owned by another application developer? Which part of a database trigger determines the number of times the trigger body executes? 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? Examine this trigger: CREATE OR REPLACE TRIGGER CHECK_TOT_SALARY AFTER INSERT OR UPDATE OF SALARY ON PLAYER FOR EACH ROW DECLARE V_TOT_SALS NUMBER(12, 2); BEGIN SELECT SUM(SALARY) INTO V_TOT_SAL FROM PLAYER WHER TEAM_ID=:NEW. SALARY; END; Why does this trigger fail when inserting a row into player table? Examine this code: CREATE OR REPLACE PROCEUDRE AUDIT_EMP; (P_ID IN EMP. EMPNO%TYPE) IS V_ID NUMBER; PROCEDURE LOG_EXEC IS BEGIN INSERT INTO LOG_TABLE (USER_ID,LOG_DATE) VALUES (USERS,SYSDATE); END LOG_EXEC V_NAME VARCHAR2(20) BEGIN DELECT FROM EMP WHERE EMPNO = P_ID; LOG_EXEC; SELECT ENAME,EMPNO INTO V_NAME,V_ID FROM EMP WHERE EMPNO=P_ID END AUDIT_EMP; Why does this code cause and error when compiled? The ADD_PLAYER procedure inserts rows into the player table. Which command will show this direct dependency? Which compiler directive to check the purity level of functions? Which code successfully calculates tax? You need to create a stored procedure, which 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? You need to recompile several program units you have recently modified through a PL/SQL program. Which statement is true? Examine this package CREATE OR REPLACE PACKAGE PACK_CUR IS CURSOR C1 IS SELECT PRODID FROM PRODUCT ORDER BY PRODID DESC; PROCEDURE PROC1; PROCEDURE PROC2; END PACK_CUR; / CREATE OR REPLACE PACKAGE BODY PACK_CUR IS V_ID NUMBER; PROCEDURE PROC1 IS BEGIN OPEN C1; LOOP FETCH C1 INTO V_PRODID; DBMS_OUTPUT. PUT_LINE (ROW IS :,||C1/ROWCOUNT); EXIT WHEN C1/ROWCOUNT>=3; END LOOP; END PROC1; PROCEDURE PROC2 IS BEGIN LOOP FETCH C1 TO V_PRODID DBMS_OUTPUT. PUT_LINE (ROW IS :,||C1/ROWCOUNT); EXIT WHEN C1/ROWCOUNT>=6; END LOOP; CLOSE C1; END PROC2; END PACK_CUR; / The products table has more than 1000 rows. The SQL plus server output setting is turned on in your session. You execute procedure proc1 fromsql plus with the command: EXECUTE PACK_CUR.PROC1. What is the output in your session? Which code can you use to ensure that the salary is neither increased by more than 10% at a time nor is ever decreased? Which type of package construct must be declared both within the package specification and package body? The programmer view developed a procedure ACCOUNT_TRANSACTION left organization. You were 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? 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? Which two statements about the overloading feature of packages are true? (Choose two) All users currently have the 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? (Choose two) Which statement is true? 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 coat later but for now you want the current block to fire when updated the salary column. Which solution should you use to verify that the user is performing an update on the salary column? There is a customer table in the schema that has a public synonym and you are granted all object privileges on it. You have a 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? Why do stored procedures and functions improve performance? (Chose two) You are about to change the arguments of the CALC_TEAM_AVG function. Which table can you query to determine the names of the procedures and functions that invoke the CALC_TEAM_AVG function? Examine the procedure: CREATE OR REPLACE PROCEDURE INSERT TEAM (V_ID in NUMBER,V_CITY in VARCHER2 DEFAULT 'AUSTIN'V_NAME in VARCHER2) 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) CREATE OR REPLACE PROCEDURE manage_emp(p_eno NUMBER) IS V_sal emp.sal%TYPE; V_job emp.job%TYPE; BEGIN SELECT sal,job INTO v_sal,v_job FROM emp WHERE empno=p_eno; IF(v_sal<1000)THEN DBMS_OUTPUT.PUT_LINE('Delete employees who earn less than$1000'); DELETE FROM emp WHERE empno=p_eno; ELSE DBMS_OUTPUT.PUT_LINE('Updating employee salaries.'); UPDATE emp SET sal=sal+100 WHERE empno=p_eno; END IF; END; What privileges do you need in orderto invoke this procedure? 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? 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? 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? Which compiler directive must you use to specify the purity of a packaged function when creating a package? Which script file must be executed before you can determine indirect independence's using the DEPTREE AND IDEPTREE VIEWS? Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHER2(30)) IS BEGIN INSERT INTO PLAYER(ID, LAST_NAME) VALUES(V_ID, V_LAST_NAME); COMMIT; END; Why does this command fail when executed? You want to execute a procedure from SQL Plus. However you are not sure of the argument list for this procedure. Which command will display the argument list? Examine this trigger: CREATE OR REPLACE TRIGGER UPD_PLAYER_STAT_TRIG AFTER INSERT ON PLAYER FOR EACH ROW BEGIN INSERT INTO PLAYER_BAT_STAT(PLAYER_ID, SEASON_YEAR,AT_BATS,HITS) VALUES(player_id_seq.currval, 1997, 0, 0 ); END; After creating this trigger, you test it by inserting a row into the PAYER table. You receive this error message: ORA-04091: table SCOTT.PLAYER is mutating,trigger/function may not see it. How can you avoid getting this error? Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER (V_ID IN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE V_ID=31; EXCEPTION WHEN STATS_EXIST_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE ('can't delete this player, child records exist in PLAYER_BAT_STAT table'); END; What prevents this procedure from being created successfully? Which two statements are true? (Choose two) Which data dictionary views gives you the names and the source code of all the procedures that you have created? Which Oracle supply package allows you to run jobs at use defined times? 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 COMILE_THIS; / Procedure C is a local construct to the package. What happens when this package is compiled? Which situation requires a before update statement level trigger on the table? The number of cascading triggers is limited by which data base initialization parameter? Which allows a PL/SQL user define a function? When creating a function in a SQL Plus You receive this message "Warning: function created with compilation errors". Which command can you issue to see the actual error message? You want to send a message to another session connected to the same instance. Which Oracle supplied package will you use to achieve this task? Examine this package specification: CREATE OR REPLACE PACKAGE concat_all IS V_string VARCHER2(100); PROCEDURE combine(p_num_val NUMBER); PROCEDURE combine (p_dateval DATE); PROCEDURE combine(p_char_val VARCHER2,p_num_val NUMBER); END concat_all; / Which overloaded COMBINE procedure declaration can be added to this package specification? Which procedure of the dbms_output supply package would you use to append text to the current line of the output buffer? Given a function CALCTAX: CREATE OR REPLACE FUNCTION CALCTAX(sal-number return number) IS BEGIN RETURN(sal*0.05); END; If you want to run above function from the SQL Plus prompt. Which statement is true? You have created a stored procedure DELETE_TEMP_TABLE that uses a dynamic SQL To remove a table in your scheme you have granted the execute privilege to a user A on this procedure. When user A executes the DELETE_TEMP_TABLE procedure under whose privilege the operations performed by default? Under which situation do you create a server side procedure? Which command must you issue to allow users to access the UPD_TEAM_STAT trigger on the TEAM table? What happens during the parse phase with dynamic SQL? 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?

Study Guides