Oracle 1Z0-147 Online Practice Questions and Exam Preparation

Oracle 1Z0-147 Online Questions & Answers

• Question 81:

  Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the UPD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure, will successfully invoke the UPD_BAT_STAT procedure?

  A. EXECUTE UPD_BAT_STAT(V_ID);
  B. UPD_BAT_STAT(V_ID);
  C. RUN UPD_BAT_STAT(V_ID);
  D. START UPD_BAT_STAT(V_ID);
  B. UPD_BAT_STAT(V_ID);

• Question 82:

  Consider this scenario: A procedure X references a view Y that is based on a table Z. Which two statements are true? (Choose two.)

  A. Y is a referenced object.
  B. Z is a direct dependent of X.
  C. Y is a direct dependent of X.
  D. Y is an indirect dependent of X.
  E. Y is an indirect dependent of Z.
  F. Z is an indirect dependent of Y.
  A. Y is a referenced object.
  C. Y is a direct dependent of X.

• Question 83:

  A dependent procedure or a function directly or indirectly references one or more of which four objects? (Choose four.)

  A. view
  B. sequence
  C. privilege
  D. procedure
  E. anonymous block
  F. packaged procedure or function
  A. view
  B. sequence
  D. procedure
  F. packaged procedure or function

• Question 84:

  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 tax NUMBER; BEGIN SELECT sal INTO v_sal BEGIN SELECT sal INTO v_sal FROM emp SELECT sal INTO v_sal FROM emp WHERE empno = p_no; FROM emp WHERE empno = p_no; tax := v_sal * 0.05; WHERE empno = p_no; tax := v_sal * 0.05; RETURN (tax); WHERE empno = p_no; tax := v_sal * 0.05; RETURN (tax); END; tax := v_sal * 0.05; RETURN (tax); END; 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 85:

  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_prodid 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 andgt;= 3; END LOOP; END proc1; PROCEDURE proc2 IS BEGIN LOOP FETCH c1 INTO v_prodid; DBMS_OUTPUT.PUT_LINE('Row is: ' ||c1%ROWCOUNT); EXIT WHEN c1%ROWCOUNT andgt;= 6; END LOOP; CLOSE c1; END proc2; END pack_cur; / The product table has more than 1000 rows. The SQL*Plus SERVEROUTPUT setting is turned on in your session. The product table has more than 1000 rows. The SQL*Plus SERVEROUTPUT setting is turned on in your session. The product table has more than 1000 rows. The SQL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure PROC1 from SQL*Plus with the command: The product table has more than 1000 rows. The SQL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure PROC1 from SQL*Plus with the command: EXECUTE pack_cur.proc1 You execute the procedure PROC1 from SQL*Plus with the command: EXECUTE pack_cur.proc1 You execute the procedure PROC1 from SQL*Plus with the command: EXECUTE pack_cur.proc1 What is the output in your session? EXECUTE pack_cur.proc1 What is the output in your session? What is the output in your session? A. ERROR at line 1: What is the output in your session? A. ERROR at line 1: A. ERROR at line 1: B. Row is:

  A. ERROR at line 1:
  B. Row is:
  C. Row is:
  D. Row is: Row is:
  E. Row is: 1 Row is:
  F. Row is: 1 Row is: 2
  G. Row is: 1 Row is: 2 Row is: 3
  H. Row is: 1 Row is: 2 Row is: 3 Row is: 2 Row is: 3
  I. Row is: 4
  J. Row is: 4 Row is: 5
  E. Row is: 1 Row is:
  F. Row is: 1 Row is: 2
  G. Row is: 1 Row is: 2 Row is: 3
  H. Row is: 1 Row is: 2 Row is: 3 Row is: 2 Row is: 3

• Question 86:

  Examine this code:

  

  Which statement is true?

  A. This statement creates a stored procedure named get_sal.
  B. This statement returns a raise amount based on an employee id.
  C. This statement creates a stored function named get_sal with a status of invalid.
  D. This statement creates a stored function named get_sal.
  E. This statement fails.
  E. This statement fails.

• Question 87:

  Which two tables or views track object dependencies? (Choose two).

  A. USER_DEPENDENCIES
  B. USER_IDEPTREE
  C. IDEPTREE
  D. USER_DEPTREE
  E. USER_DEPENDS
  A. USER_DEPENDENCIES
  C. IDEPTREE

• Question 88:

  Which three statements are true regarding database triggers? (Choose three.)

  A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database.
  B. A database trigger needs to be executed explicitly whenever a particular event takes place.
  C. A database trigger executes implicitly whenever a particular event takes place.
  D. A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.
  E. With a schema, triggers fire for each event for all users; with a database, triggers fire for each event for that specific user.
  A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database.
  C. A database trigger executes implicitly whenever a particular event takes place.
  D. A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.

• Question 89:

  You need to create a DML trigger. Which five pieces need to be identified? (Choose five.)

  A. table
  B. DML event
  C. trigger body
  D. package body
  E. package name
  F. trigger name
  G. system event
  H. trigger timing
  A. table
  B. DML event
  C. trigger body
  F. trigger name
  H. trigger timing

• Question 90:

  Examine this code:

  

  What does this trigger do?

  A. The trigger records an audit trail when a user makes changes to the database.
  B. The trigger marks the user as logged on to the database before an audit statement is issued.
  C. The trigger invokes the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table.
  D. The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table
  D. The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table