Oracle 1Z0-147 Online Practice Questions and Exam Preparation

Oracle 1Z0-147 Online Questions & Answers

• Question 71:

  You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database. What type of trigger needs to be created?

  A. DML trigger
  B. System event trigger
  C. INSTEAD OF trigger
  D. Application trigger
  B. System event trigger

• Question 72:

  You create a DML trigger. For the timing information, which are valid with a DML trigger? (Choose all that apply.)

  A. DURING
  B. IN PLACE OF
  C. ON SHUTDOWN
  D. BEFORE
  E. ON STATEMENT EXECUTION
  D. BEFORE

• Question 73:

  Which statement is valid when removing procedures?

  A. Use a drop procedure statement to drop a standalone procedure.
  B. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification.
  C. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body.
  D. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.
  A. Use a drop procedure statement to drop a standalone procedure.

• Question 74:

  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
  A. trigger type

• Question 75:

  Which code successfully calculates tax?

  A. CREATE OR REPLACE PROCEDURE calctax (p_no IN NUMBER) RETURN tax IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; END;
  B. 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;
  C. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) RETURN NUMBER IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; END;
  D. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; END;
  E. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; END;
  F. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; END;
  G. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; FROM emp WHERE empno = p_no; tax := v_sal * 0.05; END;
  H. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; WHERE empno = p_no; tax := v_sal * 0.05; END;
  I. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; RETURN (tax); tax := v_sal * 0.05; END;
  J. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; RETURN (tax); END;
  B. 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;

• Question 76:

  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 the 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*Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);
  E. You need to create a SQL*Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);

• Question 77:

  Which two statements about the overloading feature of packages are true? (Choose two.)

  A. Only local or packaged subprograms 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 name number, type and order of parameters.
  D. Overloading allows different subprograms with the same name and same number or type of parameters.
  E. Overloading allows different subprograms with same name, but different in either number, type or order of parameters.
  A. Only local or packaged subprograms can be overloaded.
  E. Overloading allows different subprograms with same name, but different in either number, type or order of parameters.

• Question 78:

  Examine this package:

  

  What is the name of the private procedure in this package?

  A. CALC_TAX
  B. INSERT_EMP
  C. UPDATE_SAL
  D. DELETE_EMP
  E. UPDATE_EMP
  F. MANAGE_EMPS
  C. UPDATE_SAL

• Question 79:

  Examine this procedure:

  

  Which two statements will successfully invoke this procedure in iSQL*Plus? (Choose two.)

  A. EXECUTE UPD_BAT_STAT;
  B. EXECUTE UPD_BAT_STAT(V_AB=andgt;10, V_ID=andgt;31);
  C. EXECUTE UPD_BAT_STAT(31,'FOUR','TWO');
  D. UPD_BAT_STAT(V_AB=andgt;10, V_ID=andgt;31);
  E. RUN UPD_BAT_STAT;
  A. EXECUTE UPD_BAT_STAT;
  B. EXECUTE UPD_BAT_STAT(V_AB=andgt;10, V_ID=andgt;31);

• Question 80:

  This statement fails when executed: CREATE OR REPLACE TRIGGER CALC_TEAM_AVG AFTER INSERT ON PLAYER BEGIN INSERT INTO PLAYER_BAT_STAT (PLAYER_ID, SEASON_YEAR,AT_BATS,HITS) VALUES (:NEW.ID, 1997, 0,0); END; To which type must you convert the trigger to correct the error?

  A. row
  B. statement
  C. ORACLE FORM trigger
  D. before
  A. row