What types of expressions are allowed for declaring a scalar variable’s size in a PL/SQL package?
Here is a db<>fiddle showing size expressions other than numeric literals where some produce errors and others don’t. Below is a snippet.
create or replace package p as
vc varchar2(length('four'));
end;
/
Package created.
but
create or replace package p as
vc varchar2(greatest(3,4)); --PLS-00491: numeric literal required
end;
/
ORA-24344: success with compilation error
The previous example with length() shows that expressions other than numeric literal are allowed.
Both length() and greatest() are SQL functions. Why does one function invoke PLS-00491 and the other does not?
Note: The syntax for specifying the size of PL/SQL datatype does not seem to be provided in the documentation Scalar Variable Declaration.
Using Oracle 19c Enterprise Edition.
Thanks in advance.
>Solution :
I suspect it’s because the length() function is deterministic – the length in characters of the string literal ‘four’ is always 4 – while greatest() is not. As shown in the documentation, it is affected by NLS settings; so while with my default English/binary settings I see this:
select greatest('á', 'b') from dual;
GREATEST('Á','B')
-----------------
á
if I change the settings I get a different result:
alter session set nls_comp = 'linguistic';
alter session set nls_language = 'spanish';
select greatest('á', 'b') from dual;
GREATEST('Á','B')
-----------------
b
While comparing numbers as you are isn’t affected by NLS settings, it’s the same function, so it’s still not deterministic.
So, in theory at least, the size of your PL/SQL variable declared based on greatest() could be different depending on your session settings, which isn’t tenable.