Implicit DB Connect

Setting the alias name in TNSNAMES.ORA in the LOCAL environment variable should work in Net Express as Oracle will first try to connect to a local connection if one exists and will then look to the setting of this environment variable in order to do the implicit connect.

Here are some troubleshooting tips to help you set this up.

----------------------------------------

Oracle network configuration is controlled by tnsnames.ora , which lives under the network\\admin directory. You would typically use Oracle's network configuration assistant, netca, to configure the connection to your server.

Having done this, the entry for that connection would look something like this :

JPN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tok-par)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

The TNS Name, or alias is JPN. The Oracle server resides on a machine named tok-par, the Oracle listener daemon is listening on port 1521 - the default for Oracle - and the service name specified when installing the server is orcl. These entries should match those specified within tnsnames.ora and listener.ora on the server-side.

In order to verify that the connectivity configuration is sound, you can use Oracle tnsping utility to verify the connection, using

tnsping alias

for example, in this case

tnsping JPN

>tnsping jpn

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 20-MAY-2009 17:41:48

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
C:\\Database\\Oracle\\10gR2x86\\NETWORK\\ADMIN\\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tok-par)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
TNS-12541: TNS:no listener

Note the error shown above - the Oracle listener daemon is not running on the Server. Having corrected that, the output should look like this :

>tnsping jpn

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 20-MAY-2009 17:41:55

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
C:\\Database\\Oracle\\10gR2x86\\NETWORK\\ADMIN\\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = tok-par)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)

-----------------------------------

Execution on Windows

Depending on your Oracle configuration - for example, if you only have client-side software installed - on execution, you may get an Oracle error :

>.\\sample1

ORACLE ERROR DETECTED:

ORA-12560: TNS:protocol adapter error

The issue here is that the CONNECT statement referenced within the sample program doesn't explicitly specify a server to connect to :

        MOVE "SCOTT" TO USERNAME-ARR.
        MOVE 5 TO USERNAME-LEN.
        MOVE "TIGER" TO PASSWD-ARR.
        MOVE 5 TO PASSWD-LEN.

        EXEC SQL
        CONNECT :USERNAME IDENTIFIED BY :PASSWD
        END-EXEC.

By default, Oracle will attempt to connect to a local server. If this does not exist, Oracle will then look at the LOCAL environment variable. So having invoked

SET LOCAL=JPN

We should now be able to execute the sample:

>.\\sample1

CONNECTED TO ORACLE AS USER: SCOTT

ENTER EMP NUMBER (0 TO QUIT): 7839

EMPLOYEE SALARY COMMISSION
-------- ------ ----------
KING 5000.00 NULL

ENTER EMP NUMBER (0 TO QUIT): 0

TOTAL NUMBER QUERIED WAS 0001.

HAVE A GOOD DAY.

Alternatively, you can code an additional host variable to hold the server (TNS) name, and specify that as part of the connect statement:

        MOVE "SCOTT" TO USERNAME-ARR.
        MOVE 5 TO USERNAME-LEN.
        MOVE "TIGER" TO PASSWD-ARR.
        MOVE 5 TO PASSWD-LEN.
        MOVE "JPN" TO SVR-APP
        MOVE 3 TO SVR-LEN
        EXEC SQL
             CONNECT :USERNAME IDENTIFIED BY :PASSWD
             USING :SVR
        END-EXEC.

       MOVE 3 TO SVR-LEN

       EXEC SQL

            CONNECT :USERNAME IDENTIFIED BY :PASSWD

            USING :SVR

       END-EXEC.

I thought about this last night, our TNSNAMES.ORA has extensions on the JPN like MYJPN.WORLD.  I didn't have the extension in the value of the LOCAL variable.  Chris, you gave good documentation.  I wish such documentation was done by Micro Focus.