How to automatically capitalize in PL/SQL?

2 years ago
Isabella Edwards
2 minutes

In PL/SQL, the functionality of automatic capitalization can be achieved by setting the initialization parameter NLS_UPPER to ‘TRUE’.

The NLS_UPPER parameter dictates the case conversion behavior of strings in PL/SQL. By default, the value of the NLS_UPPER parameter is ‘SECONDARY’, meaning that PL/SQL determines the case conversion rules for strings based on the current session’s language environment.

To set the NLS_UPPER parameter to ‘TRUE’, you can execute the following PL/SQL statement:

ALTER SESSION SET NLS_UPPER = 'TRUE';

After executing the above statement, PL/SQL will automatically convert strings to uppercase when performing operations such as string comparison and concatenation. For example:

DECLARE
  v_str1 VARCHAR2(10) := 'Hello';
  v_str2 VARCHAR2(10) := 'World';
BEGIN
  IF v_str1 = v_str2 THEN
    DBMS_OUTPUT.PUT_LINE('Strings are equal');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Strings are not equal');
  END IF;
END;

In the above code, if the NLS_UPPER parameter is set to ‘TRUE’, the output will be ‘Strings are not equal’ because ‘H’ and ‘W’ are characters with different cases; if the NLS_UPPER parameter is set to ‘SECONDARY’ or another value, the output will be ‘Strings are equal’ because it is case-insensitive.

It is important to note that the NLS_UPPER parameter is session-level, affecting only the PL/SQL code in the current session. If you want to set automatic uppercase globally, you must configure this parameter in all sessions.

bannerAds