For Direct Connect to work, it is necessary to install the Oracle client software on the local machine. The Oracle Client software installed on the local machine determines which string is used for the service.
The sde:oracle** string tells the sde client api the driver being looked for, not the database being connected to. In other words, an oracle9i driver connects to an 8i database and vice versa. The drivers differ in the version of Oracle with which they were built.
For Oracle8i client software on the local machine use:
For Oracle9i and Oracle10g client software on the local machine with ArcSDE prior to 9.2 use:
For Oracle10g client software on the local machine with ArcSDE 9.2 or later use: