It’s all downhill past 30

Posted by

Yes, it sounds like a lamentation of the aging process, and from my own experience, it is definitely true that the wheels started falling off for my knees, back and plenty of other body parts once I turned 30. But that is perhaps a legacy of too much alcohol, not eating well in my youth and failing to stretch rather than any particular chronological milestone Smile.

But this post is not about that. This one is about the magical 30 character limit on identifiers that we finally lifted in version 12c. For static SQL, this is obviously a no impact change – you either define your tables and columns with longer names or you don’t, and the static SQL you write reflects that position.

But dynamic SQL is a different proposition, in particular, this discovery I made with DBMS_SQL recently. DBMS_SQL has a number of package data types that reflect the maximum identifier length in the database, for example:


  type desc_rec is record (
        col_type            binary_integer := 0,
        col_max_len         binary_integer := 0,
        col_name            varchar2(32)   := '',
        col_name_len        binary_integer := 0,
        col_schema_name     varchar2(32)   := '',
        col_schema_name_len binary_integer := 0,
        col_precision       binary_integer := 0,
        col_scale           binary_integer := 0,
        col_charsetid       binary_integer := 0,
        col_charsetform     binary_integer := 0,
        col_null_ok         boolean        := TRUE);

For dynamic SQL where we need to determine the column names dynamically with an arbitrary SQL statement, DBMS_SQL uses this data type in it’s API calls. The example below lists out some user names from the database and dynamically derives the column names (even though we obviously know them in advance for this simple example)


SQL> set serverout on format wrapped
SQL> declare
  2    c clob := 'select username, created from dba_users';
  3    l_cur     int := dbms_sql.open_cursor;
  4    l_val     varchar2(4000);
  5    l_status  int;
  6    l_desc    dbms_sql.desc_tab;
  7    l_cnt     int;
  8    l_longcol int := 0;
  9  begin
 10    dbms_sql.parse(l_cur,c,dbms_sql.native );
 11    dbms_sql.describe_columns(l_cur,l_cnt,l_desc);
 12    for i in 1 .. l_cnt loop
 13      dbms_sql.define_column(l_cur,i,l_val,4000);
 14      l_longcol := greatest(l_longcol,length(l_desc(i).col_name));
 15    end loop;
 16    l_status := dbms_sql.execute(l_cur);
 17    while dbms_sql.fetch_rows(l_cur) > 0
 18    loop
 19      for i in 1 .. l_cnt
 20      loop
 21        dbms_sql.column_value(l_cur, i,l_val);
 22        dbms_output.put_line(
 23          rpad(l_desc(i).col_name,l_longcol)||': '||l_val );
 24      end loop;
 25      dbms_output.new_line;
 26    end loop;
 27  exception
 28    when others then
 29      dbms_output.put_line(dbms_utility.format_error_backtrace);
 30      raise;
 31  end;
 32  /
USERNAME: SYS
CREATED : 08-MAR-17

USERNAME: SYSTEM
CREATED : 08-MAR-17

USERNAME: XS$NULL
CREATED : 08-MAR-17

USERNAME: OJVMSYS
CREATED : 08-MAR-17

USERNAME: SYSADMIN
CREATED : 19-JUL-18

It would seem a trivial change to increase the size limit as the version increases from 11g to 12c, but DBMS_SQL is caught between a rock and a hard place here. If you do change the limit, then you risk issues with backward compatibility for customers. If you do not change the limit, then you run into problems like the demo below:


SQL> create table t ( using_my_new_cool_long_column_names_in_12c )
  2  as select 1 from dual;

Table created.

SQL>
SQL>
SQL> set serverout on format wrapped
SQL> declare
  2    c clob := 'select * from t';
  3    l_cur     int := dbms_sql.open_cursor;
  4    l_val     varchar2(4000);
  5    l_status  int;
  6    l_desc    dbms_sql.desc_tab;
  7    l_cnt     int;
  8    l_longcol int := 0;
  9  begin
 10    dbms_sql.parse(l_cur,c,dbms_sql.native );
 11    dbms_sql.describe_columns(l_cur,l_cnt,l_desc);
 12    for i in 1 .. l_cnt loop
 13      dbms_sql.define_column(l_cur,i,l_val,4000);
 14      l_longcol := greatest(l_longcol,length(l_desc(i).col_name));
 15    end loop;
 16    l_status := dbms_sql.execute(l_cur);
 17    while dbms_sql.fetch_rows(l_cur) > 0
 18    loop
 19      for i in 1 .. l_cnt
 20      loop
 21        dbms_sql.column_value(l_cur, i,l_val);
 22        dbms_output.put_line(
 23          rpad(l_desc(i).col_name,l_longcol)||': '||l_val );
 24      end loop;
 25      dbms_output.new_line;
 26    end loop;
 27  exception
 28    when others then
 29      dbms_output.put_line(dbms_utility.format_error_backtrace);
 30      raise;
 31  end;
 32  /
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 11

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 30
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 11

The solution here is a simple one. The existing definitions in DBMS_SQL were left unchanged to preserve that backward compatibility, and additional structures were added to handle longer column names. We need simply alter our code to use the new “desc_tab2” data type and it’s accompanying “describe_columns2” call.


SQL> declare
  2    c clob := 'select * from t';
  3    l_cur     int := dbms_sql.open_cursor;
  4    l_val     varchar2(4000);
  5    l_status  int;
  6    l_desc    dbms_sql.desc_tab2;
  7    l_cnt     int;
  8    l_longcol int := 0;
  9  begin
 10    dbms_sql.parse(l_cur,c,dbms_sql.native );
 11    dbms_sql.describe_columns2(l_cur,l_cnt,l_desc);
 12    for i in 1 .. l_cnt loop
 13      dbms_sql.define_column(l_cur,i,l_val,4000);
 14      l_longcol := greatest(l_longcol,length(l_desc(i).col_name));
 15    end loop;
 16    l_status := dbms_sql.execute(l_cur);
 17    while dbms_sql.fetch_rows(l_cur) > 0
 18    loop
 19      for i in 1 .. l_cnt
 20      loop
 21        dbms_sql.column_value(l_cur, i,l_val);
 22        dbms_output.put_line(
 23          rpad(l_desc(i).col_name,l_longcol)||': '||l_val );
 24      end loop;
 25      dbms_output.new_line;
 26    end loop;
 27  exception
 28    when others then
 29      dbms_output.put_line(dbms_utility.format_error_backtrace);
 30      raise;
 31  end;
 32  /
USING_MY_NEW_COOL_LONG_COLUMN_NAMES_IN_12C: 1


PL/SQL procedure successfully completed.

In fact, a column name could always be more than 32 characters because an un-aliased column expression can yield column names of almost arbitrary length, so these additions were made several releases ago, but 12c had brought that issue to the fore because even “normal” column names can now exceed the previous limit.

Now if I only I could add a “2” to the end of my aging body to get to handle years up to 128 just like the column names in 12c Smile

One comment

  1. Hello Connor,

    Your post just triggered a new discovery for me:
    Starting with 12.2, DBMS_SQL also has now a new pair” DESC_REC4 and DESC_TAB4,
    where COL_SCHEMA_NAME and COL_TYPE_NAME are defined as DBMS_ID.

    But, there is no corresponding DESCRIBE_COLUMNS4 !
    Instead, DESCRIBE_COLUMNS3 has two overloads, for DESC_TAB3 and DESC_TAB4.

    Wish you HAPPY HOLIDAYS and HAPPY NEW YEAR 2019,
    and long live until 128 !

    Looking forward to meet you in January 🙂

    Best Regards,
    Iudith

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.