0

I have an Oracle 18c database. From this DB I need to create a database link to a Progress/Openedge 11.7 database to retrieve some data.

I am somewhat familiar with Heterogeneous Services which uses ODBC drivers to connect to other data sources. But at present all I have is the JDBC driver openedge.jar. I am able to use this jar file to connect to the Openedge DB from DBeaver without a problem.

Is it possible to create a DBLink using this JAR file?

Paul Stearns
  • 856
  • 9
  • 30
  • It is not possible to create a database link using JDBC. – pmdba Mar 31 '22 at 00:57
  • I was afraid of that. You would think that since Oracle owns Java, and uses it a lot in their DB, they would have made this work. – Paul Stearns Mar 31 '22 at 02:07
  • Oracle *uses* Java (or perhaps more accurately allows Java to be used by providing a JVM), but the database engine and its networking stack are written in C. As @JustinCave pointed out, there's likely enough support for Java for you to create your own pseudo JDBC capability, but it wouldn't work the same as a database link, wouldn't be supported by Oracle, and wouldn't be a trivial exercise. – pmdba Mar 31 '22 at 02:50

1 Answers1

0

You can't create a database link.

You can, most likely, load the JAR file in the database and then create Java stored procedures that make connections to the remote database, issue queries, etc. That's not going to allow you to write queries that join arbitrary local and remote tables together but you could build that infrastructure. For example, you could build pipelined table functions that call Java stored procedures to fetch data from a remote database and then use those pipelined table functions in arbitrary queries that join to local tables.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Can you point me to documentation on how to do this? The good news is I don't need to do joins, but the table function idea is a good one. My main purpose is to easily copy data and occasionally query data from the Progress/Openedge database. – Paul Stearns Mar 31 '22 at 12:34
  • @PaulStearns - Here's a quick overview on building Java stored procedures https://docs.oracle.com/cd/B19306_01/java.102/b14187/chfive.htm It walks through using `loadjava` to load external Java files- you can use that to load your JAR file. – Justin Cave Mar 31 '22 at 17:01