orajdbclink



What is ?



A simple and flexible way to get data from any jdbc reachable database.

For example you can easly create an oracle view (materialized or not), from

a query performed directly on a Microsoft SQL Server (for example, but may be MySQL, PostGre, or an old version of Oracle

like 7.3.4 no more reachable from dblinks) via JDBC.

You will end up doing something like:



SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 15 11:14:42 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Release 10.2.0.2.0 - Production



SQL> select code, description from table(mysqlsrv.view_item) where code= '001'

Where mysqlsrv.view_item is a pipe lined pl/sql function using orajdbclink api to return records:



-- 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;


function view_item_by_code(p_code varchar2)

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;


function view_item_by_code(p_code varchar2)

return view_item_table

pipelined

as

v_cursor orajdbclink_o2a.jcursor:= orajdbclink_o2a.jcursor('select code, description from item_table where code= ?','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

v_cursor.bind(1,p_code); -- bind code variable



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;

/



So you can:



--slow

SQL> select code, description from table(mysqlsrv.view_item) where code= '001'



--fast may use an index on remote database

SQL> select code, description from table(mysqlsrv.view_item_by_code('001'))



--faster: create a materialized view

SQL> create materialized view mv_item

refresh complete

as select * from table(mysqlsrv.view_item)

This will not work with Oracle XE because actually, it doesn't have the aurora internal JVM. (Java Option)

Any other database edition should work.



Why ?



This was born to make 10g talk with 7.3.4. But it can be used for any purpose.



You can:



1) use it like a normal view but it will be slooow to process where clauses

2) add a parameter to the function to have the remote database use indexes (see above)

3) create a materialized view on top of it, index materialized view columns, and use it locally



Features





User feedback will drive development, submit your requests:

http://sourceforge.net/tracker/?group_id=205664



Download



Source/Doc or Binary package:

http://sourceforge.net/project/showfiles.php?group_id=205664



or CVS (updated frequently):

http://sourceforge.net/cvs/?group_id=205664

Use it



  1. Download the orajdbclink_o2a.zip (oracle-to-any) file

  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. loadjava -resolve -verbose -user orajdbclink_o2a/orajdbclink_o2a@<connstr> jcifs-xx.jar jtds-xx.jar #(jtds.jar depends on jcifs.jar)

  6. 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):

    DATA_SORCE_NAME: MYSQLSRV

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

    DBUSER: myuser

    DBPASSWORD: mypwd

    DRIVER: net.sourceforge.jtds.jdbc.Driver

  7. test it:



set serveroutput on;

-- 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;




Any exception will be visible in the serveroutput.

You can use as many drivers as you want. All you need to do is to load the jar files of drivers and dependecy into orajdbclink_o2a schema.

NOTE: (example) the jtds driver doesn't work until you load the jcifs jar on wich it depends.



Those who want to use orajdbclink only oracle-to-oracle, can use

orajdbclink_o2o.zip to have some optimizations, and haven't to

load oracle jdbc drivers, because are already shipped in the oracle JVM.

Transactions



Actually there is no way to get a real distributed transaction, like a real oracle database link,

so speaking by example:



-- TEST 1: 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 2: no distributed transaction

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'); -- ALWAYS 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;





For those who wants to use oracle-to-oracle

  1. Download the orajdbclink_o2o.zip (oracle-to-oracle) file

  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_o2o

  4. sqlplus “sys/<syspwd>@<connstr> as sysdba” @initoracletooracle.sql #(when prompted for connstr write “@<your connection string>” or live it blank if you are on the server)

  5. Use you favorite tool to insert records in the JDBC_DBLINK table (DATA_SORCE_NAME: a name for the remote database, URL: an oracle jdbc url, DBUSER: the user for the remote oracle database, DBPASSWORD: the password for that user, EXPLICIT_CACHING: false (used only for testing), IMPLICIT_CACHING: false (used only for testing), ARRAYSIZE: how many rows to prefetch from server):

    DATA_SORCE_NAME: MY734

    URL: jdbc:oracle:thin:@m734server:1521:my734

    DBUSER: scott

    DBPASSWORD: tiger

    EXPLICIT_CACHING: false

    IMPLICIT_CACHING: false

    ARRAYSIZE: 500

  6. test it as of orajdbclink_o2a





Troubleshooting

If you get any other problem please submit a support request:

http://sourceforge.net/tracker/?group_id=205664



About

orajdbclink is developed by Andrea A.A. Gariboldi

mailto: andrea.gariboldi <at> gmail.com