Scripts to help with converting an Oracle .dmp to CSV

convert2csv.sh

#!/bin/sh

# script to automate the load and export to CSV of an oracle dump
# This script assumes:
# * you have the vagrant published key available locally in your .ssh directory
# * You have the Oracle VirtualBox image running locally
# ** ssh port-forwarding is configured for host port 2022 -> guess port 22.

set -e

SSH_PORT=2022
ORACLE_HOST=127.0.0.1

REMOTE_MACHINE="oracle@${ORACLE_HOST}"

SSH_ARGS="-i ${HOME}/.ssh/vagrant -p $SSH_PORT"

DMP_FILE='my-dump.dmp'

# Copy utility scripts
rsync -Pae "ssh $SSH_ARGS" create-db.sql export.sql dump2csv.sql $REMOTE_MACHINE:~/
# Copy database dump
rsync -Pae "ssh $SSH_ARGS" $DMP_FILE $REMOTE_MACHINE:~/

# create the tablespace, user, grants, and directory object
ssh $SSH_ARGS $REMOTE_MACHINE "sqlplus / as sysdba @create-db.sql"
# import the Oracle .dmp file
ssh $SSH_ARGS $REMOTE_MACHINE "imp username/password file=$DMP_FILE full=yes"

# Create the PL/SQL procedure to export tables to CSV
ssh $SSH_ARGS $REMOTE_MACHINE "sqlplus username/password @dump2csv.sql"
# Ensure that the target directory is created.
ssh $SSH_ARGS $REMOTE_MACHINE "mkdir /tmp/exports"

# Run the script to export all the tables
ssh $SSH_ARGS $REMOTE_MACHINE "sqlplus username/password @export.sql"

# Zip up the export
ssh $SSH_ARGS $REMOTE_MACHINE "cd /tmp; tar cjvf exports.tar.bz2 exports"

# Copy the export
rsync -Pae "ssh $SSH_ARGS" $REMOTE_MACHINE:/tmp/exports.tar.bz2 ./$(date +%Y-%m-%dT%H%M%S)-exports.tar.bz2

create-db.sql

-- Create a tablespace for isolation.
CREATE BIGFILE TABLESPACE DBNAME DATAFILE 'DBNAME.dat' SIZE 20M AUTOEXTEND ON;
-- Create the user.
CREATE USER USERNAME IDENTIFIED BY PASSWORD DEFAULT TABLESPACE DBNAME;
-- Grant permissions.
GRANT CREATE SESSION,CREATE SYNONYM,CONNECT,RESOURCE,CREATE VIEW,IMP_FULL_DATABASE to USERNAME;
-- end the sqlplus session so that the ssh command returns.
quit;

dump2csv.sql

-- Ensure that the named DIRECTORY object is defined for later use when exporting data.
CREATE OR REPLACE DIRECTORY TMP_EXPORTS as '/tmp/exports';

-- Create the procedure used to dump a table to CSV.
create or replace procedure dump_table_to_csv( p_tname in varchar2 )
  is
      l_output        utl_file.file_type;
      l_theCursor     integer default dbms_sql.open_cursor;
      l_columnValue   varchar2(4000);
      l_status        integer;
      l_query         varchar2(1000)
                       default 'select * from ' || p_tname;
      l_colCnt        number := 0;
      l_separator     varchar2(1);
      l_descTbl       dbms_sql.desc_tab;
  begin
      -- Note that this takes the name of a directory object. Not the name of a directory!
      l_output := utl_file.fopen( 'TMP_EXPORTS', p_tname || '.csv', 'w', 32767 );
      execute immediate 'alter session set nls_date_format=''yyyy-mm-dd"T"hh24:mi:ss'' ';

      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
  
      for i in 1 .. l_colCnt loop
          utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"');
          dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
          l_separator := ',';
      end loop;
      utl_file.new_line( l_output );
  
      l_status := dbms_sql.execute(l_theCursor);
  
      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
          l_separator := '';
          for i in 1 .. l_colCnt loop
              dbms_sql.column_value( l_theCursor, i, l_columnValue );
              utl_file.put( l_output, l_separator || '"' || replace(l_columnValue, '"', '""') || '"');
              l_separator := ',';
          end loop;
          utl_file.new_line( l_output );
      end loop;
      dbms_sql.close_cursor(l_theCursor);
      utl_file.fclose( l_output );
  
      execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
  exception
      when others then
          execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
          raise;
  end;
/
quit;

export.sql

exec dump_table_to_csv('TABLE1');
exec dump_table_to_csv('TABLE2');
quit;