0

I have to export the data from an oracle table to the new delimited JSON format. This is the command that i used in sqlcl.

SET COLSEP ','
SET SQLFORMAT json;
SPOOL 'C:\json_o\employee.json'
SELECT *
FROM employee
/
SPOOL OFF

and the result look like this.

{"results":[{"columns":[{"name":"EMP_ID","type":"NUMBER"},{"name":"FIRST_NAME","type":"VARCHAR2"},{"name":"LAST_NAME","type":"VARCHAR2"},{"name":"HIRE_DATE","type":"DATE"},{"name":"JOB_TITLE","type":"VARCHAR2"},{"name":"SALARY","type":"NUMBER"}],"items":
[
{"emp_id":1,"first_name":"John","last_name":"Doe","hire_date":"01-01-22","job_title":"Manager","salary":5000}
,{"emp_id":2,"first_name":"Jane","last_name":"Smith","hire_date":"02-02-22","job_title":"Developer","salary":4000}
]}]}

I want myoutput to look like this.(New delimited JSON format)

{"emp_id":1,"first_name":"John","last_name":"Doe","hire_date":"01-01-22","job_title":"Manager","salary":5000}
{"emp_id":2,"first_name":"Jane","last_name":"Smith","hire_date":"02-02-22","job_title":"Developer","salary":4000}

NOTE:We are using 11g database.

I want to convert oracle table into Ndjson format via spool function.

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120

0 Answers0