This discussion is archived
4 Replies Latest reply: Jan 30, 2013 11:59 AM by jschellSomeoneStoleMyAlias RSS

Is there a JDBC driver to connect to Progress OpenEdge from Oracle

user12240205 Explorer
Currently Being Moderated
Hi,

We need to connect to a Progress OpenEdge DB from inside our Oracle DB. We need to connect inside PL/SQL.

We need to write a procedure, which is run as a job every 2 hours, which reads some Progress tables and transfers it to Oracle.

Can we do this using a JDBC Driver?

If so, what is the process of doing this??

Thanks in advance.
user12240205

Edited by: user12240205 on Jan 29, 2013 4:51 AM
  • 1. Re: Is there a JDBC driver to connect to Progress OpenEdge from Oracle
    EJP Guru
    Currently Being Moderated
    Can we do this using a JDBC Driver?
    Yes. Your PL/SQL will have to call a Java method, and there are various preconditions for that which you need to investigate.
    If so, what is the process of doing this?
    You need to do a lot of reading: about preloading Java dependencies into Oracle, about calling Java from PL/SQL, about how to write a Java method, and about how to use JDBC. Far too much to answer here. You also need to locate the JDBC driver for Progress OpenEdge, which will come from them.
  • 2. Re: Is there a JDBC driver to connect to Progress OpenEdge from Oracle
    user12240205 Explorer
    Currently Being Moderated
    EJP wrote:
    Can we do this using a JDBC Driver?
    Yes. Your PL/SQL will have to call a Java method, and there are various preconditions for that which you need to investigate.
    If so, what is the process of doing this?
    You need to do a lot of reading: about preloading Java dependencies into Oracle, about calling Java from PL/SQL, about how to write a Java method, and about how to use JDBC. Far too much to answer here. You also need to locate the JDBC driver for Progress OpenEdge, which will come from them.
    Hi, we have a Java team and they say they can create a Java class which will connect to the Progress DB using JDBC driver for Progress, read the tables, and connect to the Oracle DB and insert records to the tables.
    WE can pass the userid/pwd to the method.

    If this is taken care of by the Java team, we have to only wrap the java class in Oracle DB?

    Something like this:
    CREATE OR REPLACE PACKAGE BODY po_mgr AS
    PROCEDURE progress2oracle (progressuid VARCHAR2, progresspwd VARCHAR2,
    orauid VARCHAR2, orapwd VARCHAR2) AS LANGUAGE JAVA
    NAME 'POManager.getfromOpenEdge(java.lang.String, java.lang.String,
    java.lang.String, java.lang.String)';
  • 3. Re: Is there a JDBC driver to connect to Progress OpenEdge from Oracle
    rp0428 Guru
    Currently Being Moderated
    Whenever you post in this forum provide your full DB version(s), Java version and JDBC jar name and version.
    >
    We need to connect to a Progress OpenEdge DB from inside our Oracle DB. We need to connect inside PL/SQL.

    We need to write a procedure, which is run as a job every 2 hours, which reads some Progress tables and transfers it to Oracle.

    Can we do this using a JDBC Driver?
    >
    Why do you need to do this from inside Oracle? You CAN NOT use external libraries from within Oracle. You would have to load your 3rd party jar file into Oracle as well as any other 3rd party jars or classes that it needs for support. And Oracle 11g ships with, and requires, Java 1.5 for its own operation; you cannot use another version if you do this internally. Loading jar files and classes is overkill for what you need to do.

    Your requirement can be met very easily from the OS side.
    >
    Hi, we have a Java team and they say they can create a Java class which will connect to the Progress DB using JDBC driver for Progress, read the tables, and connect to the Oracle DB and insert records to the tables.
    WE can pass the userid/pwd to the method.
    >
    Perfect! Have them create a Java app that connects to your source and transfers data to Oracle. Schedule it in the OS to run every two hours.

    If they have any questions about the process see the JDBC Developer's Guide
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/toc.htm

    Using Oracle 11g you can even use DBMS_SCHEDULER to execute an external job. See the DBMS_SCHEDULER doc section that describes the parameters.

    >
    •'EXECUTABLE'

    This specifies that the job is a job external to the database. External jobs are anything that can be executed from the operating system's command line. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.
  • 4. Re: Is there a JDBC driver to connect to Progress OpenEdge from Oracle
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    rp0428 wrote:
    Perfect! Have them create a Java app that connects to your source and transfers data to Oracle. Schedule it in the OS to run every two hours.
    Or create a load file that can be loaded by the Oracle import tool.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points