Online Access Free 1Z0-147 Exam Questions

You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema. You have granted the EXECUTE privilege to user A on this procedure.
When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default?

• A.User A cannot execute your procedure that has dynamic SQL.
• B.Public privileges
• C.User A's privileges
• D.Your privileges
• E.SYS privileges

You create a DML trigger. For the timing information, which are valid with a DML trigger?

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

Examine this package:
CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER); END BB_PACK; / CREATE OR REPLACE PACKAGE BODY BB_PACK IS V_PLAYER_AVG NUMBER(4,3); PROCEDURE UPD_PLAYER_STAT V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS WHERE PLAYER_ID = V_ID; COMMIT; VALIDATE_PLAYER_STAT(V_ID);
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, 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
/
Which statement will successfully assign .333 to the V_PLAYER_AVG variable from a procedure
outside the package?

• A.BB_PACK.V_PLAYER_AVG := .333;
• B.BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333;
• C.This variable cannot be assigned a value from outside of the package.
• D.V_PLAYER_AVG := .333;

Examine this code:
CREATE OR REPLACE FUNCTION calc_sal(p_salary NUMBER)
RETURN NUMBER
IS
v_raise NUMBER(4,2) DEFAULT 1.08;
BEGIN
RETURN v_raise * p_salary;
END calc_sal;
/
Which statement accurately call the stored function CALC_SAL? (Choose two)

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

Examine this code:
CREATE OR REPLACE FUNCTION gen_email_name
(p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER)
RETURN VARCHAR2
is
v_email_name VARCHAR2(19);
BEGIN
v_email_home := SUBSTR(p_first_name, 1, 1) ||
SUBSTR(p_last_name, 1, 7) ||
'@Oracle.com';
UPDATE employees
SET email = v_email_name
WHERE employee_id = p_id;
RETURN v_email_name;
END;
You run this SELECT statement:
SELECT first_name, last_name
gen_email_name(first_name, last_name, 108) EMAIL
FROM employees;
What occurs?

• A.The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in stored functions called from SQL expressions.
• B.The statement fails because functions called from SQL expressions cannot perform DML.
• C.The statement fails because the functions does not contain code to end the transaction.
• D.Employee 108 has his email name updated based on the return result of the function.
• E.The SQL statement executes successfully and control is passed to the calling environment.