Need a very simple way to export Oracle data to CSV format – and import from CSV format?
Here is a fairly easy example of doing both sides (import and export) using Oracle’s early adopter “sqlcl” product. And yes, there are many other ways to accomplish this. Once your have this method down – the sqlcl tool can be applied in many situations. Let’s get on Oracle to productionize sqlcl. Google “that jeff smith” (the product Manager I believe) and leave him a note as him when it’s going production – it’s been early adopter for about 1.5 years as of this writing.
I set this up in the Win 10 Pro environment but, it’s just as easy in UNIX/Linux/Mac OS.
To export:
I’ll use the HR.EMPLOYEES table that comes with the sample schemas available when you create an Oracle database.
— create a export SQL script C:\LoadCentral\export_employees_stg.sql as follows:
SET SQLFORMAT CSV SPOOL C:\LoadCentral\export_employees_stg.csv SELECT * FROM HR.EMPLOYEES; SPOOL OFF
— start Oracle’s sqlcl tool (google sqldeveloper download) – if you don’t have it (early adopter on the Oracle SQLDeveloper download page at the bottom of the page)
C:\sqldeveloper413\sqlcl\bin>sql.bat /nolog
SQLcl: Release 4.2.0.15.296.0549 RC on Sat Apr 02 20:34:32 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Using SQLCL execute the following scripts as SYS, SYSTEM or as the HR user. You may have to unlock the account to logon to HR – but you can get at the data as SYS or SYSTEM privileged account easy enough without unlocking the HR account or:
alter user hr identified by hr account unlock;
— Connect to the user you want use to export with – note your usernames/password will vary.
SQL> connect hr/hr@silver:1521:silver
Connected
@C:\LoadCentral\export_employees_stg.sql
To import:
— Now we’ll do the import to another test schema myschema/myschema – yes normally you would be moving this data around but just to illustrate the functionality – I’ll put the CSV we just generated right back into the same database – different user / schema.
— create the myschema user and grant the correct privs
connect myschema/myschema@silver:1521:silver
show user
create table employees_stg as select * from hr.employees
truncate table employees_stg
SQL> conn myschema/myschema@silver
Connected.
SQL> load employees_stg C:\LoadCentral\export_employees_stg.csv
–Number of rows processed: 107
–Number of rows in error: 0
0 – SUCCESS: Load processed without errors
SQL> commit;
SQL> select count(*) from employees_stg;
“COUNT(*)”
107
Hope this helps, Mike