Varcher2vsalary number is begin insert into playerid

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 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) 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_PACKAGE. What happens when the stand alone procedure VALIDATE_PLAYER_STAT references this package? A. VALIDATE_PLAYER_STAT can't 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 is invalidated. Answer: B QUESTION 67: Examine this trigger: - The Power of Knowing 1Z0-101 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? A. Drop the foreign key contraint on the PLAYER_ID column of the PLAYER_BAT_STAT table. B. Drop the primary key contraint on the PLAYER_ID column of the PLAYER_BAT_STAT table. C. Drop the primary key constraint on the ID column of the PLAYER table. D. The code of the trigger is invalid. Drop and recreate the trigger. Answer: A QUESTION 68: CREATE OR REPLACE PACKAGE manage_emps IS Tax_rate CONSTRAINT NUMBER(5,2):=. 28; v_id NUMBER;PROCEDURE insert_emp(p_dept NO NUMBER, p_sal NUMBER); PROCEDURE delete_emp; PROCEDURE update_emp; FUNCTION calc_text(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_AMP)+SAL WHERE EMPNO=v_id; END; PROCEDURE insert_emp (p_deptno NUMBER,p_sal NUMBER) - The Power of Knowing 1Z0-101 IS BEGIN INSERT INTO EMP(EMPNO,DEPTNO,SAL) VALUES(v_id,p_deptno,p_sal); INERT INTO EMP; PROCEURE delete_emp IS BEGIN DELETE FROM EMP WHERE EMPNO=v_id; END delete_emp; PROCEDURE audit_emp; IS V_sal NUMBER(10,2); V_raise NUMBER(10,2); IS SELECT SAL INTO v_sal FROM EMP WHERE EMPNO=v_id; IF v_sal<500 THEN v_raise:=. 05;ELSE 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 there in the MANAGE_EMPS package? A. 1. B. 2. C. 3. D. 4. E. 5. F. None. Answer: C QUESTION 69: - The Power of Knowing 1Z0-101 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? A. You need to execute the command CALCTAX(1000); B. You need to execute the command EXECUTE FUNCTION CALCTAX; C. You need to create a SQL Plus environment variable X and issue the command :X:=CALCTAX(1000); D. You need to create a SQL Plus environment variable X and issue the command execute :X:=CALCTAX; E. You need to create a SQL Plua environment variable X and issue the command execute :X:=CALCTAX(1000); Answer: E QUESTION 70: 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? A. DESCRIBE. B. SHOWLIST. C. SHOW ARG_LIST. D. SHOW PROCEDURE. Answer: A QUESTION 71: 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 - The Power of Knowing 1Z0-101 PLAYER_BAT_STAT table'); END; What prevents this procedure from being created successfully? A. A comma has been left off after the STATA_EXIST_EXCEPTION exception. B. The STATS_EXIST+EXCEPTION has not been declared as a number. C. The STATSEXIST_EXCEPTION has not been declared as an exception. D. Only predefined exceptions are allowed in the EXCEPTION section. Answer: C QUESTION 72: 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 error? 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. The only compilation errors are...
View Full Document

This document was uploaded on 03/08/2014.

Ask a homework question - tutors are online