ORACP File Transfer Utility

This program is a command line utility that can perform file operations over an Oracle JDBC connection. It can simplify the transfer of files between a client and the database compared to approaches like SFTP.

It makes use of the SYS.UTL_FILE package to execute file operations over JDBC. For example it can transfer a datapump dump file before running the import or export utilities.

Table of Contents

• Features
• Quick Start
• Task Execution
  • List Example
  • Get Example
  • Put Example
• Building
  • Gradle Installation
  • OTN Maven Configuration
  • Bintray Maven Configuration
  • Build Tasks
• Tests
• See Also
• Author
• License

Features

This program can run the following tasks:

• List: List the contents of an oracle Directory Object.
• Get: Transfer a file from the database to a local directory.
• Put: Transfer a local file to a database directory.

If you are running on Windows you can execute the launcher generated by launch4j. As an alternative you can run tasks from the included Gradle launcher script or use the executable JAR file.

Below is the usage displayed when invoked with the --help option.

usage: oracp [OPTIONS] [get|list|put]
   -d,--debug            turn on debug messages
   -f,--force            Force overwrite of destination.
   -h,--help             print this message
   -p,--passwd <arg>     DB password
   -s,--db-dir <arg>     DB directory object
   -u,--user <arg>       DB username
      --url <arg>        Oracle JDBC URL (jdbc:oracle:thin:@//hostname:port/service)

ORACP - Database Copy utility (v1.1.0)

You must choose one of the following tasks:
   get [REMOTE-FILE] [LOCAL-DIR]...... Transfer a file from the database to a local directory.
   list............................... List the contents of an oracle Directory Object.
   put [LOCAL-FILE]................... Transfer a local file to a database directory.

This program is a command line utility to execute file operations over an
Oracle JDBC connection.
Contact Chad Juliano<[email protected]> for feedback or assistance.

Terminating: Help option requested

Quick Start

1. Confirm that you have a copy of Java 1.8.

2. Download a binary distribution from the Bintray download button at the top of this page. From the Bintray page scroll down and you will see a zip file under the Downloads heading.

3. Download Oracle JDBC 7 ojdbc7-12.1.0.2.jar and place it in the extracted lib folder. You may need a an OTN login if prompted for credentials.

4. If you are running windows execute the oracp.exe program. If you are on Linux execute oracp.sh.

5. Before you can run the directory list task you will need to create the fn_list_dir function with the included create script fn_list_dir.sql. It must be run as the SYS user.

Task Execution

The below examples show the arguments passed if you are using the launch4j generated executable (oracp.exe). If running on Linux you should use the provided oracp.sh script.

List Example

In this example we list the contents of directory APP_DUMP_DIR.

C:\temp\oracp> oracp \
	--url jdbc:oracle:thin:@//oracledb.test.com:1521/PINDB \
	--user PIN102 --passwd PIN102 \
	--source-dir APP_DUMP_DIR \
	list

Opening Connection...
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
* Starting task: LIST
Total 4 files in APP_DUMP_DIR:
  expdp-confdb-daily.dmp
  expdp-confdb-daily.log
  expdp-jiradb-daily.dmp
  expdp-jiradb-daily.log

Get Example

In this example we transfer file expdp_xref116_v4.dmp to the current directory ./.

C:\temp\oracp> oracp \
	--url jdbc:oracle:thin:@//oracledb.test.com:1521/PINDB \
	--user PIN102 --passwd PIN102 \
	--source-dir APP_DUMP_DIR \
	get expdp_xref116_v4.dmp ./

Opening Connection...
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
* Starting task: GET
Source: APP_DUMP_DIR/expdp_xref116_v4.dmp (4.14 MB)
Destination: <C:\temp\bin\expdp_xref116_v4.dmp>
  0.75% (31/4240 KB) (88.89 KB/sec)
  9.06% (383/4240 KB) (333.64 KB/sec)
[...]
  99.62% (4223/4240 KB) (420.64 KB/sec)
  100.00% (4240/4240 KB) (204.16 KB/sec)
Transfer Complete!

Put Example

In this example we transfer file C:\temp\expdp_xref116_v4.dmp to the database directory APP_DUMP_DIR.

Note: The process will fail if the file already exists in the destination. You can use the --force option to override this check.

C:\temp\oracp> oracp \
	--url jdbc:oracle:thin:@//oracledb.test.com:1521/PINDB \
	--user PIN102 --passwd PIN102 \
	--source-dir APP_DUMP_DIR \
	--force \
	put \temp\expdp_xref116_v4.dmp

Source: C:\temp\expdp_xref116_v4.dmp (0.17 MB)
Destination: <APP_DUMP_DIR/expdp_xref116_v4.dmp>
Force overwrite of destination file!
  17.95% (31/178 KB) (49.69 KB/sec)
  71.79% (127/178 KB) (65.80 KB/sec)
  100.00% (178/178 KB) (84.97 KB/sec)
Transfer Complete!

Building

Gradle Installation

To build this program you will need a Gradle Installation. If you are behind a proxy then you may need to configure proxy settings.

OTN Maven Configuration

Before you can build or run this project you will need a copy of the ojdbc7.jar driver that can't be included because it is covered under the Oracle Technology Network License Agreement.

Edit the Maven section of the gradle.build file with your OTN credentials in the section indicated below. When you launch the build target the dirver should be downloaded with the other dependencies.

maven {
    url 'https://www.oracle.com/content/secure/maven/content'
    credentials {
        username 'user'
        password 'password'
    }
}

Bintray Maven Configuration

You can reference oracp as a library from the Bintray repository. Add the following repository and dependency to your Gradle script.

maven {
    url  'http://dl.bintray.com/chadj2/cmd-utils'
}

dependencies {
    compile group: 'org.oracp.OcpTaskDriver', name: 'oracp', version: '1.1.0'
}

Build Tasks

Important Gradle tasks are:

• build: Compile java sources and create jar file in build/libs/.
• test: Invoke the JUnit test suite.
• installOjdbc7Dist: Copy distribution files without OJDBC into build/install/oracp-ojdbc7
• installNoOjdbc7Dist: Copy distribution files with OJDBC into build/install/oracp-noOjdbc7
• noOjdbc7DistZip: Create the distribution archive at build/distributions/oracp-noOjdbc7-1.1.0.zip
• publishMavenJavaPublicationToMavenLocal: Publish to local Maven repository.
• publishMavenJavaPublicationToMavenRepository: Publish to remote Maven repository.

Tests

This project includes a JUnit test suite that is executed as part of the build and test targets. By default most of the tests are skipped because they require connectivity to an Oracle database.

To enable these tests edit the connection information in OcpMainTest.java and comment the includeTestsMatching filter in the build.gradle script shown below.

test {
    filter {
        //don't include tests requiring a DB connection.
        includeTestsMatching "*.T01_testHelp"
    }

    testLogging {
        // set options for log level LIFECYCLE
        exceptionFormat "full"
    	showStandardStreams true
    }
}

See Also

• Task Driver: Command line framework used by ORACP.

Author

• Chad Juliano

License

This program is licensed under GNU Lesser General Public License v3.0 only. Some rights reserved. See LICENSE.