View the Exhibit and examine the structure of the SALGRADE table.
Examine the following code:
What is the outcome?
A.
It is created successfully.
B.
It gives an error because the return clause condition is invalid.
C.
It gives an error because the usage of the host variables is invalid.
D.
It gives an error because the data type of the return clause is invalid.
c
C
c
C
C
Why is the answer not C? My understanding is that bind variables can only be used within PL/SQL blocks, not subprograms.
Bind variables are not created in the DECLARATIVE section of PL/SQL block but are created in the environment. Therefore the bind variable are not declared in the function and C is the answer.
C
SQL> desc salgrade;
Name Null? Type
—————————————– ——– —————————-
GRADE NOT NULL NUMBER
LOSAL NUMBER
HISAL NUMBER
SQL> variable minsal number;
SQL> variable min_sal number;
SQL> variable max_sal number;
SQL> create or replace function sal_ok(sal number, jobgrade number)
2 RETURN boolean as
3 begin
4 select losal,hisal into :min_sal, :max_sal from salgrade where grade= jobgrade;
5 return (sal>=min_sal) and (sal<=max_sal);
6 end sal_ok;
7 /
Warning: Function created with compilation errors.
BAD Bind variable
C
c