Search code examples
sqloracleoracle-sqldevelopersqlcl

Oracle SQL: How to capitalize first character of column header rather than the value?

I've built a statement where the result is spooled into a .json file.

With SQLcl, it starts off as:

SET ENCODING UTF-8
SET SQLFORMAT JSON
SPOOL language.json

Then, I do selects. Just an example:

SELECT
    INITCAP(ltl.language) language,

// ... goes on

Output:

"language":"En-Us"

This will capitalize the value of language -- however, what if I want language to be Language in the .json dump?

What I have tried is the following:

SELECT
    ltl.language INITCAP(language),

// ... goes on

But that isn't a valid query.

Also, changing language to Language in the query makes no difference - it's transformed to lowercase:

SELECT
    INITCAP(ltl.language) Language,

Output:

"language":"en-US"

My desired output is:

Output:

"Language":"en-US"

How do I achieve that from the query?

Solution

  • You cannot influence the case of the json value pair KEYS generated when using /*json*/ or SQLFORMAT json. Per our Oracle spec, those are lowercase by design.

    You would need to build your own JSON generator, or write some shell scripts with RegEX or whatever you find handy to go in and init cap the keys after-the-fact.