Connect Sql server to Oracle Database using ODBC

===========Oracle Database Gateways=======

To access non Oracle DB systems we need to use Oracle Heterogeneous Services.
Oracle Database Gateways Provide Heterogeneous data access.
Oracle Database Gateways provide the ability to transparently access data residing
in a non Oracle systems from an Oracle Environment

Steps 1: Download and install Oracle database gateway.

Oracle Database 19c Download for Microsoft Windows x64 (64-bit)

Step 2: Create new Listener_ODBC installed software

----done
Create ODBC data source.
---name mytestodbc

Configure initdg4odbc.ora file in oracle gateway home.
---gateway home : C:\app\tg\Ekramul\product\19.0.0\tghome_1\hs\admin
copy initdg4odbc.ora and past with a new name

HS_FDS_CONNECT_INFO = mytestodbc
HS_FDS_TRACE_LEVEL = OFF

Configure listener.ora in oracle gateway home.

---add below lines

SID_LIST_LISTENER_ODBC =
(SID_LIST =
(SID_DESC =
(SID_NAME = mytestodbc)
(ORACLE_HOME = C:\app\tg\Ekramul\product\19.0.0\tghome_1)
(PROGRAM = dg4odbc)
)
)

Configure tnsname.ora in oracle database home.
---oracle 19c database tns file
---add below lines

mytestodbc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-53PA6MR)(PORT = 1525))
(CONNECT_DATA =(SID=mytestodbc))
(HS=OK)
)

now restart services

lsnrctl reload or from services
now check from cmd by tnsping
My 19c database tns name is : orcl
My 19c gateway database tns name is : mytestodbc

tnsping orcl
tnsping mytestodbc
export environment
set oracle_home=E:\app\19c
set oracle_sid=orcl

Step 3: Create a database link to connect with the MSSQL server ODBC source
sqlplus / as sysdba
create database link mylink01 connect to "sa" identified by "sa" using 'mytestodbc';
select * from test_tbl@mylink01;

SQL> select * from emp@mylink01;

select * from emp@mylink01

                  *

ERROR at line 1:

ORA-28545: error diagnosed by Net8 when connecting to an agent

Unable to retrieve text of NETWORK/NCR message 65535

ORA-02063: preceding 2 lines from MYLINK01

Solution: reconfigure listener_odbc

SQL> select * from emp@mylink01;

emp_id    emp_name   Date_time

----------  --------------   -------------

10            LIMA            21-AUG-23

20          Abdul Quium  21-AUG-23

PIVOT in Oracle Database

 CREATE TABLE pivot_test (

  id            NUMBER,

  customer_id   NUMBER,

  product_code  VARCHAR2(5),

  quantity      NUMBER

);

INSERT INTO pivot_test VALUES (1, 1, 'A', 10);

INSERT INTO pivot_test VALUES (2, 1, 'B', 20);

INSERT INTO pivot_test VALUES (3, 1, 'C', 30);

INSERT INTO pivot_test VALUES (4, 2, 'A', 40);

INSERT INTO pivot_test VALUES (5, 2, 'C', 50);

INSERT INTO pivot_test VALUES (6, 3, 'A', 60);

INSERT INTO pivot_test VALUES (7, 3, 'B', 70);

INSERT INTO pivot_test VALUES (8, 3, 'C', 80);

INSERT INTO pivot_test VALUES (9, 3, 'D', 90);

INSERT INTO pivot_test VALUES (10, 4, 'A', 100);

commit;

SELECT *

FROM   (SELECT product_code, quantity

        FROM   pivot_test)

PIVOT  (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c));