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

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.

1 Answer 1


Have you tried creating the JSON with SQL/JSON?

Data to JSON https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/generation.html#GUID-6C3441E8-4F02-4E95-969C-BBCA6BDBBD9A

JSON to Data https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/function-JSON_VALUE.html#GUID-0565F0EE-5F13-44DD-8321-2AC142959215

  • The page includes several functions that convert from JSON into VARCHAR2. I need to convert the other way around. Also it seems those functions are implemented in Oracle 18, but I have Oracle 12 only. Oct 26, 2021 at 23:23
  • Answer updated with correct methodfor 18c. I'll have to double check 12c documents. I was actually looking for 21c, not 12c. Oct 26, 2021 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.