Concatinate SEQUENCE/TRIGGER names based on table names

Question

I'm using code from an answer given in a different question thread which creates sequences and triggers based on the table name.

However, I have tables which are very close to exceededing (or have already reached) the 30 character limit so I get this error:

Error report:
ORA-00972: identifier is too long
ORA-06512: at line 15
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 30 characters was specified.
*Action:   Specify at most 30 characters.

My question is, how do I concat the table names so they won't throw this error whislt still keeping some of the table name in the title? Maybe concat to something like 20 characters and "_SEQ" or "_TRIG" at the end?

Here's my code:

DECLARE
  CURSOR TABLES
  IS
    SELECT *
    FROM USER_TABLES
    WHERE 0 =
      (SELECT COUNT(*)
      FROM USER_CONSTRAINTS
      WHERE USER_CONSTRAINTS.TABLE_NAME    = USER_TABLES.TABLE_NAME
      AND USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P'
      );
BEGIN
  FOR T IN TABLES
  LOOP
    EXECUTE IMMEDIATE 'CREATE SEQUENCE '||T.TABLE_NAME||'_SEQ START WITH 1';
    EXECUTE IMMEDIATE 'UPDATE '||T.TABLE_NAME||' SET ID = '||T.TABLE_NAME||'Seq.NEXTVAL';
    EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' ADD PRIMARY KEY (ID)';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER '||T.TABLE_NAME||'_TRIG '||CHR(10) ||'BEFORE INSERT ON '||T.TABLE_NAME||' '||CHR(10) ||'FOR EACH ROW '||CHR(10) ||'BEGIN '||CHR(10) ||':NEW.ID := '||T.TABLE_NAME||'Seq.NEXTVAL; '||CHR(10) ||'END; ';
  END LOOP;
END;
/

Can't you simply use a SUBSTR to trim the table name to, say, 25 chars and then concat '_SEQ' or '_TRIG'? — Aleksej, Mar 06 '17 at 11:15

MT0 · Accepted Answer · 2017-03-06T11:28:13.837

0

This will use the table name with the vowels removed (and then take the first 25 characters of the name, if you happen to have a table name that is more than 25 characters with few vowels).

DECLARE
  CURSOR TABLES
  IS
    SELECT *
    FROM USER_TABLES
    WHERE 0 =
      (SELECT COUNT(*)
      FROM USER_CONSTRAINTS
      WHERE USER_CONSTRAINTS.TABLE_NAME    = USER_TABLES.TABLE_NAME
      AND USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P'
      );
  t_name VARCHAR2(30);
BEGIN
  FOR T IN TABLES
  LOOP
    t_name := SUBSTR( REGEXP_REPLACE( T.TABLE_NAME, '[aeiou]', NULL, 1, 0, 'i' ), 1, 25 );
    EXECUTE IMMEDIATE 'CREATE SEQUENCE '||t_name||'_SEQ START WITH 1';
    EXECUTE IMMEDIATE 'UPDATE '||T.TABLE_NAME||' SET ID = '||t_name||'_SEQ.NEXTVAL';
    EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' ADD CONSTRAINT '||t_name||'_UNQ PRIMARY KEY (ID)';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER '||t_name||'_TRIG BEFORE INSERT ON '||T.TABLE_NAME||' FOR EACH ROW BEGIN :NEW.ID := '||t_name||'_SEQ.NEXTVAL; END;';
  END LOOP;
END;
/

edited Mar 06 '17 at 11:28

answered Mar 06 '17 at 11:18

MT0

143,790
11
59
117

The 30 character table name limit has been increased to 128 (bytes) in the latest version. – BobC Mar 06 '17 at 12:47
@BobC The OP quotes a 30-character limit in their post; so it is reasonably safe to assume that they are not using Oracle 12c. – MT0 Mar 06 '17 at 13:44
I understand that. But it's useful for other people to know; save writing a lot of code or messing about when migrating from other systems – BobC Mar 06 '17 at 13:57
I'm currently using 12c and it's limited to 30 chars, is there a setting i need to modify? – Azarix Mar 06 '17 at 14:17
@Azarix See [this question](http://stackoverflow.com/a/18248529/1509264) - you need Oracle 12cR2 and to set the `COMPATIBLE` initialization parameter to `12.2`. – MT0 Mar 06 '17 at 14:32

Concatinate SEQUENCE/TRIGGER names based on table names

1 Answers1