I’ve been working on PL/SQL topics and wanted to share with you a new Oracle12 feature.
Oracle12 now allows for much longer varchar2, nvarchar2, and raw fields…in both the database (tables) and in PL/SQL. The new 32K size not only lines up the database side of things with the programming side but also allows for much larger objects to be stored directly in the database with ease.
The standard sizes are as follows:
Varchar2 – 4K for tables, 32K for PL/SQL
Char – 2K for tables, 32K for PL/SQL
Nvarchar2 – 4K for tables, 32K for PL/SQL
Raw – 2K for tables, and 4K for PL/SQL.
New Extended sizes available in Oracle12:
Varchar2 – 32K
Nvarchar2 – 32K
Raw – 32K
Oracle12 has a new init.ora setting: MAX_STRING_SIZE. When this is set to ‘STANDARD’, you get the Oracle11 and before sizes. When it is set to ‘EXTENDED’, you can then use the larger sizes. This mostly lines up the database side with the PL/SQL side but allows you to store much larger items now in the database.
There are a couple of caveats to keep in mind.
There is no going back! Once this setting is set to EXTENDED, you cannot change it back to STANDARD. How would Oracle know if you used the feature or not?
Max index column size is 6,400bytes.
Some other sizes to reference, since I’m talking about sizes here are:
- Program name/variable name – 30 positions
- Sub-query nesting – 254 deep
- PL/SQL block nesting 255
- Nested records – 32 deep
- Max PL/SQL program size – 64K
- Max trigger size – 32K
How large is your PL/SQL object? Select * from user_object_size where name = ‘<pgm name>’;
I hope this technique helps you in your day-to-day use of Rapid SQL.