Error: ORA-20004: Error generating shape from text: Shape has too many parts (-5)

Error Message

When constructing a Well-Known Text (WKT) representation of a geometry in a SQL statement, the following Oracle error may display:

|| x || ' ' || y
|| ' ) '',' || srid
|| ')) from DUAL';

ORA-20004: Error generating shape from text: Shape has too many parts (-5).
ORA-06512: at line 54


The following pieces of SQL syntax show a common way for constructing WKT strings. WKT is constructed by concatenating literal text strings and numeric variables casted to text either implicitly (in the first example) or explicitly (in the second example).

'point ( ' || x || ' ' || y || ' )'

'point ( ' || to_char(x) || ' ' || to_char(y) || ' )'

In each example, the numbers stored in the variables 'x' and 'y' are cast to text strings before being concatenated to the remaining text string. The character used for the decimal point is chosen based on the NLS setting of the database. The NLS setting on some databases can cause the decimal point to be represented by a comma.

If the decimal point is not a period (a dot), the WKT will not be correct. Strings containing numeric values in the WKT representation of a geometry are expected to have decimal points that are the period character. If the decimal points are commas, the WKT parser considers them to be separators of vertices. This can cause the parser to believe that the geometry (shape) contains too many parts.

Solution or Workaround

It is possible to force the decimal point to be a period by using the optional parameters of Oracle’s TO_CHAR function. For complete information on the options available in the TO_CHAR function, refer to Oracle's documentation. Here is an example:

'point ( ' || TO_CHAR(x,'99999D99', NLS_NUMERIC_CHARACTERS = ".,") ||
' ' || TO_CHAR(y,'99999D99', NLS_NUMERIC_CHARACTERS = ".,") || ' )'