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.
There is no more constraints/rules for overloading in pl/sql as compare to other programming languages like Java, c, C++, etc, So Dont get confused with them
One among them is:
default parameters must follow left association.
Example: function_name(p_1,p_2,p_3 default, p_4 default) { — Right syntax
function_name(p_1,p_2 default,p_3,p_4) { — Wrong syntax
In oracle,
Example; function_name(p_1,p_2,p_3 default, p_4 default) { — Right syntax
function_name(p_1,p_2 default,p_3,p_4) { — Right syntax