Pass with professional 1Z0-147 actual quiz materials

Under which two circumstances do you design database triggers? (Choose two)

• A. To duplicate the functionality of other triggers.
• B. To replicate built-in constraints in the Oracle server such as primary key and foreign key.
• C. To guarantee that when a specific operation is performed, related actions are performed.
• D. For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement.

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

Which two statements about object dependencies are accurate? (Choose two.)

• A. When referencing a package procedure or function from a stand-alone procedure or function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct remains valid.
• B. When referencing a package procedure or function from a stand-alone procedure or function, if the package specification changes, the package body remains valid but the stand-alone procedure becomes invalid
• C. When referencing a package procedure or function from a stand-alone procedure or function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct becomes invalid
• D. When referencing a package procedure or function from a stand-alone procedure or function, If the package specification changes, the stand-alone procedure referencing a package construct as well as the package body become invalid

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

Examine this package:
CREATE OR REPLACE PACKAGE manage_emps IS tax_rate CONSTANT NUMBER(5,2) := .28; v_id NUMBER; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER); PROCEDURE delete_emp; PROCEDURE update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER; END manage_emps; / CREATE OR REPLACE PACKAGE BODY manage_emps IS PROCEDURE update_sal (p_raise_amt NUMBER) IS BEGIN UPDATE emp SET sal = (sal * p_raise_emt) + sal WHERE empno = v_id; END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS BEGIN INSERT INTO emp(empno, deptno, sal) VALYES(v_id, p_depntno, p_sal); END insert_emp; PROCEDURE delete_emp IS BEGIN DELETE FROM emp WHERE empno = v_id; END delete_emp; PROCEDURE update_emp IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = v_id;
IF v_sal < 500 THEN
v_raise := .05;
ELSIP v_sal < 1000 THEN
v_raise := .07;
ELSE
v_raise := .04;
END IF;
update_sal(v_raise);
END update_emp;
FUNCTION calc_tax
(p_sal NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_sal * tax_rate;
END calc_tax;
END manage_emps;
/
What is the name of the private procedure in this package?

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