Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Convert a simple String to table

Geert01Mar 27 2024 — edited Mar 27 2024

I want to convert this simple string to a table:

{ "parameter_1":"Value_1","parameter_2":"Value_2","parameter_3":"Value_3","parameter_4":"Value_4" }

I tried using DBMS_UTILITY.COMMA_TO_TABLE but that throws an ERROR: ORA-20001: comma-separated list invalid near 1":"V error.

I have tried many json things but cannot manage to get the key fields into the table.

SELECT key,value
FROM json_table('{ "parameter_1":"Value_1","parameter_2":"Value_2","parameter_3":"Value_3","parameter_4":"Value_4" }', '$.*'
COLUMNS (
key PATH '$'
,value PATH '$'
)
)

The above query obviously gives me the value twice (because the key column has the same path), but what do I need to change in order to get the key into the key column?

I have asked ChatGPT for a solution, and even though it gave me many suggestions, none of them work."

Comments
Post Details
Added on Mar 27 2024
1 comment
132 views