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.