1

From Oracle 12.2 on, table names and a lot of other identifiers change their maximum length from VARCHAR2(30) to VARCHAR2(128), as mentioned f.i. here.

We have tons of scripts, stored procedures and tables that refer to table names, column names etc, for instance for housekeeping purposes (copy tables, drop partitions, etc etc).

EDIT: They need to be changed so they will continue to work once the first longer name appears. I think this will probably take some time as people are so used to the short names, but it is safe to assume that it will happen at some stage.

The proper way would probably be to change from

CREATE OR REPLACE PROCEDURE myprocedure(pname VARCHAR2) IS 
  tabname VARCHAR2(30);
  colname VARCHAR2(30);
  idxname VARCHAR2(30);
BEGIN
  tabname := pname;
END myprocedure; 
/

to

CREATE OR REPLACE PROCEDURE myprocedure(pname VARCHAR2) IS 
  tabname USER_TABLES.TABLE_NAME%TYPE;
  colname USER_TAB_COLUMNS.COLUMN_NAME%TYPE;
  idxname USER_INDEXES.INDEX_NAME%TYPE;
BEGIN
  tabname := pname;
END myprocedure; 
/

But what to do about tables? The only idea I could find is a disabled foreign key, but that seems to be evil (besides, I can't even get the privileges right):

CREATE TABLE mytable (
   table_name REFERENCES USER_TABLES(TABLE_NAME) DISABLE
);

Any help appreciated.

wolφi
  • 8,091
  • 2
  • 35
  • 64
  • So, if I understand it correctly; you are trying to update your proc to support new variable lengths and are trying to change then in the table DDL as well? – wonderlearner May 27 '18 at 15:17
  • Yes, exactly. We could hardcode the `VARCHAR2(128)` but I am not sure if that will change again in a couple of years. – wolφi May 27 '18 at 15:48
  • So is the issue that you have application tables that store table names? I'm not sure what the FK has to do with your question. – William Robertson May 27 '18 at 17:42
  • A foreign key would copy the datatype. Just an idea, but I don't really like it. – wolφi May 27 '18 at 17:59

1 Answers1

1

Use the built-in constant ORA_MAX_NAME_LEN, see https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-predefined-data-types.html#GUID-1D28B7B6-15AE-454A-8134-F8724551AE8B.

ewramner
  • 5,810
  • 2
  • 17
  • 33
  • Thanks for pointing that out, I haven't seen this constant yet, though it was mentionen on the page I'm linking to :-(. However, it's only introduced in 12.2 (so we cannot change to code now before we migrate later this year). And I don't know how to use a PL/SQL constant in SQL: `CREATE TABLE mytable (table_name VARCHAR2(ORA_MAX_NAME_LEN BYTE));` raises `ORA-00910: specified length too long for its datatype` – wolφi May 27 '18 at 16:54
  • You can certainly use the constant in SQL, just wrap it in a function. See https://stackoverflow.com/questions/5178830/how-to-use-a-package-constant-in-sql-select-statement. I don't think you can use it in DDL like create table without resorting to execute immediate or something, though. As for not being there yet, see the example in https://medium.com/oracledevs/pl-sql-constants-for-data-type-lengths-12-things-developers-will-love-about-oracle-database-12c-cb5f4d7c2d06 and use conditional compilation for your own constant. – ewramner May 27 '18 at 18:50