Oracle DBLink to any DB (SQL server, MySQL,…)

  1. Download the orajdbclink_o2a.zip (oracle-to-any) file: http://sourceforge.net/project/showfiles.php?group_id=205664
  2. unzip in on a computer that has an oracle client installed and can reach the target Oracle server or directly on the oracle server
  3. cd orajdbclink_o2a
  4. sqlplus “sys/<syspwd>@<connstr> as sysdba” @initoracletoany.sql #(when prompted for connstr write “@<your connection string>” or live it blank if you are on the server)
  5. Download Microsoft SQL Server JDBC Driver:

10g is JDK 1.4 compatible SQL Server 2005 JDBC Driver 1.2

Oracle 11g has JDK 5.0 SQL Server JDBC Driver 2.0 (sqljdbc.jar  for Oracle 11g)

http://msdn.microsoft.com/en-us/data/aa937724.aspx

http://forums.oracle.com/forums/thread.jspa?messageID=1767683

Install:

  1. loadjava -resolve -verbose -user orajdbclink_o2a/orajdbclink_o2a@<connstr> sqljdbcx.jar #(sqljdbcx.jar depends on JDBC driver)if you need:

    -synonym -grant PUBLIC

    loadjava -resolve -verbose -synonym -grant PUBLIC -user orajdbclink_o2a/orajdbclink_o2a@<connstr> sqljdbcx.jar

    Then grand Privileges: CREATE PUBLIC SYNONYM

    to “orajdbclink_o2a”

  2. Use you favorite tool to insert records in the JDBC_DBLINK table (

DATA_SORCE_NAME: a name for the remote database,

URL: the jdbc url,

DBUSER: the user for the remote database,

DBPASSWORD: the password for that user,

DRIVER: the jdbc driver class):

Microsoft SQL Server JDBC Driver:

DATA_SORCE_NAME: MYSQLSRV

URL: jdbc:sqlserver://myhost:1433;SelectMethod=Cursor;DatabaseName=mydatabase

jdbc:sqlserver://localhost\SQLEXPRESS:1433;SelectMethod=Cursor;DatabaseName=mydatabase

for SQLExpress

DBUSER: myuser

DBPASSWORD: mypwd

DRIVER: com.microsoft.sqlserver.jdbc.SQLServerDriver

net.sourceforge.jtds.jdbc.Driver:

DATA_SORCE_NAME: MYSQLSRV

URL: jdbc:jtds:sqlserver://myhost:1433/mydatabase

DBUSER: myuser

DBPASSWORD: mypwd

DRIVER: net.sourceforge.jtds.jdbc.Driver

Test:

— configure MYSQLSRV datasource with jtds jdbc driver (http://jtds.sourceforge.net/)

Insert into JDBC_DBLINK (DATA_SOURCE_NAME,URL,DBUSER,DBPASSWORD,DRIVER) values (‘MYSQLSRV’,’jdbc:jtds:sqlserver://mysqlsrv.mydomain:1433/mydatabase’,’myuser’,’mypwd’,’net.sourceforge.jtds.jdbc.Driver’);

commit;

— test jcursor
declare
v_cursor   orajdbclink_o2a.jcursor:= orajdbclink_o2a.jcursor(‘select col1, col2, col5 from sqlservertable’,’MYSQLSRV’,3);
begin

dbms_java.set_output(10000);

v_cursor.init;
v_cursor.open;

while v_cursor.dofetch = 1 loop
dbms_output.put_line(v_cursor.get_string(1));
dbms_output.put_line(v_cursor.get_string(2));
dbms_output.put_line(v_cursor.get_string(3));
end loop;

v_cursor.close;

exception
when others then
dbms_output.put_line(‘err: ‘||sqlerrm(sqlcode));
v_cursor.close;
end;

— test jcall
declare
v_call   orajdbclink_o2a.jcall:= orajdbclink_o2a.jcall(‘insert into sqlservertable (col) values (?)’,’MYSQLSRV’);
begin

dbms_java.set_output(10000);

v_call.init;
v_call.bind(1,’hello’);
v_call.executecall;
v_call.close;
exception
when others then
dbms_output.put_line(‘err: ‘||sqlerrm(sqlcode));
v_call.rollback;  — if something bad happens we rollback the jcall connection
v_call.close;
end;

— test transaction isolation
declare
v_call     orajdbclink_o2a.jcall;
v_cursor   orajdbclink_o2a.jcursor;
begin

dbms_java.set_output(10000);

— suppose “sqlservertable” to be empty
v_call:= orajdbclink_o2a.jcall(‘insert into sqlservertable (col) values (?)’,’MYSQLSRV’);
v_call.init;
v_call.bind(1,’hello’);
v_call.executecall;
v_call.close;
— actually v_call is not committed
v_cursor:= orajdbclink_o2a.jcursor(‘select col from sqlservertable’,’MYSQLSRV’,1);
v_cursor.init;
v_cursor.open;

while v_cursor.dofetch = 1 loop
dbms_output.put_line(v_cursor.get_string(1)); –this will print out a ‘hello’ because v_cursor uses the same jdbc connection
end loop;
v_cursor.close;

raise_application_error(-20002,’Something bad happens’);  — something bad happens, so v_call will be rolled back
— if we remove this line the connectionmanager will commit the
— transaction at the end of the pl/sql call (oracle.aurora.memoryManager.EndOfCallRegistry).
exception
when others then
dbms_output.put_line(‘err: ‘||sqlerrm(sqlcode));
v_call.rollback;  — if something bad happens we rollback the jcall connection
v_call.close;
end;

— test “distributed” transactions
declare
v_call     orajdbclink_o2a.jcall;
begin

dbms_java.set_output(10000);

— suppose “sqlservertable” to be empty
v_call:= orajdbclink_o2a.jcall(‘insert into sqlservertable (col) values (?)’,’MYSQLSRV’);
v_call.init;
v_call.bind(1,’hello’); — USE BIND VARIABLES !!!!
v_call.executecall;
v_call.close;
— actually v_call is not committed

insert into mytable values(1,2,3);

— NOTE: If somthing goes wrong before that commit all will goes fine: the local and the remote transaction
—       will be rolled back
commit;

— WARNING: if we loose the connection with the remote host here (between “commit” and “end”) we will lost the jcall transaction !!
—          SO USE IT AT YOUR OWN RISK
exception
when others then
rollback;
v_call.rollback;  — if something bad happens we rollback the jcall connection
v_call.close;
dbms_output.put_line(‘err: ‘||sqlerrm(sqlcode));
end;

— create a package for pipelined views in your application schema
— NOTE: first grant all on orajdbclink_o2a.jcursor to <your application schema>

create or replace
package MYSQLSRV as

type view_item_record is record
(
code                varchar2(255),
description         varchar2(2000)
);
type view_item_table is table of view_item_record;
function view_item
return view_item_table
pipelined;

end MYSQLSRV;
/

create or replace
package body MYSQLSRV as

function view_item
return view_item_table
pipelined
as
v_cursor              orajdbclink_o2a.jcursor:= orajdbclink_o2a.jcursor(‘select code, description from item_table’,’MYSQLSRV’,2); –define the cursor query
v_record              view_item_record;
begin

v_cursor.init; — open connection, and prepare query
v_cursor.open; — execute query

while v_cursor.dofetch = 1 loop — fetch query results into your view record
v_record.code:= v_cursor.get_string(1); — code
v_record.description:= v_cursor.get_string(2); — description
pipe row (v_record); — pipe row to the query
end loop;

v_cursor.close;  — close resources

exception
when others then — if something happens
v_cursor.close;  — close resources
raise; — raise the exception
end;

end mysqlsrv;
/

— test your new view

set serveroutput on;

begin
dbms_java.set_output(10000);
end;
/

— o yeah… look at this…
select * from table(mysqlsrv.view_item)

— now probably you can
—  1) use it like a normal view but it will be slooow to process where clauses
—  2) add a parameter where clause to the function to have the remote database ose indexes
—  3) create a materialized view on top of it index it and use it as you like

Gửi phản hồi

Please log in using one of these methods to post your comment:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s