Examine the following code:
What is the outcome?
A.
The procedure is created successfully and displays the values 20 and 30 when it is called.
B.
The procedure gives errors because the parameters should be in out mode.
C.
The procedure gives errors because the host variables cannot be referenced anywhere in the
definition of a PL/SQL stored procedure.
D.
The procedure is created successfully but does not display any values when it is called
because the host variables cannot be displayed inside the procedure.
Explanation:
c
answer is C
sqlplus output:
SQL> show errors
Errors for PROCEDURE PROC1:
LINE/COL ERROR
——– —————————————————————–
2/4 PLS-00049: bad bind variable ‘N1’
2/4 PLS-00103: Encountered the symbol “” when expecting one of the
following:
current delete exists prior
The symbol ” was inserted before “” to continue.
4/3 PLS-00049: bad bind variable ‘N1’
5/24 PLS-00049: bad bind variable ‘N1’
6/3 PLS-00049: bad bind variable ‘N2’
7/24 PLS-00049: bad bind variable ‘N2’
I found that Oracle Docs make C a not likely correct selection.
“Inside a PL/SQL block, host variables are treated as global to the
entire block and can be used anywhere a PL/SQL variable is allowed.”
See link below.
http://docs.oracle.com/cd/B28359_01/appdev.111/b28427/pc_07pls.htm#i2336
BUT, by eliminating C, I now don’t see a correct answer to this question.
This article indicates that “host variables are treated as global to the
entire block and can be used anywhere a PL/SQL variable is allowed.”
Host vairables can be used as PLSQL variables, but cannot be used as an input into function or procedure.
Try to run the procedure in example without definition – it will succeed:
set serveroutput on
variable n1 number
variable n2 number
begin
:n1 := 20;
dbms_output.put_line(:n1);
:n2 := 20;
dbms_output.put_line(:n2);
end;
/
All in all, answer is C.
C
C
C
C
i think A is the right answer because host variable used here as a parameter.
??