Hitachi Vantara Pentaho Community Wiki
Skip to end of metadata
Go to start of metadata

OCI

OCI uses the Oracle client installed on the client you're currently using. If you are using OCI and an Oracle Net8 client, the JDBC driver version used in Kettle needs to match your Oracle client version. PDI 2.5.0 shipped with version 10.1, 3.0.0 ships with version 10.2. You can either install that version of the Oracle client or change the JDBC driver in PDI if versions don't match up.

This is how you change the Oracle JDBC driver in Kettle. Replace files "ojdbc14.jar" and "orai18n.jar" in the directory libext/JDBC of your distribution with the files found in the $ORACLE_HOME/jdbc directory on your server or if the versions are different, with the JDBC driver that matches your Net8 client. For Oracle 11g the drivers are named ojdbc5.jar and ojdbc6.jar.

If you still have issues please remember that the DLL that Oracle uses to connect has to be in your path. If all else fails, try copying the ocijdbc10.dll (might be called different in different versions) to the libswt/win32 folder (or win64).

Connecting to an older 7.3 version

This forum thread offers insights.  Apparently the trick includes installing the 8.1.7 JDBC driver. This is the last version that has support for the 7.3 series.

Issues with JDBC drivers

One user reported problems with the JDBC driver 10.0.2.4 and recommended to stay on version 10.0.2.3 or 10.0.2.2. Unfortuneatly there are concurring issues with the JDBC 10.2.0.4 driver against the JDBC 10.2.0.2 driver that is actually shipped with PDI.
Please see http://jira.pentaho.com/browse/PDI-1205 about the backdraw using JDBC 10.2.0.4 driver (issues with batch commits) and http://jira.pentaho.com/browse/PDI-3964 about the backdraw using JDBC 10.2.0.2 driver (loosing precision in certain circumstances).

In general we recommend to use the JDBC driver that is suitable for your database version. Please replace the JDBC driver in your distribution folder under libext/JDBC. JDBC drivers are downloadable from http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

PL/SQL scripts in Execute SQL Script step

PL/SQL scripts are not supported in the task Execute SQL Script step. But they can be called as desribed in the next section.

Calling Functions or Procedures in a SQL script

When you need to call functions or procedures (for instance the Call DB Procedure step would not be suitable when you need to return a result set / REF CURSOR, see PDI-200), then you can use the own Oracle syntax with the dummy table DUAL:

SELECT function(your parameters) as xyz FROM DUAL;

RAC (Real Application Cluster)

When dealing with a Real Application Cluster or other complex failover oracle situations, please define the connection like this:

  • * Set to native (JDBC) connection type
  • * Leave hostname and port empty (this is also working with a port setting of -1)
  • * Set the database name to something like this...
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host1-vip)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = host2-vip)(PORT = 1521))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = db-service)(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))

or (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARY_NODE_HOSTNAME)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=SECONDARY_NODE_HOSTNAME)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DATABASE_SERVICENAME)))

or (DESCRIPTION=(FAILOVER=ON)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx)(PORT=1526))(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1526)))(CONNECT_DATA=(SERVICE_NAME=somesid)))

Note: This does only work with a repository based system until 3.0.4. Beginning with 3.0.5 and 3.1 file based systems do also support this.

Performance Considerations: Standard Fetch Size and Row Prefetching

In case you have a slow connection and/or your latency is high (e.g. via a VPN), prefetching data into the client reduces the number of round trips to the server and you can specify the number of rows to fetch with each trip.

Set the defaultRowPrefetch and fetchSize properties in the database connection dialog.

For more information, see Oracle Row Prefetching and Fetch Size in the JDBC Developer's Guide and Reference.

  • No labels

4 Comments

  1. user-15066

    If you use function instead of stored procedure and want to use DML statements, use statement PRAGMA AUTONOMOUS TRANSACTION in declaration section of your function:

    FUNCTION MYPROC RETURN VARCHAR2
    IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN

      -- your code here

      COMMIT;
      RETURN NULL;
    END;

    and you can call your "procedure" in Execute SQL script transformation with

    select
      MYSCHEMA.MYPROC() as x
    from
      dual;

  2. user-99042

    There's a problem with this step when using RAC:

     Leave hostname and port empty (this is also working with a port setting of -1)

    If you enter hostname with a variable and this variable is an empty string, Oracle returns a "NL Exception was generated" error.

    If you have the connection string in a xml file (for example) so one set of Jobs can work with several connections, this error can be fatal.

  3. The hostname with variable issue will be addressed by  http://jira.pentaho.com/browse/PDI-9231

  4. user-7ae51

    If these suggestions don't work, what are the log files that would help debug the issue?

    I can connect to an Oracle RAC database through other tools, so issues of access and credentials are resolved, and need to be translated to the right command strings in Pentaho. Where do the errors get logged?