Oracle PL/SQL Scalar Variable Size Syntax Ambiguity

Question

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.

You'd probably have to ask Oracle; but as a guess, `length()` is deterministic, and `greatest()` isn't? (Obviously the result of `greatest(3,4)` isn't going to change; but that function applies to strings too, and then NLS settings would affect the outcome, so overall it can't be deterministic.) — Alex Poole, Jan 30 '22 at 16:39
Genius observation, I never thougt that something like this would ever be possible `varchar2(8000/(1+1))`. BTW your second example sounds like [conditional compilation](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-language-fundamentals.html#GUID-DC23291F-006D-49C4-87DA-6B31F19F162A) `$IF 3 > 4 $THEN v_vc varchar2(3) := '3'; $ELSE v_vc varchar2(4) := '4'; $END` — Marmite Bomber, Jan 30 '22 at 17:54

score 4 · Accepted Answer · answered Jan 30 '22 at 16:53

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

db<>fiddle demo

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.

Oracle PL/SQL Scalar Variable Size Syntax Ambiguity

1 Answers1