I need to produce a JSON document with several properties from a list of Oracle 12c1 production databases, and I was given read-only access in order to generate this data.
The problem is these databases were created over time and may store VARCHAR2 values in different character sets. My naive attempt at producing JSON is:
SELECT '{' ||
'"content":"' || content || '", "' ||
'"name":"' || name || '"}'
FROM my_table
WHERE ...
However, since the columns content and name may have all kind of special characters and use non-trivial character sets, the query above may produce invalid JSON documents.
Is there any function that can convert a VARCHAR2 into Unicode from any character set? I think if the string is in Unicode it could be easy to manage. Unfortunately, I don't really know each column's character set in advance.
