Get real 1Z0-147 exam questions for better preparation

Examine this code:
CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston')
IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; /
Which three are valid calls to the add_dep procedure? (Choose three)

• A. add_dept('Accounting');
• B. add_dept(p_location=>'New York');
• C. add_dept;
• D. add_dept(, 'New York');

Examine this code:
CREATE OR REPLACE PACKAGE metric_converter
IS
c_height CONSTRAINT NUMBER := 2.54;
c_weight CONSTRAINT NUMBER := .454;
FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER;
FUNCTION calc_weight (p_weight_in_pounds NUMBER)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY metric_converter
IS
FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_height_in_inches * c_height;
END calc_height;
FUNCTION calc_weight (p_weight_in_pounds NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_weight_in_pounds * c_weight
END calc_weight
END metric_converter;
/
CREATE OR REPLACE FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_height_in_inches * metric_converter.c_height;
END calc_height;
/
Which statement is true?

• A. If you remove the package body, then the package specification is removed.
• B. If you remove the package body, then the package specification and the stand alone stored function CALC_HEIGHT are removed.
• C. The stand alone function CALC_HEIGHT cannot be created because its name is used in a packaged function.
• D. If you remove the package specification, then the package body is removed.
• E. If you remove the package specification, then the package body and the stand alone stored function CALC_HEIGHT are removed.
• F. If you remove the stand alone stored function CALC_HEIGHT, then the METRIC_CONVERTER package body and the package specification are removed.

What is true about stored procedures?

• A. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.
• B. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.
• C. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.
• D. A stored procedure must have at least one executable statement in the procedure body.

Examine this procedure:
CREATE OR REPLACE PROCEDURE DELETE_PLAYER (V_ID IN NUMBER) IS BEGIN
DELETE FROM PLAYER
WHERE ID = V_ID;
EXCEPTION
WHEN STATS_EXITS_EXCEPTION
THEN DBMS_OUTPUT.PUT_LINE
('Cannot delete this player, child records exist in PLAYER_BAT_STAT table');
END;
What prevents this procedure from being created successfully?

• A. The STATS_EXIST_EXCEPTION has not been declared as a number.
• B. A comma has been left after the STATS_EXIST_EXCEPTION exception.
• C. Only predefined exceptions are allowed in the EXCEPTION section.
• D. The STATS_EXIST_EXCEPTION has not been declared as an exception.

Which three are true regarding error propagation? (Choose three)

• A. An exception cannot propagate across remote procedure calls.
• B. An exception raised inside an exception handler immediately propagates to the enclosing block.
• C. An exception raised inside a declaration immediately propagates to the current block.
• D. The use of the RAISE; statement in an exception handler reraises the current exception.