To create a database link to MySQL, follow these steps:

Prerequisites:

1. Oracle Database: Ensure that Oracle Database is installed and running.
2. MySQL Database: Have access to a MySQL database.

Steps:

1. Install MySQL ODBC Driver:

   • Download and install the MySQL ODBC driver from the official MySQL website.
   • After installation, configure the ODBC driver in the ODBC Data Source Administrator (Windows).
     • Open ODBC Data Source Administrator (Control Panel > Administrative Tools).
     • Click on System DSN tab and then Add.
     • Select MySQL ODBC driver and configure the connection with MySQL credentials (host, port, database, username, password).
     • choose oproprate name for you DSN, here we use MYSQLDB. we should use this name in all configuration files.

2. Edit Oracle Configuration Files:

   • Locate and edit the Oracle tnsnames.ora and listener.ora files (usually found in C:\app\oracle\****\****\NETWORK\ADMIN\).

   • Add the MySQL connection in tnsnames.ora:

     MYSQLDB =
     (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = MachineName)(PORT = 1521))
     (CONNECT_DATA =(SID = MYSQLDB)   )
     (HS = OK)
     )

     p.s. the name of the service is the same as the DSN name you have created in step 1.

   • Add the MySQL listener in listener.ora:

     SID_LIST_LISTENER =
     (SID_LIST =
      (SID_DESC =
        (SID_NAME = MYSQLDB)
        (ORACLE_HOME = /path/to/oracle)
        (PROGRAM = dg4odbc)
      )
     )

   • Create initMYSQLDB.ora file in C:\app\oracle\****\****\HS\ADMIN\ with following content:

     HS_FDS_CONNECT_INFO = MYSQLDB
     HS_FDS_TRACE_LEVEL = OFF

   • Restart the Oracle listener:

     lsnrctl reload

     check the status of the listener:

     lsnrctl status

3. Create the Database Link in Oracle:

   • Open Oracle SQL Developer or any Oracle SQL client.
   • Create the database link using the following SQL command:

     CREATE DATABASE LINK mysql_link
     CONNECT TO "mysql_username"
     IDENTIFIED BY "mysql_password"
     USING 'MYSQLDB';

4. Test the Database Link:

   • Once the link is created, you can test it by running a query on the MySQL database through the database link:

     SELECT * FROM "mysql_table"@mysql_link;

If everything is configured correctly, Oracle should now be able to communicate with MySQL via the database link.