.::: Create Linked Server MSSQL to use Open Query (Oracle) insert, update, delete :::.

A. Preconfigurasi
1. Konfiguration ODBC Data Source (64)
No Need Configuration ODBC

2. create Linked Server
Server Objects -> Linked Servers -> New Linked Server
General
Linked Server: Oracle
Provider: Oracle Provider for OLE DB
Product name: Oracle
Data Source: localhost:1521/tka

Security (user Oracle) 
Be made using the seciroty context
remote login: aisyah
With password: []

Server Options
RPC & RPC Out: True

B. Select Query
1. Normal Query - insert
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-99','Barang - After Migration','BUAH',20);
insert into suplier(KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER) values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');
insert into customer(KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER) values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');
insert into pasok(KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK) values('PAS-E099','ELK-99','EJ-99','2002-01-01',8);
insert into pembelian(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN) values('BEL-E099','ELK-99','J-0099','2002-05-20',3);
select * from barang where KODE_BARANG ='ELK-99';
select * from suplier where KODE_SUPLIER ='EJ-99';
select * from customer where KODE_CUSTOMER ='J-0099';
select * from pasok where KODE_PASOK ='PAS-E099';
select * from pembelian where KODE_PEMBELIAN ='BEL-E099';

2. Open Query - insert
insert into openquery (Oracle, 'select KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG from barang') VALUES  ('ELK-99','Barang - After Migration','BUAH',20); 
insert into openquery (Oracle, 'select KODE_SUPLIER,NAMA_SUPLIER,ALAMAT_SUPLIER,KOTA_SUPLIER,TELEPON_SUPLIER from suplier') values ('EJ-99','Suplier - After Migration','JL THAMRIN 12','JAKARTA','(021) 850-2301');
insert into openquery (Oracle, 'select KODE_CUSTOMER,NAMA_CUSTOMER,ALAMAT_CUSTOMER,KOTA_CUSTOMER,TELEPON_CUSTOMER from customer') values('J-0099','Customer - After Migration','JL CIMANGGIS 34','JAKARTA','(021) 856-4209');
insert into openquery (Oracle, 'select KODE_PASOK,KODE_BARANG,KODE_SUPLIER,TANGGAL_PASOK,JUMLAH_PASOK from pasok') values('PAS-E099','ELK-99','EJ-99','2002-01-01',8);
insert into openquery (Oracle, 'select KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN from pembelian') values('BEL-E099','ELK-99','J-0099','2002-05-20',3);
-- or 
select * from openquery(Oracle,'select * from barang') where KODE_BARANG ='ELK-99';
select * from openquery(Oracle,'select * from suplier') where KODE_SUPLIER ='EJ-99';
select * from openquery(Oracle,'select * from customer') where KODE_CUSTOMER ='J-0099';
select * from openquery(Oracle,'select * from pasok') where KODE_PASOK ='PAS-E099';
select * from openquery(Oracle,'select * from pembelian') where KODE_PEMBELIAN ='BEL-E099';
-- or
select * from openquery(Oracle,'select * from barang where KODE_BARANG =''ELK-99''');
select * from openquery(Oracle,'select * from suplier where KODE_SUPLIER =''EJ-99''');
select * from openquery(Oracle,'select * from customer where KODE_CUSTOMER =''J-0099''');
select * from openquery(Oracle,'select * from pasok where KODE_PASOK =''PAS-E099''');
select * from openquery(Oracle,'select * from pembelian where KODE_PEMBELIAN =''BEL-E099''');

C. Update Query
1. Normal Query - update
update barang set nama_barang='Barang OpenQuery' where KODE_BARANG ='ELK-99';
select * from barang

2. Open Query - update
update openquery(Oracle,'select * from barang where KODE_BARANG =''ELK-99''') set nama_barang='Barang OpenQuery'  ;
select * from openquery(Oracle,'select * from barang where KODE_BARANG =''ELK-99''');

D. Delete Query 
1. Normal Query - delete
delete from pembelian where KODE_BARANG ='ELK-99';
delete from pasok where KODE_PASOK ='PAS-E099';
delete from barang where KODE_BARANG ='ELK-99';

2. Open Query - delete
delete from openquery (Oracle,'select * from barang') ;
delete from openquery (Oracle,'select * from barang ') where NAMA_BARANG='KOMPUTER' ;
--foreign key must be delete first before delete primary key
-- table pembelian
delete from openquery (Oracle,'select * from pembelian ') where KODE_BARANG ='ELK-99' ;
-- or
delete from openquery (Oracle,'select * from pembelian where KODE_BARANG =''ELK-99''')  ;
-- table pasok
delete from openquery (Oracle,'select * from pasok ') where KODE_PASOK ='PAS-E099' ;
delete from openquery (Oracle,'select * from pasok where KODE_PASOK =''PAS-E099''') ;
--foreign key must be delete first before delete primary key
delete from openquery (Oracle,'select * from barang ') where KODE_BARANG ='ELK-99' ;
select * from openquery(Oracle,'select * from barang where KODE_BARANG =''ELK-99''');