Is there a bulk unload utility in Oracle

Question

I am trying to unload a large oracle table as a JSON file. Is there a BCP utility similar to what we have in SQL Server?

Thanks

Answer 1

Using SQL*Plus or SQLcl to spool to a file is one option (methods to generate CSV would work similarly for JSON):

• https://blogs.oracle.com/opal/fast-generation-of-csv-and-json-from-oracle-database
• https://oracle-base.com/articles/misc/sqlcl-format-query-results-with-the-set-sqlformat-command#json
• https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536328100346697722

You could also use PL/SQL functions and write to a file using UTL_FILE:

• https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-6C3441E8-4F02-4E95-969C-BBCA6BDBBD9A
• https://oracle-base.com/articles/9i/generating-csv-files

Or use ROracle:

• https://oralytics.com/2015/05/14/extracting-oracle-data-generating-json-data-file-using-roracle/

There are most likely several other ways, too.