I have created a script that reads data from Mobile App DB (which is based on MongoDB) from Oracle SQL Developer. The result is JSON string stored in l_response_text VARCHAR2(32767);
The string has the format like:
[{"Postcode":"47100","OutletCode":"128039251","MobileNumber":"0123071303","_createdAt":"2014-11-10 06:12:49.837","_updatedAt":"2014-11-10 06:12:49.837"}, {"Postcode":"32100","OutletCode":"118034251", ..... ]
However, I need to parse this l_response_text such that each array goes into into its specific column in a table called appery_test. The table appery_test has a number of columns same as the number of JSON pairs and in the same order (e.g. first column name is "Postcode")
I searched and I found most of the results about parsing Oracle table into JSON and not the opposite. I found, though, this link which is somewhat similar to my issue. However, the suggested library in the answer does not have any example on how to use it to insert JSON into conventional table using PL/SQL.
N.B.: I'm using 11g and not 12c. So the built in functions are not available for me.
Many thanks,