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;
B.
EXECUTE INSERT_TEAM(3, V_NAME=>’LONGHORNS’, V_CITY=>’AUSTIN’);
C.
EXECUTE INSERT_TEAM(3, ‘AUSTIN’,’LONGHORNS’);
D.
EXECUTE INSERT_TEAM (V_ID := V_NAME := ‘LONGHORNS’, V_CITY := ‘AUSTIN’);
E.
EXECUTE INSERT_TEAM (3, ‘LONGHORNS’);
Explanation:
B: This statement correctly uses mixed notation. The following example uses named notation for
passing actual parameters to a procedure:
EXECUTE my_procedure (p_deptcode=>10, p_empid => 1);
C: This statement correctly uses positional notation
Incorrect Answers:
A: This statement will fail because parameters are not specified the for V_ID and V_NAMME
arguments and there are no default values specified in the procedure. Formal parameters that do
not have any default values assigned in the parameter list must be providedthe actual values
when the procedure is invoked. When invoking a procedure, you cannot omit the actual value of a
formal parameter that does not have a default value.
D: This is incorrect syntax for named notation. The following example demonstrates using named
notation for passing actual parameters to the above created procedure my_procedure:EXECUTE
my_procedure (p_deptcode=>10, p_empid => 1);
E: This statement will fail because a parameter is not supplied to the V_Name argument and a
default value is not specified in the procedure.