Oracle Database¶

Introduction¶

This page covers the SFTPPlus configuration options for connecting to an Oracle Database server and mapping files to database rows.

Check the Oracle Database Transfers documentation, to find out more about how to configure Oracle Database transfers.

name¶

Default value:

Empty

Optional:

No

From version:

2.8.0

Values:

• Any text.

Description:

Human-readable short text used to identify this location.

description¶

Default value:

Empty

Optional:

Yes

From version:

2.8.0

Values:

• Any text.

Description:

Human-readable text that describes the purpose of this location.

type¶

Default value:

''

Optional:

No

From version:

2.6.0

Values:

• filesystem - Local file system.

• sftp - SFTP protocol v3 over SSH v2.

• ftp - FTP protocol without any encryption.

• ftpse - Explicit FTPS protocol.

• ftpsi - Implicit FTPS protocol.

• smb - SMB / Windows Share

• as2 - AS2 over HTTP or HTTPS

• azure-file - Azure File Service.

• azure-blob - Azure BLOB Storage.

• sharepoint-online - SharePoint via MS Graph API.

• oracle-database - Oracle Database.

• exchange-online - Microsoft Exchange Online.

• http-pull - HTTP or HTTPS for pulling files.

• smtp - SMTP protocol for sending emails.

• webdavs - WebDAV over HTTPS.

Description:

This option specifies the type of the location. Each type has a set of specific configuration options

idle_connection_timeout¶

Default value:

300

Optional:

Yes

From version:

3.0.0

Values:

• Number of seconds

• 0

Description:

This controls the automatic disconnection from the remote server after the location has not received any file transfer operation requests for the configured number of seconds.

Keep-alive command requests are not counted as file transfer operations. The connection gets automatically disconnected if keep-alive is the only command requested in the configured interval.

Disconnected locations automatically reconnect when a new file transfer operation request is made. For example, when a new file needs to be transferred to the remote server.

If the remote peer closes the connection before the configured timeout, the connection is left closed. It gets automatically reconnected when a new file transfer operation is requested.

Set to 0 to always keep the connection active, by forcing re-connection when the remote server closes the connection.

Note

The idle_connection_timeout is the maximum number of seconds before closing an idle connection to the server. If the remote server decides that the connection is idle and closes the connection, SFTPPlus doesn't try to "challenge" the server, leaving the connection closed. The connection is automatically reopened next time a file needs to be transferred.

idle_connection_keepalive_interval¶

Default value:

0

Optional:

Yes

From version:

3.0.0

Values:

• Number of seconds

Description:

Send a keep-alive command every N seconds to avoid having the connection disconnected by the other peer due to inactivity.

Set to 0 to disable keep-alive commands.

The keep-alive command does not reset the idle connection timeout,

connection_retry_count¶

Default value:

12

Optional:

Yes

From version:

3.9.0

Values:

• Number of retries

Description:

Number of times to retry connection to the location, when the initial connection fails.

Set to 0 to not retry.

When the connection still fails after all the retries, the location is marked as failed and no re-connections or transfers are attempted. An administrator needs to review the error, fix the issue, and manually restart the location.

connection_retry_interval¶

Default value:

300

Optional:

Yes

From version:

3.9.0

Values:

• Number of seconds

Description:

Number of seconds to wait between connection attempts.

Set to 0 to retry right away without any delay.

address¶

Default value:

Empty

Optional:

No

Values:

• Host name or IP address of the Oracle DB server.

From version:

5.18.0

Description:

Address of the server. IP or host name.

port¶

Default value:

1521

Optional:

Yes

Values:

• Number, greater than 0.

From version:

5.18.0

Description:

Port number to connect to the server.

username¶

Default value:

Empty

Optional:

No

From version:

5.18.0

Values:

• Text.

Description:

Username used to authenticate to the server.

password¶

Default value:

Empty

Optional:

Yes

From version:

5.18.0

Values:

• Plain text password.

• Empty.

Description:

This option specifies the password used to connect to the server.

service_name¶

Default value:

Empty

Optional:

No

From version:

5.18.0

Values:

• Plain text.

• Empty.

Description:

Name of the database/PDB to connect to.

sql_file_column_type¶

Default value:

CLOB

Optional:

No

From version:

5.18.0

Values:

• CHAR

• CLOB

• BLOB

• NCLOB

Description:

The SQL column type used to store the file content.

Use CHAR for CHAR, VARCHAR2, NCHAR, or NVARCHAR2 columns.

sql_table_list¶

Default value:

All tables for current user.

Optional:

No

From version:

5.18.0

Values:

• Single SQL statement.

Description:

SQL statement to execute against the database to retrieve the list of possible tables to be considered for the transfer.

Used to detect which tables are available as source or destination for transfers.

The statement should return a single column, of type string, with the table names.

When left empty, it will default to this query:

SELECT OWNER || '.' || OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'TABLE' AND OWNER = (SELECT USER FROM dual)

sql_file_list¶

Default value:

Empty

Optional:

No

From version:

5.18.0

Values:

• Single SQL statement.

Description:

SQL statement to execute against the database to retrieve the list of possible files to be considered for the transfer.

Used to detect which files needs to be transferred from a source database.

Use to detect if there are any conflicts when sending files to a destination database.

It should return four columns, in this exact order and with this exact data types:

1. File ID (number)

2. File modification time (Oracle timestamp / date and time)

3. File size (number)

4. File name (string)

This is a single statement, without semicolon at the end. Any trailing semicolon will be removed automatically.

The SQL statement can be defined on multiple lines.

sql_file_read¶

Default value:

Empty

Optional:

No

From version:

5.18.0

Values:

• Single SQL statement.

Description:

SQL statement to execute to retrieve the content of a file.

It should return a single column, of type string, BLOB or CLOB.

The statement should contain the :FILE_ID SQL bind variable. It will be replaced with the actual file ID when executing the statement.

sql_file_delete¶

Default value:

Empty

Optional:

No

From version:

5.18.0

Values:

• Single SQL statement.

Description:

SQL statement to execute to delete a file.

The statement should contain the :FILE_ID SQL bind variable. It will be replaced with the actual file ID when executing the statement.

sql_file_write¶

Default value:

Empty

Optional:

No

From version:

5.18.0

Values:

• Single SQL statement.

Description:

SQL statement to execute to write the content of a file.

If if data is stored as BLOB or CLOB, use the empty_blob() / empty_clob() SQL function to initialize the value, then return it via the :CONTENT bind variable.

The following placeholders can be used in the statement, and will be replaced with actual values when executing the statement: - :FILE_CONTENT: The content of the file being written. - :FILE_ID: The ID of the file being written. - :FILE_NAME: The name of the file being written. - :FILE_SIZE: The size of the file being written. - :FILE_MODIFIED: The modification time of the file being written.