Tags

,

ORACLE 12.2 enables us to name our objects with names up to 128 byte long. Even if this has been something we were looking forward to for a very long time there are still many things to consider before deciding to cross the old 30 byte border.

  • Are there places in your PL/SQL code where you placed some VARCHAR2(30) variables to hold oracle object names?
  • What is the size of the columns in your log tables that you use to store the name of the object you are logging information about?
  • What about the variables you use in your PL/SQL code to put your dynamic SQL statements together? Are those variables wide enough?

How about this one:

  • If you are using some dynamic SQL that evaluates a dynamic select list using SYS.DBMS_SQL.DESCRIBE_COLUMNS … then this is a point where you will have to do some changes…

EXAMPLE: Problems using DBMS_SQL

CREATE TABLE Table_with_a_name_that_is_so_much_longer_than_usual (
   column_with_a_very_long_name_as_it_is_possible_in_other_databases NUMBER(5)
  ,another_column_with_also_a_very_very_long_name                    VARCHAR2(100)
);

Table TABLE_WITH_A_NAME_THAT_IS_SO_MUCH_LONGER_THAN_USUAL created.

DECLARE
   l_handle         PLS_INTEGER := sys.dbms_sql.open_cursor();
   l_column_counter NUMBER;
   l_describe_table_to_describe_a_very_long_table sys.dbms_sql.desc_tab;
BEGIN
   sys.dbms_sql.parse(c             => l_handle
                     ,statement     => 'SELECT * FROM Table_with_a_name_that_is_so_much_longer_than_usual'
                     ,language_flag => sys.dbms_sql.NATIVE);

   sys.dbms_sql.describe_columns(c       => l_handle
                                ,col_cnt => l_column_counter 
                                ,desc_t  => l_describe_table_to_describe_a_very_long_table);
 
   FOR i IN 1..l_column_counter
   LOOP
      sys.dbms_output.put_line('Column Name : ' || l_describe_table_to_describe_a_very_long_table(i).col_name);
   END LOOP;
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 10
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
 occurred. For example, this error occurs if an attempt is made to
 assign the value NULL to a variable declared NOT NULL, or if an
 attempt is made to assign an integer larger than 99 to a variable
 declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
 that values do not violate constraints.

The problem is, that desc_rec in DBMS_SQL is still limited to 30 character length for the column names of the result.

To support long names ORACLE has implemented a new function and new data structures in DBMS_SQL.  

EXAMPLE: Solution

DECLARE
   l_handle         PLS_INTEGER := sys.dbms_sql.open_cursor();
   l_column_counter NUMBER;
   l_describe_table_to_describe_a_very_long_table sys.dbms_sql.desc_tab2;
BEGIN
   sys.dbms_sql.parse(c             => l_handle
                     ,statement     => 'SELECT * FROM Table_with_a_name_that_is_so_much_longer_than_usual'
                     ,language_flag => sys.dbms_sql.NATIVE);

   sys.dbms_sql.describe_columns2(c       => l_handle
                                 ,col_cnt => l_column_counter 
                                 ,desc_t  => l_describe_table_to_describe_a_very_long_table);
 
   FOR i IN 1..l_column_counter
   LOOP
      sys.dbms_output.put_line('Column Name : ' || l_describe_table_to_describe_a_very_long_table(i).col_name);
   END LOOP;
END;
/

Column Name : COLUMN_WITH_A_VERY_LONG_NAME_AS_IT_IS_POSSIBLE_IN_OTHER_DATABASES
Column Name : ANOTHER_COLUMN_WITH_ALSO_A_VERY_VERY_LONG_NAME

Conclusion

Having the possibility to give decent names to objects – e.g. naming an index addresses_employees_usages_fk_index – instead of searching for abbreviation that are at least a little bit meaningfull is a nice thing. But before starting to cross the 30 byte border you have to deeply inspect your code.