DRIVER CLASS: net.sourceforge.jtds.jdbc.Driver DRIVER LOCATION: Specify the location on your machine of the jar file containing the jTDS driver. JTDS is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000, 2005, 2008 and 2012) and Sybase Adaptive Server Enterprise (10, 11, 12 and 15). JTDS is based on FreeTDS and is currently the fastest production-ready JDBC driver for SQL Server and Sybase ASE. JTDS is 100% JDBC 3.0 compatible, supporting.

What is ?

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

For example you can easly create an oracle view (materialized ornot), 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:422007

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 functionusing orajdbclink api to return records:

--create a package for pipelined views in your application schema

--NOTE: first grant all on orajdbclink_o2a.jcursor to <yourapplication schema>

createorreplace

packageMYSQLSRV as

typeview_item_record is record

(

codevarchar2(255),

descriptionvarchar2(2000)

);

typeview_item_table istableof view_item_record;

functionview_item

returnview_item_table

pipelined;

functionview_item_by_code(p_code varchar2)

returnview_item_table

pipelined;

endMYSQLSRV;

/

createorreplace

packagebody MYSQLSRV as

functionview_item

returnview_item_table

pipelined

as

v_cursororajdbclink_o2a.jcursor:= orajdbclink_o2a.jcursor('selectcode, description from item_table','MYSQLSRV',2);--define the cursor query

v_recordview_item_record;

begin

v_cursor.init;-- open connection, and prepare query

v_cursor.open;-- execute query

whilev_cursor.dofetch = 1 loop -- fetch queryresults into your view record

v_record.code:=v_cursor.get_string(1); -- code

v_record.description:=v_cursor.get_string(2); -- description

piperow (v_record); --pipe row to the query

endloop;

v_cursor.close;-- close resources

exception

whenothers then-- if something happens

v_cursor.close;-- close resources

raise;-- raise the exception

end;

functionview_item_by_code(p_code varchar2)

returnview_item_table

pipelined

as

v_cursororajdbclink_o2a.jcursor:= orajdbclink_o2a.jcursor('selectcode, description from item_table where code= ?','MYSQLSRV',2);--define the cursor query

v_recordview_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

whilev_cursor.dofetch = 1 loop -- fetch queryresults into your view record

v_record.code:=v_cursor.get_string(1); -- code

v_record.description:=v_cursor.get_string(2); -- description

piperow (v_record); --pipe row to the query

endloop;

v_cursor.close;-- close resources

exception

whenothers then-- if something happens

v_cursor.close;-- close resources

raise;-- raise the exception

end;

endmysqlsrv;

/

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 fromtable(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'thave 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 forany purpose.

You can:

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

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

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

Features

• Enables to connect any jdbc reachable database to oracle

• Enables to query other databases from PL/SQL

• Enables to call remote stored procedures, or DML

• Enables to reach old versions of Oracle no more reachable by dblinks

User feedback will drive development, submit your requests:

Download

Source/Doc or Binary package:

or CVS (updated frequently):

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_cursororajdbclink_o2a.jcursor:= orajdbclink_o2a.jcursor('selectcol1, col2, col5 from sqlservertable','MYSQLSRV',3);

begin

dbms_java.set_output(10000);

v_cursor.init;

v_cursor.open;

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

endloop;

v_cursor.close;

exception

whenothers then

dbms_output.put_line('err:'||sqlerrm(sqlcode));

v_cursor.close;

end;

--test jcall

declare

v_callorajdbclink_o2a.jcall:= orajdbclink_o2a.jcall('insertinto 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

whenothers then

dbms_output.put_line('err:'||sqlerrm(sqlcode));

v_call.rollback;-- if something bad happens we rollbackthe 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 toload the jar files of drivers and dependecy into orajdbclink_o2aschema.

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

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

orajdbclink_o2o.zip to have someoptimizations, and haven't to

load oracle jdbc drivers, because arealready shipped in the oracle JVM.

Transactions

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

so speaking by example:

Cannot Load Jdbc Driver Class Net.sourceforge.jtds.jdbc.driver

--TEST 1: transaction isolation

declare

v_callorajdbclink_o2a.jcall;

v_cursororajdbclink_o2a.jcursor;

begin

dbms_java.set_output(10000);

--suppose 'sqlservertable' to be empty

v_call:=orajdbclink_o2a.jcall('insert intosqlservertable (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 fromsqlservertable','MYSQLSRV',1);

v_cursor.init;

v_cursor.open;

whilev_cursor.dofetch = 1 loop

dbms_output.put_line(v_cursor.get_string(1));--this will print out a 'hello' becausev_cursor uses the same jdbc connection

endloop;

v_cursor.close;

raise_application_error(-20002,'Somethingbad 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

whenothers then

dbms_output.put_line('err:'||sqlerrm(sqlcode));

v_call.rollback;-- if something bad happens we rollbackthe jcall connection

v_call.close;

end;

--TEST 2: no distributed transaction

declare

v_callorajdbclink_o2a.jcall;

begin

dbms_java.set_output(10000);

--suppose 'sqlservertable' to be empty

v_call:=orajdbclink_o2a.jcall('insert intosqlservertable (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

insertintomytable 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 thejcall transaction !!

--SO USE IT AT YOUR OWN RISK

exception

whenothers then

rollback;

v_call.rollback;-- if something bad happens we rollbackthe jcall connection

v_call.close;

Cannot Load Jdbc Driver Class Net Sourceforge Jtds Jdbc Driver Tomcat

dbms_output.put_line('err:'||sqlerrm(sqlcode));

end;

For those who wants to useoracle-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:

About

orajdbclink is developed by Andrea A.A. Gariboldi

mailto: andrea.gariboldi <at> gmail.com

• ⋅100分求《新手学HTML+CSS》的光盘内容
• ⋅运行时错误1004 类worksheet的paste方法无效
• ⋅去掉xml里的非法字符问题

Weblogic Cannot Load Driver Class Net.sourceforge.jtds.jdbc.driver For Datasource

Cannot Load Jdbc Driver Class 'net.sourceforge.jtds.jdbc.driver' Jmeter