1Z0-101 - Develop PL/SQL Program Units
Go back to Oracle
Which system privileges must you have to manually recompile a stored procedure owned by another application developer?
ALTER ANY PROCEDURE
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?
For each row trigger on the EMP table.
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?
You can't read data from a table that is being affected by the same trigger.
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 V_NAME variable should be declared before declaring the LOG_EXEC procedure.
The ADD_PLAYER procedure inserts rows into the player table. Which command will show this direct dependency?
SELECT*FROM USER_DEPENDENCIES WHERE REFFERENCE_NAME= 'PLAYER';
Which compiler directive to check the purity level of functions?
Which code successfully calculates tax?
CREATE OR REPLACE FUNCTION calctax(p_no NUMBER) RETURN NUMBER IS V_sal NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno =p_no; RETURN(v_sal* 0. 05); END;
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?
Use DBMS_SQL packaged routines in the procedure to delete the rows.
You need to recompile several program units you have recently modified through a PL/SQL program. Which statement is true?
You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to recompile the program units.
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?
Row is:1 Row is:2 Row is:3
Which code can you use to ensure that the salary is neither increased by more than 10% at a time nor is ever decreased?
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;
Which type of package construct must be declared both within the package specification and package body?
Public procedures and functions.
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?
Query the USER_DEPENDENCIES data dictionary views.
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?
The value of DISCOUNT_RATE is set to 0. 10 when the package is invoked for first time in a session.
Which two statements about the overloading feature of packages are true? (Choose two)
Only local or packaged sub programs can be overloaded.
Overloading allows different subprograms with the same name but different in either number or type or order of parameter.
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)
GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;
REVOKE INSERT ON PLAYER FROM PUBLIC;
Which statement is true?
Server side procedures are stored in script files on the server.
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?
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.
Why do stored procedures and functions improve performance? (Chose two)
They postpone PL/SQL parsing until run time.
They reduce the number of calls to the database and decrease network traffic by bundling commands.
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)
EXECUTE INSERT_TEAM (3, "AUSTIN", "LONG HORNS")
EXECUTE INSERT_TEAM (V_ID := 3, V_NAME:="LONG HORNS", VCITY:="AUSTIN");
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?
EXECUTE privilege on the 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?
SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
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?
When declaring arguments length is not allowed.
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?
Drop the foreign key contraint on the PLAYER_ID column of the PLAYER_BAT_STAT table.
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?
The STATSEXIST_EXCEPTION has not been declared as an exception.
Which two statements are true? (Choose two)
A function must return a value.
A function is invoked as part of an expression.
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?
It produces a compilation error because procedure c requires a forward declaration.
Which situation requires a before update statement level trigger on the table?
When you need to make sure that user making modifications to the table as necessary privileges.
The number of cascading triggers is limited by which data base initialization parameter?
Which allows a PL/SQL user define a function?
HAVING clause of the SELECT COMMAND.
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 need to create a SQL Plua environment variable X and issue the command execute :X:=CALCTAX(1000);
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?
When the procedure needs to be used by many users accessing the same schema objects on a local database.
Which command must you issue to allow users to access the UPD_TEAM_STAT trigger on the TEAM table?
GRANT SELECT, INSERT, UPDATE, DELETE ON TEAM TO PUBLIC;
What happens during the parse phase with dynamic SQL?
The validity of the SQL statement is established.
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?
In the package body.