Examine the following partial code:
Which statement is correct about the unnamed block of code at the end of a package body?
A. It generates an error because all the blocks of code in a package body must be named.
B. It generates an error because V_TAXRATE is a public variable that is already initialized in the package specification.
C. It acts as a package initialization block that executes once, when the package is first invoked within the user session.
D. It acts as a package initialization block that executes each time a package subprogram is invoked within the user session and refreshes the initialized variable value.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
Correct answer is C.
Tested with below code which is almost identical to the one on the screenshot:
create table tax_rates (
rate_value number,
year number
);
insert into tax_rates values (101,2009);
commit;
create or replace package calc_income is
v_taxrate number:=100;
procedure calc_tax(p_empno number);
procedure calc_sal(p_empno number);
end calc_income;
/
create or replace package body calc_income is
procedure calc_tax(p_empno number) is
begin
null;
end calc_tax;
procedure calc_sal(p_empno number) is
begin
null;
end calc_sal;
begin
select rate_value into v_taxrate
from tax_rates
where year=2009;
end calc_income;
/
set serveroutput on;
exec dbms_output.put_line(‘Current value of v_taxrate: ‘||calc_income.v_taxrate);
Current value of v_taxrate: 101
PL/SQL procedure successfully completed.
exec calc_income.v_taxrate:=102;
PL/SQL procedure successfully completed.
HR@XE > exec dbms_output.put_line(‘Current value of v_taxrate: ‘||calc_income.v_taxrate);
Current value of v_taxrate: 102
PL/SQL procedure successfully completed.
HR@XE > exec calc_income.calc_tax(10);
PL/SQL procedure successfully completed.
exec dbms_output.put_line(‘Current value of v_taxrate: ‘||calc_income.v_taxrate);
Current value of v_taxrate: 102
PL/SQL procedure successfully completed.
So, value of calc_income.v_taxrate variable initialized first time it referenced,
but not refreshed after it’s assigned manually and later package subprogram calc_tax called;
Reference:
https://docs.oracle.com/database/121/LNPLS/packages.htm#LNPLS00906
I also think that correct answer is C.
Tested with below code which is almost identical to the one on the screenshot:
create table tax_rates (
rate_value number,
year number
);
insert into tax_rates values (101,2009);
commit;
create or replace package calc_income is
v_taxrate number:=100;
procedure calc_tax(p_empno number);
procedure calc_sal(p_empno number);
end calc_income;
/
create or replace package body calc_income is
procedure calc_tax(p_empno number) is
begin
null;
end calc_tax;
procedure calc_sal(p_empno number) is
begin
null;
end calc_sal;
begin
select rate_value into v_taxrate
from tax_rates
where year=2009;
end calc_income;
/
set serveroutput on;
exec dbms_output.put_line(‘Current value of v_taxrate: ‘||calc_income.v_taxrate);
Current value of v_taxrate: 101
PL/SQL procedure successfully completed.
exec calc_income.v_taxrate:=102;
PL/SQL procedure successfully completed.
HR@XE > exec dbms_output.put_line(‘Current value of v_taxrate: ‘||calc_income.v_taxrate);
Current value of v_taxrate: 102
PL/SQL procedure successfully completed.
HR@XE > exec calc_income.calc_tax(10);
PL/SQL procedure successfully completed.
exec dbms_output.put_line(‘Current value of v_taxrate: ‘||calc_income.v_taxrate);
Current value of v_taxrate: 102
PL/SQL procedure successfully completed.
So, value of calc_income.v_taxrate variable initialized first time it referenced,
but not refreshed after it’s assigned manually and later package subprogram calc_tax called;
Reference:
https://docs.oracle.com/database/121/LNPLS/packages.htm#LNPLS00906