0

The goal is to export an existing test Oracle database to SQL so that it can be run in a test pipeline using a Docker images's /container-entrypoint-initdb.d bootstrap. Currently we mount an oradata export, but it's opaque what the actual data is, hence the desire to use SQL so that it can be maintained via git.

I'm aware you can use the GUI Oracle SQL Developer Export Wizard to export a database to SQL.

Though how to do it via command line tools? I've tried many things like:

impdp test/test@XEPDB1 dumpfile=export.dmp directory=DUMP_DIR sqlfile=ddl.sql

And the best I can achieve is exporting the schema, however it is missing the actual data, e.g.

INSERT INTO currencies_countries (currency_id, country_id) VALUES ('EUR', 'CYP');
Improve this question
5
  • Sorry, but - what does it mean, "export Oracle database to SQL"? What is "SQL" in this context? That's a language so ... kind of strange requirement. Commented Feb 3, 2023 at 7:59
  • The SQL describes the state of the database. Commented Feb 3, 2023 at 8:07
  • 2
    SQLcl is the command-line derivative of SQL Developer (and sort of replaces SQL*Plus), and with set sqlformat insert you can query all of your tables individually to get the insert statements you seem to want, maybe with spool. Or you can write PL/SQL to dump to files yourself. But you still need the DDL, and you'll have to run inserts in the right order for PK/FKs etc. Data pump handles all the complications for you - I would seriously reconsider using expdp/impdp. Commented Feb 3, 2023 at 8:32
  • SQLcl does not seem packaged in any Docker image like github.com/gvenzl/oci-oracle-xe so I am puzzled how to get it in my CLI script. Commented Feb 3, 2023 at 8:46
  • impdp is the import data pump utility and is used, as the name suggests, to import data. If you want to export data then you want the export data pump utility expdb. Commented Feb 3, 2023 at 9:36

1 Answer 1

Reset to default
-1

You have the exp which exports to a dump file that can be imported into another Oracle DB using imp. More recent are the xpdp and impdp. See: https://oracle-base.com/articles/10g/oracle-data-pump-10g for instance. What do you mean by "export to SQL"? The sqldeveloper client tool has the possibility of exporting a table as SQL inserts, for instance. But to export that table by table would be quite an effort. Moreover, if you have millions of rows, those row by row inserts won't perform well. Better write to tab-separated text files, which, in case of need may be imported into an Oracle DB using sqlldr, or may somehow be imported to some other kind database. Still, tab-separated files won't work well for tables having CLOB, XMLTYPE or some object-type columns. You could write to a text file by spooling sqlplus output to a file having set linesize ling enough to fit the columns length, set pagesize 0 (no pages). Or you could write to a file in an Oracle directory via a pl/sql program in which you use utl_file.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I want please to get the input I primed the DB with, back out: github.com/kaihendry/oracle-sql/blob/main/install.sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.