Oracle 1Z0-147 Online Practice Questions and Exam Preparation

Oracle 1Z0-147 Online Questions & Answers

• Question 11:

  Which three describe a stored procedure? (Choose three.)

  A. A stored procedure is typically written in SQL.
  B. By default, a stored procedure executes with the privileges of its owner.
  C. A stored procedure has three parts: the specification, the body, and the exception handler part .
  D. A stored procedure is stored in the database and can be shared by a number of programs.
  E. A stored procedure offers some advantages over a standalone SQL statement, such as programmable functionality and compiled code.
  B. By default, a stored procedure executes with the privileges of its owner.
  D. A stored procedure is stored in the database and can be shared by a number of programs.
  E. A stored procedure offers some advantages over a standalone SQL statement, such as programmable functionality and compiled code.

• Question 12:

  All users currently have the INSERT privilege on the PLAYER table. You only want your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two.)

  A. GRANT SELECT ON ADD_PLAYER TO PUBLIC;
  B. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;
  C. GRANT INSERT ON PLAYER TO PUBLIC;
  D. GRANT EXECUTE,INSERT ON ADD_PLAYER TO PUBLIC;
  E. REVOKE INSERT ON PLAYER FROM PUBLIC;
  B. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;
  E. REVOKE INSERT ON PLAYER FROM PUBLIC;

• Question 13:

  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

• Question 14:

  Which two statements about packages are true? (Choose two.)

  A. Packages can be nested.
  B. You can pass parameters to packages.
  C. A package is loaded into memory each time it is invoked.
  D. The contents of packages can be shared by many applications.
  E. You can achieve information hiding by making package constructs private.
  D. The contents of packages can be shared by many applications.
  E. You can achieve information hiding by making package constructs private.

• Question 15:

  When creating stored procedures and functions, which construct allows you to transfer values to and from the calling environment?

  A. local variables
  B. arguments
  C. Boolean variables
  D. substitution variables
  B. arguments

• Question 16:

  Which two statements about packages are true? (Choose two.)

  A. Both the specification and body are required components of a package.
  B. The package specification is optional, but the package body is required.
  C. The package specification is required, but the package body is optional.
  D. The specification and body of the package are stored together in the database.
  E. The specification and body of the package are stored separately in the database.
  C. The package specification is required, but the package body is optional.
  E. The specification and body of the package are stored separately in the database.

• Question 17:

  Examine this package:

  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 then execute the procedure PROC2 from the SQL*Plus with the command EXECUTE pack_cur.PROC2;

  What is the output in your session from the PROC2 procedure?

  A. ERROR at line 1:
  B. Row is: Row is: Row is:
  C. Row is:1 Row is: 2 Row is: 3
  D. Row is: 4 Row is: 5 Row is: 6
  D. Row is: 4 Row is: 5 Row is: 6

• Question 18:

  Which statement about triggers is true?

  A. You use an application trigger to fire when a DELETE statement occurs.
  B. You use a database trigger to fire when an INSERT statement occurs.
  C. You use a system event trigger to fire when an UPDATE statement occurs.
  D. You use an INSTEAD OF trigger to fire when a SELECT statement occurs.
  B. You use a database trigger to fire when an INSERT statement occurs.

• Question 19:

  Examine this function:

  

  Which statement will successfully invoke this function in SQL*Plus?

  A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
  B. EXECUTE CALC_PLAYER_AVG(31);
  C. CALC_PLAYER('Ruth');
  D. CALC_PLAYER_AVG(31);
  E. START CALC_PLAYER_AVG(31)
  A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

• Question 20:

  You have the following table:

  

  You create this trigger:

  CREATE OR REPLACE TRIGGER Log_salary_increase AFTER UPDATE ON employees FOR EACH ROW WHEN (new.Salary andgt; 1000) BEGIN INSER INTO Emp_log (Emp_id, Log_date, New_salary, Action) VALUES (:new.Employee_id, SYSDATE, :new.SALary, 'NEW SAL'); END; /

  Then, you enter the following SQL statement:

  UPDATE Employees SET Salary = Salary + 1000.0 WHERE Department_id = 20;

  What are the results in the EMP_LOG table?

  

  A. B. C.
  D. No rows are inserted
  B