Hitachi Vantara Pentaho Community Wiki

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 4.0

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 driver 10.0.2.4

One user reported problems with this driver and recommended to stay on version 10.0.2.3 or 10.0.2.2

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

When dealing with a Real Application Cluster or other complex failover oracle situations, you can do the following:

  • * Set to native (JDBC) connection type
  • * Leave hostname and port empty
  • * 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.