Skip to content

Instantly share code, notes, and snippets.

@jabley
Created September 13, 2012 11:05
Show Gist options
  • Save jabley/3713603 to your computer and use it in GitHub Desktop.
Save jabley/3713603 to your computer and use it in GitHub Desktop.
Scripts to help with converting an Oracle .dmp to CSV
#!/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 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;
-- 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;
exec dump_table_to_csv('TABLE1');
exec dump_table_to_csv('TABLE2');
quit;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment