Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Error While Transferring Between Oracle and MySQL

  1. #1
    Join Date
    Aug 2008
    Posts
    1

    Default Error While Transferring Between Oracle and MySQL

    'scuse the verbose title.

    Anyway, I'm trying to pull data from an Oracle database to a MySQL database. The process has two steps and a hop between them. The first step (called 'Get From Oracle') executes an SQL query on the Oracle DB, which makes several columns by summing columns in the DB and so on. There are about ten columns in the Oracle DB, and the query returns four. One column contains a date value, the others are all integers.

    The second step is an Insert/Update to the MySQL database, which has four columns (which are identical to the ones returned by the Oracle query; one date, four integers).

    Kettle/PDI can connect to both databases fine. I ran the Verify command and everything seemed alright. When I actually try to run the transformation, however, it goes like this:

    Code:
    ...
    - Spoon - Launching transformation [TransformationTest]...
    ...
    -TransformationTest - Initialising 2 steps...
    -Get From Oracle.0 - Starting to run...
    -Insert / Update.0 - Starting to run...
    -Insert / Update.0 - Unexpected error :
    -Insert / Update.0 - org.pentaho.di.core.exception.KettleDatabaseException:
    No fields in row, can't insert!
      (stack trace goes here)
    -Insert / Update.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    -Spoon - The transformation has finished!!
    -Get From Oracle.0 - Unexpected error :
    -Get From Oracle.0 - org.pentaho.di.core.exception.KettleDatabaseException:
    Couldn't get row from result set
    ORA-01013: user requested cancel of current operation
      (stack trace goes here)
    ...
    What exactly does this mean? I made sure, in the Insert/Update step, to click on the 'Get fields' and 'Get update fields' buttons (available by right-clicking on the step and selecting 'edit step'). The fields that I'm getting and setting showed up in both windows just fine, and Update was set to 'Y' for all of the fields being updated.

    The only thing I can think of is that the data takes a little while to pull from the Oracle database...perhaps it's trying to load data before it has anything to actually put in? Is there a way to stop that?

  2. #2
    Join Date
    Sep 2008
    Posts
    20

    Default

    I'm having this same problem after canceling a transformation once. I get the same error from any oracle database I connect to now and it cancels the entire transformation. Not sure how to approach this problem.

    Code:
    2009/01/29 02:40:45 - Album Ranges.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : An error occurred executing SQL: 
    2009/01/29 02:40:45 - Album Ranges.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : SELECT 
    2009/01/29 02:40:45 - Album Ranges.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : MIN(album_id) min_album_id, 
    2009/01/29 02:40:45 - Album Ranges.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : MAX(album_id) max_album_id, 
    2009/01/29 02:40:45 - Album Ranges.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : COUNT(*) batch_size 
    2009/01/29 02:40:45 - Album Ranges.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     FROM( SELECT TRUNC((ROWNUM-1)/5000) r, album_id FROM XMLGEN.INTERFACE_ALBUM_PACKET WHERE interface_type_id = 1)
    2009/01/29 02:40:45 - Album Ranges.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : GROUP BY r
    2009/01/29 02:40:45 - Album Ranges.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : ORA-01013: user requested cancel of current operation
    Any advice appreciated.
    Last edited by dyerrington; 01-28-2009 at 10:41 PM.

  3. #3
    Join Date
    Sep 2008
    Posts
    20

    Default

    Also, doing a "Verify" (F11) on my transform reports no errors.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Code:
    No fields in row, can't insert!
    It seems pretty clear to me.

  5. #5
    Join Date
    Sep 2008
    Posts
    20

    Default

    It seems that if you could:
    • Preview the results from all table inputs
    • Successfully run a verify

    this message wouldn't persist. The error message for ORA-01013 is described as:

    ORA-01013 user requested cancel of current operation

    Cause: The user interrupted an Oracle operation by entering CTRL-C, Control-C, or another canceling operation. This forces the current operation to end. This is an informational message only.
    Action:
    Continue with the next operation.


    I cancelled a transformation while it was running. Repeated attempts to run the same transformation, even on different database connections for the offending table input results in the same error. Could it be that it's caching somehow? Is there a way to delete any cache files that may be sticking somewhere? I tried unchecking the option "Use database cache?" but the problem remains. Opening and closing spoon doesn't seem to help (even overnight).

    Oracle docs suggest:
    The most common cause of ORA-01013 is timeouts. Timeouts can be a little more tricky to identify because they may not be specifically reported in connection with ORA-01013. . If a timeout is causing your ORA-01013 error in a SQL pass-through query, the properties in the timeout parameter should be altered to a higher value.
    Is there a way to do that?

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    ORA-01013 user requested cancel of current operation
    This has nothing to do with Oracle nor Kettle. Kettle simply canceled the query because you had an error elsewhere in the transformation.
    Didn't you even read my message?

    And yes, it is possible to preview data going through the steps, just try it I guess.

    Matt

  7. #7
    Join Date
    Sep 2008
    Posts
    20

    Default

    Quote Originally Posted by MattCasters View Post
    This has nothing to do with Oracle nor Kettle. Kettle simply canceled the query because you had an error elsewhere in the transformation.
    Didn't you even read my message?

    And yes, it is possible to preview data going through the steps, just try it I guess.

    Matt
    Thanks for responding Matt. While I read and understood your previous comment, it wasn't helpful in resolving my problem. The first step in the transformation (a table input to an Oracle database, executing a select statement) produces an ORA-01013 referencing the query that caused it (the query defined in the first step), which in a six step transformation, prevents the other 5 from performing. I've since exported and executed the exact same transformation, that consistently fails due to the ORA error, on another machine. Running the same transofmration on another machine, with the same version of PDI, runs without the ORA-01013 error stopping the entire transformation or showing up in the logs.

    My best guess is that the message from the database was cached on the originating install that produced this case and therefore persists beyond restarting Spoon and/or rebooting.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.