Latest News

the latest news from our team

ODBC – Troubleshooting Oracle Connections

  • Procedure or function returning a ref cursor type
    • eFORMz will only accept a result set as input data from a SQL load/update call. A ref cursor is a pointer to a result set and does not actually contain data. When you pass this into eFORMz’s  SQL LOAD/UPDATE functions it will not produce an error because it has accepted the ref cursor’s input that has no input. Please refer to the following links.
    •  PL/SQL 101
    • How To Return A Result Set From SQL Server and Oracle

  • ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    ORA-02063: preceding line from sid_name

    This error occurs when dg4odbc (under 11g and later) is used without aODBC Driver Manager.
    funcname=SQLColAttributeW hoaerr:28500

  • ORA-28509: unable to establish a connection to non-Oracle system
    • Make sure the HOST parameter in the tnsnames.ora file is correct.
    • Make sure the PORT number is correct.
    • Make sure the SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA

  • ORA-28500: connection from ORACLE to a non-Oracle system returned this message:[Transparent gateway for ODBC][H001] The environment variable <HS_FDS_CONNECT_INFO> is not set.
    • Set HS_FDS_CONNECT_INFO in the hs{sid}init.ora file to the data source name.
      Example: HS_FDS_CONNECT_INFO = <ODBC DataSource Name>
    • Make sure the hs{sid}init.ora file exists in the ORACLE_HOME/hs/admin directory and has the same name as the SID in the LISTENER.ORA.
      Example: If SID=hsodbc in the listener.ora file, then the hs{sid}init.ora file would be named ORACLE_HOME/hs/admin/inithsodbc.ora

    • ORA-02019: connection description for remote database not found
      • Verify that the database link you are using exists.SQL> select db_link,host,created from user_db_links;DB_LINK
        ——————————————————————
        HOST
        ——————————————————————
        CREATED
        —————
        A.MINISOFT.LOCAL
        mscard
        22-MAY-03
      • If no records are found, confirm that the database link is accessible.
        SQL> select db_link,host,created from all_db_links;
        SQL> select db_link,host,created from dba_db_links;
        note: You may need to use PUBLIC or SHARED
        CREATE [SHARED][PUBLIC] DATABASE LINK link_name
        [CONNECT TO CURRENT_USER]
        [USING ‘connect_string’]CREATE [SHARED][PUBLIC] DATABASE LINK link_name
        [CONNECT TO user IDENTIFIED BY password]
        [AUTHENTICATED BY user IDENTIFIED BY password]
        [USING ‘connect_string’]

      • ORA-02068:  following severe error from HS
        • Verify the structure of the tnsnames.ora entry for hsodbc.MSCARD =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = oak)(PORT = 1521))
          )
          (CONNECT_DATA =
          (SERVICE_NAME = mscard)
          (SRVR = DEDICATED)
          )
          (HS = OK)
          )

      • ORA-12154: TNS:could not resolve service name

        • The name as tried by oracle could not be located in the TNSNAMES.ORA file. This may be do to a missing entry or incomplete resolution do to a problem in SQLNET.ORA and the default domain.
        • Invalid entry in tnsnames.ora, missing right parentheses.

         


      • ORA-28545: NCRO Failed to make RSLV connection

        • Verify the hostname in the tnsnames.ora file references your Oracle server.  This should not reference the HP3000.

         


      • ORA-28500: connection from ORACLE to a non-Oracle system returned this message:[Generic Connectivity Using ODBC][MiniSoft] [3kodbc.dll] In (NetConnect), error 0 Unknown Error – Connect(): Cannot resolve address () (SQL State: 08004; SQL Code: 0)This indicates that the driver was unable to find a server entry in the DSN or was unable to locate the DSN.
        • Check that the odbc.ini file referenced by init{sidname}.ora exists and is readable by the Oracle listener process.

      • If the init{sidname}.ora file cannot be found, the following message will be seen:SQL> select * from customer@a;
        select * from customer@a
        *
        ERROR at line 1:
        ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
        [Generic Connectivity Using ODBC][H006] The init parameter
        is not set. Please set it in init.ora file.
        ORA-02063: preceding 2 lines from A

      • Execution of the Oracle application “hsodbc” should return a banner. If you receive an error message, that the application cannot be run or has a problem with a required library, review your Oracle installation to ensure all components are correctly installed.$ hsodbcOracle Corporation — FRIDAY    SEP 26 2003 08:26:47.268Heterogeneous Agent based on the following module(s):
        – External Procedure Module
        – Transaction Module
        – SQL/PLSQL ModuleIf a library is missing you may see the followingSQL> select * from customer@a;
        select * from customer@a
        *
        ERROR at line 1:
        ORA-28509: unable to establish a connection to non-Oracle system
        ORA-02063: preceding line from AYour listener.log file will show:26-SEP-2003 08:31:13 * (CONNECT_DATA=(SID=mscard)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.23.118)(PORT=49349)) * establish * mscard * 12500
        TNS-12500: TNS:listener failed to start a dedicated server process
        TNS-12547: TNS:lost contact
        TNS-12560: TNS:protocol adapter error
        TNS-00517: Lost contact
        HPUX Error: 32: Broken pipe

Leave a Reply

Your email address will not be published. Required fields are marked *