8 Replies Latest reply: Mar 11, 2013 12:05 PM by CraigB RSS

    Unable to connect Form 6i to Oracle database 10g

    995443
      I've got an Oracle Form application to run with Oracle Form 6i (yes, too old, but that is also an old application written for Form 6i and now I have to run it). And the database server is 10.1.0. I run both in the same Windows XP pro box (in a virtualBox machine).

      The encoding of the database server is UTF8.

      I have already configured the listener for Oracle server; configured TNS name for Form 6i client. I can successfully connect to the server with sqlplus (PLUS80W.EXE) shipped in the Form 6i.

      But I am unable to login with the Form application.

      Here is the listener.ora (configured by Net Manager application)
      ORAUTF8 =
      +(DESCRIPTION_LIST =+
      +(DESCRIPTION =+
      +(ADDRESS = (PROTOCOL = TCP)(HOST = winxp-vm)(PORT = 1521))+
      +)+
      +(DESCRIPTION =+
      +(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))+
      +)+
      +)+

      SID_LIST_ORAUTF8 =
      +(SID_LIST =+
      +(SID_DESC =+
      +(SID_NAME = PLSExtProc)+
      +(ORACLE_HOME = C:\data\oracle\product\10.1.0\db)+
      +(PROGRAM = extproc)+
      +)+
      +)+

      Here is the tnsnames.ora (C:\orant\NET80\ADMIN\tnsnames.ora)
      TESTDB  =
      +(DESCRIPTION =+
      +(ADDRESS = (PROTOCOL = TCP)(HOST = winxp-vm)(PORT = 1521))+
      +(CONNECT_DATA =+
      +(SERVER = DEDICATED)+
      +(SERVICE_NAME = orautf8)+
      +)+
      +)+

      I have already added winxp-vm to hosts file. I also tried to replace winxp-vm with localhost but no luck.

      Looking at the sqlnet.log file, I noticed the following:
      - If I keep HOST = winxp-vm, there are 4 times the following errors were written to the log file

      ***********************************************************************
      Fatal NI connect error 12203, connecting to:
      +(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle80)(ARGV0=oracle80ORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=C:\orant\BIN\ifrun60.EXE)(HOST=WINXP-VM)(USER=htb))))+
      VERSION INFORMATION:
      TNS for 32-bit Windows: Version 8.0.6.3.0 - Production
      Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 8.0.6.3.0 - Production


      - But in the tnsnames.ora, If I change *"winxp-vm" to "xyzwinxp-vm"* (a nonexistent host), there are *5 error entries in the log, 4 are same as above*, 1 is the following

      ***********************************************************************
      Fatal NI connect error 12545, connecting to:
      +(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xyzwinxp-vm)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orautf8)(CID=(PROGRAM=C:\orant\BIN\ifrun60.EXE)(HOST=WINXP-VM)(USER=htb))))+

      VERSION INFORMATION:
      TNS for 32-bit Windows: Version 8.0.6.3.0 - Production
      Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 8.0.6.3.0 - Production


      That means the Form does use file C:\orant\NET80\ADMIN\tnsnames.ora

      Could anybody tell me what could be wrong with my environment?

      Thanks.
      Bao.

      Edited by: user6281962 on Mar 7, 2013 3:55 AM

      Edited by: user6281962 on Mar 7, 2013 5:22 PM
        • 1. Re: Unable to connect Form 6i to Oracle database 10g
          HamidHelal
          post your tnsnames.ora file content from database.
          • 2. Re: Unable to connect Form 6i to Oracle database 10g
            995443
            File tnsnames.ora from database server is:

            TESTDB =
            +(DESCRIPTION =+
            +(ADDRESS_LIST =+
            +(ADDRESS = (PROTOCOL = TCP)(HOST = winxp-vm)(PORT = 1521))+
            +)+
            +(CONNECT_DATA =+
            +(SERVICE_NAME = orautf8)+
            +)+
            +)+

            File sqlnet.ora from database server is (just single line below):

            SQLNET.AUTHENTICATION_SERVICES = (NTS)

            I also noticed the following new things:
            - I launch the ifbld60.EXE (C:\orant\BIN\ifbld60.EXE)
            - On the welcome screen, I choose option "Build a new form manually"
            - Then, I select menu File > Connect...
            - The screen which is same when I run the Form application is displayed.
            - I filled 3 input fields with the same value as for the Form application (User name, password, database). Then I can login, I can browse database objects

            Could it be something missing to run the form application? It is compiled and I don't have its source code. That application runs on another machine. Just don't run on my machine. The application is run by the following command

            C:\orant\BIN\ifrun60.EXE C:\formapp\main.fmx

            Edited by: user6281962 on Mar 7, 2013 5:32 PM
            • 3. Re: Unable to connect Form 6i to Oracle database 10g
              user346369
              It may actually be the location of your tnsnames.ora file, rather than what is in it.

              I still use Forms 6i and Forms 10, and when I need to make a change to tnsnames.ora, I save it 4 places. One is a top-level easy to find folder, not used by any application. The other three are used by different versions of Forms or SQL Plus.

              From my notes stored within my tnsnames.ora file:

              <pre>#-- For SQL Plus10, save as: D:\Oracle\product\10.2.0\db_1\network\ADMIN\tnsnames.ora
              #-- For Forms6i, save as: C:\Program Files\Oracle\Forms6\NET80\admin\tnsnames.ora
              #-- For Forms10, save as: D:\DevSuiteHome_1\NETWORK\ADMIN\tnsnames.ora</pre>

              And by the way, I reformat the tnsnames text so it is much easier to read and change. All those nested bits and pieces make it crap to read. Here are several entries--note how I align the host/port/service_name -- the important items. I even keep the connect string including username/password in my copies since I need to connect to so many different databases.

              <pre>#--- connect USERXXX/passw***@uadb123
              UADB123 = (DESCRIPTION = (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)
              (Host = boxa.site1.com )
              (Port = 2002 )))
              (CONNECT_DATA = (SERVICE_NAME = uadb123 )))

              #-- Laptop Oracle 10: connect myuser/mypassword@orcl
              ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
              (HOST = 127.0.0.1)
              (PORT = 1521 )) (CONNECT_DATA = (SERVER = DEDICATED)
              (SERVICE_NAME = orcl )))</pre>
              • 4. Re: Unable to connect Form 6i to Oracle database 10g
                995443
                I verified location of the tnsnames.ora. They are:

                C:\orant\NET80\ADMIN\tnsnames.ora (this is for Form 6i)
                C:\data\oracle\product\10.1.0\db\NETWORK\ADMIN\tnsnames.ora (this is oracle 10g server)

                I don't have Form 10 installed. Both files are same content. So this would not be problem with the location of tnsnames.ora

                I made further investigation

                - First, I monitored the ifrun60.EXE process and noticed that it looked for the tnsnames.ora in the following location when I performed a login:

                C:\formapp\tnsnames.ora (my Form application folder)
                C:\orant\NET80\ADMIN\tnsnames.ora

                So, I copy the tnsnames.ora from C:\orant\NET80\ADMIN to C:\formapp and start the application again. But it is the same as before.

                - Then, I stop the listener windows service and perform a login, I see the following error in sqlnet.log

                -----
                Fatal NI connect error 12224, connecting to:
                (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=winxp-vm)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orautf8)(CID=(PROGRAM=C:\orant\BIN\ifrun60.EXE)(HOST=WINXP-VM)(USER=htb))))

                VERSION INFORMATION:
                     TNS for 32-bit Windows: Version 8.0.6.3.0 - Production
                     Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 8.0.6.3.0 - Production
                Time: 08-MAR-2013 15:57:38
                Tracing not turned on.
                Tns error struct:
                nr err code: 12224
                TNS-12224: TNS:no listener
                ns main err code: 12541
                TNS-12541: TNS:no listener
                ns secondary err code: 12560
                nt main err code: 511
                TNS-00511: No listener
                nt secondary err code: 61
                nt OS err code: 0
                -----

                With all of tries above, I would say that the Form application find the correct tnsnames.ora file and use it to send request to database server.

                Looking at the listener log file (after login when the listener service running), I saw the following:

                -----
                08-MAR-2013 16:32:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orautf8)(CID=(PROGRAM=C:\orant\BIN\ifrun60.EXE)(HOST=WINXP-VM)(USER=htb))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1299)) * establish * orautf8 * 0
                -----

                So the request from Form 6i arrived at TNS listener. I don't now what happen then...

                If in the login, I entered a dump user name or a dump password, I still saw the same line as above in TNS listener log, no error at all.
                • 5. Re: Unable to connect Form 6i to Oracle database 10g
                  user346369
                  Exactly what version of Forms 6i are you using: 6.0.8.*???*

                  Also, are you SURE you are not using the incompatible character set AL32UTF8 ?

                  Edited by: Steve Cosner on Mar 8, 2013 12:54 PM
                  • 6. Re: Unable to connect Form 6i to Oracle database 10g
                    995443
                    - Form version: from the about box of ifbld60.EXE
                    Forms [32 Bit] Version 6.0.8.25.2

                    - Server Character set: Output of the following query
                    SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

                    VALUE$
                    -------

                    UTF8

                    I have read from other posts saying that Form 6i cannot work with any AL32UTF8, only work with UTF8. Thus, when creating the database, I carefully chose UTF8.
                    • 7. Re: Unable to connect Form 6i to Oracle database 10g
                      user346369
                      Forms [32 Bit] Version 6.0.8.25.2
                      I'm not sure it will help, but I use Forms 6.0.8.27.0 and it connects to Oracle 10.2.0.3, 10.2.0.4

                      Patch 18 is what upgrades Forms 6i to 6.0.8.27

                      Not sure you can find it, though.
                      • 8. Re: Unable to connect Form 6i to Oracle database 10g
                        CraigB
                        Is this a new installation of Oracle RDBMS 10g and Forms 6i? If so, what order did you install them? Typically, you need to install the oldest Oracle product before installing newer products. It has to do with how well the older products support Oracle Homes.

                        I recommend you also look at your PATH environement variable and make sure your Oracle path entries are ordered with the oldest entries before the newer entries.

                        Hope this helps,
                        Craig...