Knowledge Base

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

Select the Data Source – Oracle

Click the Next button

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))
    )
  )
)