OrgPlus 5 - Connect to Oracle
OP-0500-S0004

Summary:

OrgPlus 5 can connect to a variety of data sources, including an Oracle database. When OrgPlus 5 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 5 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 and click New Source. The Select Data Source dialog box is displayed. Select Oracle.

The Oracle Wizard - Connection dialog box is displayed. Enter the database name or alias. For Log on credentials, select either Use Windows Authentication or Use the following User Name and Password option and enter the appropriate User Name and Password.

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

Return to OrgPlus 5 Knowledge Base Articles