I have created a dblink of mysql database, and I can querry using sqlplus ,but when I querry using pl/sql ,errors ORA-28500 : information for connection to a non oracle system and ORA-02063.
the configuration list bellow:
*****/etc/odbc.ini****
[pms_system]
Driver = /usr/lib64/libmyodbc5.so
Description = MySQL Connector/ODBC 5.0 UNICODE Driver DSN
SERVER = 10.30.0.11
PORT = 3306
USER = inno_ocs
Password = ENFIip2018
Database = pms_system
OPTION = 3
SOCKET =
*********isql testing ************
[oracle@enfidb3 admin]$ isql -v pms_system
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
********$ORACLE_HOME/hs/admin/initpms_system.ora*****
HS_FDS_CONNECT_INFO = pms_system
HS_FDS_TRACE_LEVEL = on
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so#此处为ODBC manager而不是 connector
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR = UCS2
HS_IDLE_TIMEOUT =1440
HS_LONG_PIECE_TRANSFER_SIZE=1258291
HS_FDS_SQLLEN_INTERPRETATION=64 #此处要跟目标mysql的位数一致
set ODBCINI = /etc/odbc.ini
***************listener.ora*******
LISTENERODBC=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.6.12)(PORT=1522))))
SID_LIST_LISTENERODBC =
(SID_LIST =
(SID_DESC =
(SID\_NAME = togetherbs)
(ORACLE\_HOME = /u01/app/oracle/product/12.1.0/db\_1)
(PROGRAM = dg4odbc)
(ENVS=LD\_LIBRARY\_PATH=/u01/app/oracle/product/12.1.0/db\_1/lib:/usr/lib:/usr/local/lib:/usr/lib64)
)
(SID_DESC =
(SID\_NAME = pms\_system)
(ORACLE\_HOME = /u01/app/oracle/product/12.1.0/db\_1)
(PROGRAM = dg4odbc)
(ENVS=LD\_LIBRARY\_PATH=/u01/app/oracle/product/12.1.0/db\_1/lib:/usr/lib:/usr/local/lib:/usr/lib64)
)
)
******************LISTENERODBC status*************
[oracle@enfidb3 admin]$ lsnrctl status LISTENERODBC
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-NOV-2018 10:40:00
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.6.12)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENERODBC
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 12-NOV-2018 10:07:44
Uptime 0 days 0 hr. 32 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/PRD3_enfidb3/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/enfidb3/listenerodbc/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.6.12)(PORT=1522)))
Services Summary...
Service "pms_system" has 1 instance(s).
Instance "pms_system", status UNKNOWN, has 1 handler(s) for this service...
Service "togetherbs" has 1 instance(s).
Instance "togetherbs", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
******************tnsnames.ora******
pms_system = (DESCRIPTION =
(ADDRESS\_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.6.12)(PORT = 1522)))
(CONNECT\_DATA =(SID = pms\_system))
(HS = OK)
)
**************tnsing test *********
[oracle@enfidb3 admin]$ tnsping pms_system
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 12-NOV-2018 10:42:27
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/db_1/network/admin/PRD3_enfidb3/sqlnet_ifile.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.6.12)(PORT = 1522))) (CONNECT_DATA =(SID = pms_system)) (HS = OK))
OK (0 msec)
****************dblink create***********
create public database link topms_system connect to "inno_ocs" identified by "ENFIip2018" using 'pms_system';
**************error info******************************
