Oracle 1Z0-147 Online Practice Questions and Exam Preparation

Oracle 1Z0-147 Online Questions & Answers

• Question 1:

  A CALL statement inside the trigger body enables you to call _____.

  A. a package
  B. a stored function
  C. a stored procedure
  D. another database trigger
  C. a stored procedure

• Question 2:

  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 3:

  You need to create a trigger to ensure that information in the EMP table is only modified during business hours, Monday to Friday from 9:00am to 5:00pm. Which types of trigger do you create? (Choose two.)

  A. row level AFTER INSERT OR UPDATE OR DELETE ON EMP
  B. row level BEFORE INSERT OR UPDATE OR DELETE ON EMP
  C. statement level AFTER INSERT OR UPDATE OR DELETE ON EMP
  D. statement level BEFORE INSERT OR UPDATE OR DELETE ON EMP
  B. row level BEFORE INSERT OR UPDATE OR DELETE ON EMP
  D. statement level BEFORE INSERT OR UPDATE OR DELETE ON EMP

• Question 4:

  Examine this code:

  

  Which statements accurately call the stored function CALC_SAL ? (Choose two.)

  A. UPDATE employees (calc_sal(salary)) SET salary = salary * calc_sal(salary);
  B. INSERT calc_sal(salary) INTO employees WHERE department_id = 60;
  C. DELETE FROM employees(calc_sal(salary)) WHERE calc_sal(salary) andgt; 1000;
  D. SELECT salary, calc_sal(salary) FROM employees WHERE department_id = 60;
  E. SELECT last_name, salary, calc_sal(salary) FROM employees ORDER BY calc_sal(salary);
  D. SELECT salary, calc_sal(salary) FROM employees WHERE department_id = 60;
  E. SELECT last_name, salary, calc_sal(salary) FROM employees ORDER BY calc_sal(salary);

• Question 5:

  Which table should you query to determine when your procedure was last compiled?

  A. USER_PROCEDURES
  B. USER_PROCS
  C. USER_OBJECTS
  D. USER_PLSQL_UNITS
  C. USER_OBJECTS

• Question 6:

  Examine this code:

  

  You execute the CREATE PROCEDURE statement above and notice that it fails. What are two reasons why it fails? (Choose two.)

  A. The syntax of the UPDATE statement is incorrect.
  B. You cannot update a table using a stored procedure.
  C. The formal parameter p_bonus_value is declared but is not used anywhere.
  D. The declaration of the formal parameter p_cutoff cannot have a DEFAULT clause.
  E. The declaration of the formal parameter p_margin cannot have a DEFAULT clause.
  F. The declaration of the formal parameter p_bonus_percent cannot have a DEFAULT clause.
  E. The declaration of the formal parameter p_margin cannot have a DEFAULT clause.
  F. The declaration of the formal parameter p_bonus_percent cannot have a DEFAULT clause.

• Question 7:

  Examine this code:

  

  You created the add_dept procedure above, and you now invoke the procedure in SQL*Plus. Which four are valid invocations? (Choose four.)

  A. EXECUTE add_dept(p_loc=andgt;2500)
  B. EXECUTE add_dept('Education', 2500)
  C. EXECUTE add_dept('2500', p_loc =andgt;2500)
  D. EXECUTE add_dept(p_name=andgt;'Education', 2500)
  E. EXECUTE add_dept(p_loc=andgt;2500, p_name=andgt;'Education')
  A. EXECUTE add_dept(p_loc=andgt;2500)
  B. EXECUTE add_dept('Education', 2500)
  C. EXECUTE add_dept('2500', p_loc =andgt;2500)
  E. EXECUTE add_dept(p_loc=andgt;2500, p_name=andgt;'Education')

• Question 8:

  Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two.)

  A. It persists across transactions within a session.
  B. It persists from session to session for the same user.
  C. It does not persist across transactions within a session.
  D. It persists from user to user when the package is invoked.
  E. It does not persist from session to session for the same user.
  A. It persists across transactions within a session.
  E. It does not persist from session to session for the same user.

• Question 9:

  Examine this code:

  

  Which statement removes the function?

  A. DROP gen_email_name;
  B. REMOVE gen_email_name;
  C. DELETE gen_email_name;
  D. TRUNCATE gen_email_name;
  E. DROP FUNCTION gen_email_name;
  F. ALTER FUNCTION gen_email_name REMOVE;
  E. DROP FUNCTION gen_email_name;

• Question 10:

  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