Importing Data from Oracle

Connect to Oracle

Summary:

OrgPlus can connect to a variety of data sources, including an Oracle database. When OrgPlus and an Oracle Client are installed on the same computer, three Oracle configuration files (.ora) are used by the OrgPlus connection Wizard to create a connection to the Oracle database.

Procedure:

If errors occur when creating a connection to an Oracle database, the Oracle error messages (e.g., ORA-12154) point to specific problems within the configuration files. Google searches of ORA-##### error messages typically have good results.

Oracle Configuration Files:

The three Oracle configuration files are generated by Oracle configuration tools. The file names are tnsnames.ora, sqlnet.ora, and listner.ora.

OrgPlus expects to locate the Oracle programs (.dlls) in the directories indicated by the paths below. These files are necessary to create the connection as well as to obtain database parameters.

The local paths to the configuration files are:

  • C:\Oracle\product\oracle version\Client_1\network\admin\tnsnames.ora
  • C:\Oracle\product\oracle version\Client_1\network\admin\sqlnet.ora
  • C:\Oracle\product\oracle version\Client_1\network\admin\listner.ora

To create a Connection to Oracle:

Select Import Data from the File Menu

Select “My employee data is ready to be imported.

Click Get Started

connect-to-oracle-1

Select the Data Source – Oracle

Click the Next button

connect-to-oracle-2B

connect-to-oracle-3

table/view name.ora:

The table/view name.ora connection file is created by the OrgPlus connection Wizard. The file name is derived from the table or view selected in the Wizard. The contents of a connection file are as follows:

[SCHEMA]
AskLogin=yes
FldPosition=
FldReportsTo=
FieldsTimeFormat=
FieldsToImport=list of fields found in the selected table/view
FieldsFullName=
QueryDir=C:\Documents and Settings\user\My Documents\My Charts\
DataQuery=table/view name.sql

[CONNECT]
Server=database name or alias entered in the Connection Wizard
User=database user name entered in the Connection Wizard
Password=user password entered in the Connection Wizard

Sample Oracle Configuration Files:

The entries in the Oracle configuration files are needs based, below is just a sampling of the general syntax for such entries.

tnsnames.ora

The file contains the following entries:

database name or alias =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host server name)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = global/server side database name)
  )
)

sqlnet.ora:

The file contains the following entries:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

listner.ora:

The file contains the following entries:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = C:\Oracle\product\oracle version\Client_1)
     (PROGRAM = extproc)
   )
 )

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dell_p4)(PORT = 1521))
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
  )
)