Oracle9i program with pl/sql : 1Z0-147 valid dump

Examine this procedure:
CREATE OR REPLACE PROCEDURE INSERT_TEAM
(V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT 'AUSTIN', V_NAME in VARCHAR2)
IS
BEGIN
INSERT INTO TEAM (id, city, name)
VALUES (v_id, v_city, v_name);
COMMIT;
END
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

• A. EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');
• B. EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');
• C. EXECUTE INSERT_TEAM;
• D. EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS');
• E. EXECUTE INSERT_TEAM (3, 'LONGHORNS');

Examine this package specification:
CREATE OR REPLACE PACKAGE concat_all
IS
v_string VARCHAR2(100);
PROCEDURE combine (p_num_val NUMBER);
PROCEDURE combine (p_date_val DATE);
PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER);
END concat_all; /
Which overloaded COMBINE procedure declaration can be added to this package specification?

• A. PROCEDURE concat_all
  (p_num_val VARCHAR2, p_char_val NUMBER);
• B. PROCEDURE combine (p_no NUMBER);
• C. PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER;
• D. PROCEDURE combine;

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

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

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

There is a CUSTOMER table in a schema that has a public synonym CUSTOMER and you are granted all object privileges on it. You have a procedure PROCESS_CUSTOMER that processes customer information that is in the public synonym CUSTOMER table. You have just created a new table called CUSTOMER within your schema. Which statement is true?

• A. If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER successfully recompiles when invoked and accesses your CUSTOMER table.
• B. If the structure of your CUSTOMER table is entirely different from the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER successfully recompiles and accesses your CUSTOMER table.
• C. If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER is invalidated and gives compilation errors.
• D. Creating the table has no effect and procedure PROCESS_CUSTOMER still accesses data from public synonym CUSTOMER table.