Issues with Oracle Connector and/or Oracle Extracts

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Issues with Oracle Connector and/or Oracle Extracts

      Hi everyone,

      we are in the process of migrating from MySQL to Oracle in our backend, and I am testing the connection to Oracle.
      I stumpled upon a few weird issues when extracting.

      1. I used often a ";" at the end of the queries as a delimiter, since I tested the queries in the MySQL Workbench and now Oracle Developper respectively.
        With MySQL there was no issue. When using the same query to an Oracle Connection it results in an error Error in source e_rel_D_PROJ: ORA-00933: SQL command not properly ended
        I would consider this as a bug.
      2. I work a lot with variables that are set with a groovy script and used in the relational extract to extract data of only certain dates for example.
        Here I get problems with dates. With the MySQL Connector there was no issue, that the variable was a string and the SQL correctly showed the dates matching the string.
        With Oracle there are two issues.
        1. String Variable does not work. I have to put a TO_DATE() in front of it, which causes a lot of extra tiny work steps to adjust all the relational extracts
        2. For some reason the dates result in a different format. So far it was always YYYY-MM-DD. Now it results in DD-MM-YY
      Did anyone of you come across the same issues? Does someone have recommendations on this?

      Unfortunately I did not find anything on this so far.

      Thanks a lot in advance.

      BR
      Bjoern

    • Hi Bjoern,

      1)

      I have same problem with end of request, I can't used ";" but without ";" my request works in Jedox.

      2)

      For the date, I use native function from oracle

      example:

      SELECT TO_CHAR(SYSDATE, 'dd-MM-YYYY')
      from YOUR_TABLE

      result => 01-07-2020 or with 'YYYYMM' => 202007

      I don't know if I help you with that.


      Regards

      The post was edited 2 times, last by belgarion ().

    • Thanks Belgarion,

      I assume that the oracle connector in jedox is not equally developped as the MySQL-Connector for example.
      And I guess that I need to specify jdbc parameters in the connection. But there is no documentation on this whatsoever by jedox

      NLS_DATE_FORMAT with YYYY-MM-DD and NLS_LANG with UTF-8 did not help so far.