CANONICALIZE procedure - Canonicalize a string

The CANONICALIZE procedure performs various operations on an input string.

The CANONICALIZE procedure performs the following operations on an input string:

If the string is not double-quoted, verifies that it uses the characters of a legal identifier. If not, an exception is thrown. If the string is double-quoted, all characters are allowed.
If the string is not double-quoted and does not contain periods, puts all alphabetic characters into uppercase and eliminates leading and trailing spaces.
If the string is double-quoted and does not contain periods, strips off the double quotes.
If the string contains periods and no portion of the string is double-quoted, puts each portion of the string into uppercase and encloses each portion in double quotes.
If the string contains periods and portions of the string are double-quoted, returns the double-quoted portions unchanged, including the double quotes, and returns the non-double-quoted portions in uppercase and enclosed in double quotes.

Syntax

Parameters

name: An input argument of type VARCHAR(1024) that specifies the string to be canonicalized.
canon_name: An output argument of type VARCHAR(1024) that returns the canonicalized string.
canon_len: An input argument of type INTEGER that specifies the number of bytes in name to canonicalize starting from the first character.

Authorization

EXECUTE privilege on the DBMS_UTILITY module.

Examples

Example 1: The following procedure applies the CANONICALIZE procedure on its input parameter and displays the results.

SET SERVEROUTPUT ON@

CREATE OR REPLACE PROCEDURE canonicalize(
  IN p_name VARCHAR(4096), 
  IN p_length INTEGER DEFAULT 30)
BEGIN
  DECLARE v_canon VARCHAR(100);

  CALL DBMS_UTILITY.CANONICALIZE(p_name, v_canon, p_length);
  CALL DBMS_OUTPUT.PUT_LINE('Canonicalized name ==>' || v_canon || '<==');
  CALL DBMS_OUTPUT.PUT_LINE('Length: ' || LENGTH(v_canon));
END@

CALL canonicalize('Identifier')@
CALL canonicalize('"Identifier"')@
CALL canonicalize('"_+142%"')@
CALL canonicalize('abc.def.ghi')@
CALL canonicalize('"abc.def.ghi"')@
CALL canonicalize('"abc".def."ghi"')@
CALL canonicalize('"abc.def".ghi')@

This example results in the following output:

SET SERVEROUTPUT ON
DB20000I  The SET SERVEROUTPUT command completed successfully.

CREATE OR REPLACE PROCEDURE canonicalize(
  IN p_name VARCHAR(4096), 
  IN p_length INTEGER DEFAULT 30)
BEGIN
  DECLARE v_canon VARCHAR(100);

  CALL DBMS_UTILITY.CANONICALIZE(p_name, v_canon, p_length);
  CALL DBMS_OUTPUT.PUT_LINE('Canonicalized name ==>' || v_canon || '<==');
  CALL DBMS_OUTPUT.PUT_LINE('Length: ' || LENGTH(v_canon));
END
DB20000I  The SQL command completed successfully.

CALL canonicalize('Identifier')

  Return Status = 0

Canonicalized name ==>IDENTIFIER<==
Length: 10

CALL canonicalize('"Identifier"')

  Return Status = 0

Canonicalized name ==>Identifier<==
Length: 10

CALL canonicalize('"_+142%"')

  Return Status = 0

Canonicalized name ==>_+142%<==
Length: 6

CALL canonicalize('abc.def.ghi')

  Return Status = 0

Canonicalized name ==>"ABC"."DEF"."GHI"<==
Length: 17

CALL canonicalize('"abc.def.ghi"')

  Return Status = 0

Canonicalized name ==>abc.def.ghi<==
Length: 11

CALL canonicalize('"abc".def."ghi"')

  Return Status = 0

Canonicalized name ==>"abc"."DEF"."ghi"<==
Length: 17

CALL canonicalize('"abc.def".ghi')

  Return Status = 0

Canonicalized name ==>"abc.def"."GHI"<==
Length: 15