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 
.
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
Learning is not a spectator sport 
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