Oracle Database

The Oracle Database connector allows you to connect to Oracle databases directly without having to use Oracle SQL*Net client libraries.

NOTE: Starting 16.2, this connector is deprecated. The Database connector or JDBC Database connector should be used. Existing workbooks will continue to operate for this 16.2 release, but connectivity will need to be migrated for subsequent releases.

Using Oracle Database

Launch the Connect to Data dialog and then select Oracle.

The Oracle Connection dialog displays.

Select a connection type from either of these two generic modes of connection for Oracle DB Server. They are made available when you create a DSN for Oracle.

- Standard

Used without having any client software or configuration file on your machine.

- TNS Names

When you have a client and configuration file that you want to use.

If you selected the Standard connection type, provide the hostname, SID or Service Name, user ID, and password required to connect to the Oracle database you wish to access. If the port you wish to use is different from the default port, change the default value to the correct one.
If you selected the TNSNames connection type, provide the Server name, TNSNames File, user ID, and password required to connect to the Oracle database you wish to access.
Specify other connection options if desired, making sure to separate individual connection strings by a semi-colon.

The following table lists the connection string attributes supported by the Oracle Wire Protocol driver.

Attribute (Short Name)	Default
AccountingInfo (AI)	None
Action (ACT)	None
AlternateServers (ASRV)	None
ApplicationName (AN)	None
ApplicationUsingThreads (AUT)	1 (Enabled)
ArraySize (AS)	60000
AuthenticationMethod (AM)	1 (Encrypt Password)
BulkBinaryThreshold (BBT)	32
BulkCharacterThreshold (BCT)	-1
BulkLoadBatchSize (BLBS)	1024
BulkLoadFieldDelimiter (BLFD)	None
BulkLoadOptions (BLO)	0
BulkLoadRecordDelimiter (BLRD)	None
CachedCursorLimit (CCL)	32
CachedDescriptionLimit (CDL)	0
CatalogIncludesSynonyms (CIS)	1 (Enabled)
CatalogOptions (CO)	0 (Disabled)
ClientHostName (CHN)	None
ClientID (CID)	None
ClientUser (CU)	None
ConnectionReset (CR)	0 (Disabled)
ConnectionRetryCount (CRC)	0
ConnectionRetryDelay (CRD)	3
CryptoLibName (CLN)	Empty string
CryptoProtocolVersion (CPV)	TLSv1.2, TLSv1.1, TLSv1, SSLv3
DataIntegrityLevel (DIL)	0 (Disabled)
DataIntegrityTypes (DIT)	SHA1,MD5
DataSourceName (DSN)	None
DefaultLongDataBuffLen (DLDBL)	1024
DescribeAtPrepare (DAP)	0 (Disabled)
Description (n/a)	None
EditionName (EN)	None
EnableBulkLoad (EBL)	0 (Disabled)
EnableDescribeParam (EDP)	0 (Disabled)
EnableNcharSupport (ENS)	0 (Disabled)
EnableScrollableCursors (ESC)	1 (Enabled)
EnableServerResultCache (ESRC)	0 (Disabled)
EnableStaticCursorsForLongData (ESCLD)	0 (Disabled)
EnableTimestampwithTimezone (ETWT)	0 (Disabled)
EncryptionLevel (EL)	0 (Disabled)
EncryptionMethod (EM)	0 (No Encryption)
EncryptionTypes (ET)	No encryption methods are specified. The driver sends a list of all of the encryption methods to the Oracle server.
FailoverGranularity (FG)	0 (Non-Atomic)
FailoverMode (FM)	0 (Connection)
FailoverPreconnect (FP)	0 (Disabled)
FetchTSWTZasTimestamp (FTSWTZAT)	0 (Disabled)
GSSClient (GSSC)	native
HostName (HOST)	None
HostNameInCertificate (HNIC)	None
IANAAppCodePage (IACP) UNIX ONLY	4 (ISO 8559-1 Latin-1)
InitializationString (IS)	None
KeepAlive (KA)	0 (Disabled)
KeyPassword (KP)	None
Keystore (KS)	None
KeystorePassword (KSP)	None
LoadBalanceTimeout (LBT)	0
LoadBalancing (LB)	0 (Disabled)
LocalTimezoneOffset (LTZO)	"" (Empty String)
LockTimeout (LTO)	-1
LoginTimeout (LT)	15
LogonID (UID)	None
MaxPoolSize (MXPS)	100
MinPoolSize (MNPS)	0
Module (MOD)	None
Password (PWD)	None
Pooling (POOL)	0 (Disabled)
PortNumber (PORT)	None
PRNGSeedFile (PSF) UNIX\Linux only	/dev/random
PRNGSeedSource (PSS) UNIX\Linux only	0 (File)
ProcedureRetResults (PRR)	0 (Disabled)
ProgramID (PID)	None
QueryTimeout (QT)	0
ReportCodepageConversionErrors (RCCE)	0 (Ignore Errors)
ReportRecycleBin (RRB)	0 (Disabled)
ServerName (SRVR)	None
ServerType (ST)	0 (Server Default)
ServiceName (SN)	None
SID (SID)	None
SSLLibName (SLN)	Empty string
TimestampEscapeMapping (TEM)	0 (Oracle Version Specific)
TNSNamesFile (TNF)	None
Truststore (TS)	None
TruststorePassword (TSP)	None
UseCurrentSchema (UCS)	1 (Enabled)
ValidateServerCertificate (VSC)	1 (Enabled)
WireProtocolMode (WPM)	2

Provide the edition name of the database.
The easiest way to select a table and/or view to load is by choosing from a set of predefined tables and views. To do so, ensure that the Tables & Views radio button is selected. If you wish to manually construct a SQL query to pull and load data, ensure that the Query button is selected. Once either a table or view or a query has been selected, the OK button at the bottom of the dialog is enabled.
Click Load Tables to load a list of predefined tables or views. This list can be filtered by entering an appropriate string in the Search Tables search box.

You can also add a duplicate column.

Select a table to display the available columns in the Search Columns list. Once a table has been selected, the Query text box is updated to reveal the results of a SELECT * FROM TABLE query. Any other selection made updates the Query text box accordingly.
Select the columns to add to your data table by checking their corresponding Output Column box.
If you wish to parameterize a specific column, check the Parameterize checkbox and, in the dropdowns that display, select the desired value.
If the data returned is to be aggregated, check the Aggregate checkbox. The following aggregation methods are possible:

- Text Columns: Group By
- Date Columns: Count, Min, Max, Group By
- Numeric Columns: Sum, Count, Min, Max, Group By
- Numeric Columns: Sum, Count, Min, Max, Group By.

The time zone of input parameters and output data is, by default, unchanged. Changing the time zone is supported by using the Timezone list box based on the assumption that data are stored in UTC time and outputs are presented in the selected time zone.

Check the box for Enable on-demand queries if you would like to enable this function.
Click OK to confirm the selection and retrieve the record set into Panopticon Designer (Desktop).

The flat record set corresponding to the executed SQL is returned from the source database and displayed in Data Prep with the database name as the title and all fields listed displayed in Data Source Preview.

If you wish to make changes to your fields, you may do so now and then click OK when you are finished. If you do not wish to make any changes to your data, simply select the OK button.