View Exhibit1 and examine the structure of the EMP table.
View Exhibit2 and examine the code.
EKPNOS 7845 and 7900 exist in the EMP table.
Which two calls to the RAISE_SALABY procedure in the anonymous block execute successfully? (Choose two.)
A. call in line 6
B. call in line 7
C. call in line 8
D. call in line 9
I also think correct answers are C,D.
https://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_pc.htm
“When a parameter takes a default value, it can be omitted from the actual parameter list when you call the procedure. ”
amount parameter doesn’t have a default value. So in lines when it’s ommitted (6,7), error is raised.
create or replace procedure raise_salary(emp_id in number,
amount in number,extra in number default 50)
is
begin
update emp set sal=sal+NVL(amount,0)+extra
where empno=emp_id;
end raise_salary;
/
Procedure created.
SQL> set serveroutput on;
SQL> declare
2 emp_num number(6):=7900;
3 bonus number(6);
4 merit number(4);
5 begin
6 raise_salary(7845);
7 raise_salary(emp_num,extra=>25);
8 raise_salary(7845,NULL,25);
9 raise_salary(emp_num,extra=>25,amount=>NULL);
10 end;
11 /
raise_salary(7845);
*
ERROR at line 6:
ORA-06550: line 6, column 3:
PLS-00306: wrong number or types of arguments in call to ‘RAISE_SALARY’
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
ORA-06550: line 7, column 3:
PLS-00306: wrong number or types of arguments in call to ‘RAISE_SALARY’
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
If we modify procedure a little bit, providing default value for amount, all procedure calls execute successfully.
SQL> create or replace procedure raise_salary(emp_id in number,
2 amount in number default 50,extra in number default 50)
3 is
4 begin
5 update emp set sal=sal+NVL(amount,0)+extra
6 where empno=emp_id;
7 end raise_salary;
8 /
Procedure created.
SQL>
SQL> set serveroutput on;
SQL> declare
2 emp_num number(6):=7900;
3 bonus number(6);
4 merit number(4);
5 begin
6 raise_salary(7845);
7 raise_salary(emp_num,extra=>25);
8 raise_salary(7845,NULL,25);
9 raise_salary(emp_num,extra=>25,amount=>NULL);
10 end;
11 /
PL/SQL procedure successfully completed.