12.2: Use static expressions

View All Scripts Login to Run Script

Script Name 12.2: Use static expressions
Description Starting with Oracle Database 12c Release 2 (12.2), we can now use static expressions where previously only literal constants were allowed. This will help us write code that adapts more easily and automatically to changes and is easier to maintain. Doc: http://docs.oracle.com/database/122/LNPLS/plsql-language-fundamentals.htm#LNPLS300
Area PL/SQL General
Contributor Steven Feuerstein
Created Wednesday January 04, 2017

Statement 1
Sure, the length must be computable at compile time (that's what it means to be a static expression). But still I can reference a length defined elsewhere (and in one place). I can do this is a "regular" declaration and also in the definition of a subtype.
Maximum length of VARCHAR2 no longer hard-coded!
```
CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
IS 
   c_max_length constant integer := 32767; 
   SUBTYPE maxvarchar2 IS VARCHAR2 (c_max_length); 
END;   
```
```
Package created.
```

Statement 2

The first declaration uses the package constant for the maximum length. The second simply references the datatype. The third declaration performs a static computation to come up with the maximum length. Cool!

DECLARE 
   l_big_string1 VARCHAR2 (pkg.c_max_length) := 'So big....'; 
   l_big_String2 pkg.maxvarchar2 := 'So big via packaged subtype....'; 
   l_half_big VARCHAR2 (pkg.c_max_length / 2) := 'So big....'; 
BEGIN    
   DBMS_OUTPUT.PUT_LINE (l_big_string1); 
   DBMS_OUTPUT.PUT_LINE (l_big_string2); 
END;

So big....
So big via packaged subtype....

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database