In Oracle Database, the COMPOSE() function returns the result of applying the Unicode canonical composition, as described in the Unicode Standard definition D117, to its string argument.

It’s the opposite of the DECOMPOSE() function.

Syntax

The syntax goes like this:

COMPOSE(char)

Where char is one of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data types.

Example

Here’s an example to demonstrate:

SELECT 
    COMPOSE('a' || UNISTR('\0303'))
FROM DUAL;

Result:

ã

Non Unicode Characters

If the character set of the argument is not one of the Unicode character sets, the argument is returned unmodified.

Example:

SELECT COMPOSE( 'a' )
FROM DUAL;

Result:

a

Null Argument

If the argument is null, the result is null:

SET NULL 'null';
SELECT COMPOSE(null)
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Invalid Argument Count

Calling COMPOSE() without any arguments results in an error:

SELECT COMPOSE()
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"

And passing too many arguments also results in an error:

SELECT COMPOSE('a', 'b')
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"