CODEWITHSUNDEEP
oracle-databaseplsqloracle12cname-length
Bauerhof
Bauerhof

Reputation: 165

enforcing functions name length in Oracle 12 to meet SQL-92 standard

in Oracle 12 is there a way to enforce the max length of function name (and constant-names) to be 30 chars only? (as in oracle 11g and priors)

Upvotes: 1

Views: 623

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

Oracle function name length can be restricted through the COMPATIBLE parameter or through an event trigger.

Setting the COMPATIBLE parameter to a value lower than 12.2.0.0.0 will prevent any identifiers more than 30 bytes. However, this is a powerful parameter that affects a lot of functionality and cannot be easily changed.

An easier, but not as comprehensive approach, would be to create an event trigger that blocks any DDL that creates an object with a long object name. For example, the below trigger prevents one schema from creating any functions with long names.

create or replace trigger enforce_name_length
--Change "schema" to "database" to protect entire database.
--But be careful and test thoroughly before you block DDL on an entire database.
before create on schema
begin
    if ora_dict_obj_type = 'FUNCTION' and lengthb(ora_dict_obj_name) >= 31 then
        raise_application_error(-20000, 'Cannot create ' || ora_dict_obj_owner || '."' 
            || ora_dict_obj_name || '", the object name is longer than 30 bytes.');
    end if;
end;
/

Trying to create a function like this:

create or replace function large_function_name012345789012 return number is begin return 1; end;
/

Will generate this error:

ORA-04088: error during execution of trigger 'JHELLER.ENFORCE_NAME_LENGTH'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Cannot create JHELLER."LARGE_FUNCTION_NAME012345789012", the object name is longer than 30 bytes.
ORA-06512: at line 3

How badly do you need to restrict the names within a function? I can think of a somewhat-difficult way to restrict all identifiers of a function to 30 bytes, but it would be even more difficult to restrict only constant identifiers to 30 bytes.

Upvotes: 0

Related Questions