{"id":2259,"date":"2014-12-17T22:25:57","date_gmt":"2014-12-17T22:25:57","guid":{"rendered":"https:\/\/alt2.minisoft.com\/support\/?p=2259"},"modified":"2018-05-12T18:34:16","modified_gmt":"2018-05-12T18:34:16","slug":"odbc-troubleshooting-oracle-connections","status":"publish","type":"post","link":"https:\/\/c002.minisoft.com\/support\/odbc-troubleshooting-oracle-connections\/","title":{"rendered":"ODBC &#8211; Troubleshooting Oracle Connections"},"content":{"rendered":"<ul>\n<li>Procedure or function returning a ref cursor type\n<ul>\n<li>eFORMz will only accept a <strong>result set<\/strong>\u00a0as 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&#8217;s\u00a0 SQL LOAD\/UPDATE functions it will not produce an error because it has accepted the ref cursor&#8217;s input that has no input. Please refer to the following links.<\/li>\n<li>\u00a0<a href=\"https:\/\/community.oracle.com\/thread\/888365\">PL\/SQL 101<\/a><\/li>\n<li><a href=\"https:\/\/www.akadia.com\/services\/ora_return_result_set.html\">How To Return A Result Set From SQL Server and Oracle<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li><code><strong>ORA-28500: connection from ORACLE to a non-Oracle system returned this message:<br \/>\nORA-02063: preceding line from sid_name<\/strong><\/code><br \/>\nThis error occurs when dg4odbc (under 11g and later) is used without a<a href=\"http:\/\/minisoft.com\/pages\/middleware\/ODBC_UNIX\/odbc_for_oracle_11g.htm\">ODBC Driver Manager<\/a>.<br \/>\n<code><strong>funcname=SQLColAttributeW hoaerr:28500<\/strong><\/code><\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li><code>ORA-28509: unable to establish a connection to non-Oracle system<\/code>\n<ul>\n<li>Make sure the HOST parameter in the tnsnames.ora file is correct.<\/li>\n<li>Make sure the PORT number is correct.<\/li>\n<li>Make sure the SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li><code><code>ORA-28500: connection from ORACLE to a non-Oracle system returned this message:<\/code><\/code>[Transparent gateway for ODBC][H001] The environment variable &lt;HS_FDS_CONNECT_INFO&gt; is not set.\n<ul>\n<li>Set HS_FDS_CONNECT_INFO in the hs{sid}init.ora file to the data source name.<br \/>\nExample: HS_FDS_CONNECT_INFO = &lt;ODBC DataSource Name&gt;<\/li>\n<li>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.<br \/>\nExample: If SID=hsodbc in the listener.ora file, then the hs{sid}init.ora file would be named ORACLE_HOME\/hs\/admin\/inithsodbc.ora<\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li>ORA-02019: connection description for remote database not found\n<ul>\n<li>Verify that the database link you are using exists.<code>SQL&gt; select db_link,host,created from user_db_links;<\/code>DB_LINK<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<br \/>\nHOST<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<br \/>\nCREATED<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;<br \/>\nA.MINISOFT.LOCAL<br \/>\nmscard<br \/>\n22-MAY-03<\/li>\n<li><code><code>If no records are found, confirm that the database link is accessible.<br \/>\nSQL&gt; select db_link,host,created from all_db_links;<br \/>\nSQL&gt; select db_link,host,created from dba_db_links;<br \/>\nnote: You may need to use PUBLIC or SHARED<\/code><\/code>CREATE [SHARED][PUBLIC] DATABASE LINK link_name<br \/>\n[CONNECT TO CURRENT_USER]<br \/>\n[USING &#8216;connect_string&#8217;]CREATE [SHARED][PUBLIC] DATABASE LINK link_name<br \/>\n[CONNECT TO user IDENTIFIED BY password]<br \/>\n[AUTHENTICATED BY user IDENTIFIED BY password]<br \/>\n[USING &#8216;connect_string&#8217;]<\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li>ORA-02068:\u00a0 following severe error from HS\n<ul>\n<li>Verify the structure of the tnsnames.ora entry for hsodbc.<code>MSCARD =<br \/>\n(DESCRIPTION =<br \/>\n(ADDRESS_LIST =<br \/>\n(ADDRESS = (PROTOCOL = TCP)(HOST = oak)(PORT = 1521))<br \/>\n)<br \/>\n(CONNECT_DATA =<br \/>\n(SERVICE_NAME = mscard)<br \/>\n(SRVR = DEDICATED)<br \/>\n)<br \/>\n(HS = OK)<br \/>\n)<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li><code>ORA-12154: TNS:could not resolve service name<br \/>\n<\/code><\/p>\n<ul>\n<li>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.<\/li>\n<li>Invalid entry in tnsnames.ora, missing right parentheses.<\/li>\n<\/ul>\n<p>&nbsp;<\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li><code>ORA-28545: NCRO Failed to make RSLV connection<br \/>\n<\/code><\/p>\n<ul>\n<li>Verify the hostname in the tnsnames.ora file references your Oracle server.\u00a0 This should not reference the HP3000.<\/li>\n<\/ul>\n<p>&nbsp;<\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li><code>ORA-28500: connection from ORACLE to a non-Oracle system returned this message:<\/code>[Generic Connectivity Using ODBC][MiniSoft] [3kodbc.dll] In (NetConnect), error 0 Unknown Error &#8211; 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.\n<ul>\n<li>Check that the odbc.ini file referenced by init{sidname}.ora exists and is readable by the Oracle listener process.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li>If the init{sidname}.ora file cannot be found, the following message will be seen:SQL&gt; select * from customer@a;<br \/>\nselect * from customer@a<br \/>\n*<br \/>\nERROR at line 1:<br \/>\nORA-28500: connection from ORACLE to a non-Oracle system returned this message:<br \/>\n[Generic Connectivity Using ODBC][H006] The init parameter<br \/>\nis not set. Please set it in init.ora file.<br \/>\nORA-02063: preceding 2 lines from A<\/li>\n<\/ul>\n<hr size=\"1\" \/>\n<ul>\n<li>Execution of the Oracle application &#8220;hsodbc&#8221; 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 &#8212; FRIDAY\u00a0\u00a0\u00a0 SEP 26 2003 08:26:47.268Heterogeneous Agent based on the following module(s):<br \/>\n&#8211; External Procedure Module<br \/>\n&#8211; Transaction Module<br \/>\n&#8211; SQL\/PLSQL ModuleIf a library is missing you may see the followingSQL&gt; select * from customer@a;<br \/>\nselect * from customer@a<br \/>\n*<br \/>\nERROR at line 1:<br \/>\nORA-28509: unable to establish a connection to non-Oracle system<br \/>\nORA-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<br \/>\nTNS-12500: TNS:listener failed to start a dedicated server process<br \/>\nTNS-12547: TNS:lost contact<br \/>\nTNS-12560: TNS:protocol adapter error<br \/>\nTNS-00517: Lost contact<br \/>\nHPUX Error: 32: Broken pipe<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Procedure or function returning a ref cursor type eFORMz will only accept a result set\u00a0as 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&#8217;s\u00a0 SQL LOAD\/UPDATE functions it will not produce an error because it [&hellip;]<\/p>\n","protected":false},"author":75,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[54,63],"tags":[],"_links":{"self":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/2259"}],"collection":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/users\/75"}],"replies":[{"embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/comments?post=2259"}],"version-history":[{"count":6,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/2259\/revisions"}],"predecessor-version":[{"id":6729,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/2259\/revisions\/6729"}],"wp:attachment":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/media?parent=2259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/categories?post=2259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/tags?post=2259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}