PL/SQL Constants for Data Type Lengths: 12 Things Developers Will Love About Oracle Database 12c Release 2 Part 3

In part 2 of this series we saw that you can now create objects with names 128 bytes long. I know some of you are desperate to startCreatingTablesWithRidiculouslyLongNames. But before you rush out to do so, check your code. If your dev team uses sloppy coding practices, there may be some traps waiting for you…

Most applications have at least one piece of PL/SQL that selects from the data dictionary. For example:

begin select table_name into tab from user_tables where ...

Because the maximum length of a table name has been 30 bytes forever, some developers took to declaring the variable like so:

declare tab varchar2(30);

Because who needs more than 30 characters, right?

But as we saw in part 2, upgrade to 12.2 and the limit is now 128 bytes! So it’s only a matter of time before people create tables with longer names. Eventually this code will fail with:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

So what to do?

It’d be nice if you could change the maximum length of a varchar2 dynamically. So instead of combing through your PL/SQL, changing varchar2 ( 30 ) -> varchar2 ( 128 ), you could increase the size in a single place.

Fortunately, in 12.2 you can!

The new release enables you to declare a variable length using a constant. So you could create a constants package:

create or replace package constants as tab_length 
  constant pls_integer := 128; 
end constants; 
/

Then use this when declaring your variables:

declare tab varchar2( constants.tab_length );

Now if we ever increase the length of names again, you only need to make one change: the constant’s value!

Note these aren’t fully dynamic. The PL/SQL compiler has to know the value for the variable size at compile time. This means you can’t base it on the results of a query. User-defined functions are also out. So to enable the variable to hold longer strings, you need to increase the value of constants.tab_length and recompile your code.

Now you may be thinking: for something as common as object names, surely Oracle provides something stating their max length?

The good news is: we do :)

In DBMS_standard you’ll find a new constants. This includes ora_max_name_len. As the name suggests, this states the maximum length for object names. So you can change your table name variable declarations to:

declare 
  tab varchar2( ora_max_name_len ); 
begin

The best part is you can make your code future proof now! By using conditional compilation you can change your data dictionary based variable declarations to:

declare 
  $if DBMS_DB_VERSION.VER_LE_12_1 $then 
    tab varchar2( 30 ); 
  $else 
    tab varchar2( ora_max_name_len ); 
  $end

Then when you come to upgrade the tab variable will automatically have the larger limit!

You may be thinking that all sounds like a lot of work. And you’re right.

You can also make your variables 12.2 compatible now with type anchoring:

declare tab user_tables.table_name%type;

Whichever method you use, start preparing your code now. It may be a long time until you upgrade. But the more robust your code is, the easier it’ll be for you to use the new features.

Full article originally published at blogs.oracle.com on November 10, 2016.