Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Problem with stored Procedure in Oracle

  1. #1
    Join Date
    Aug 2007
    Posts
    9

    Default Problem with stored Procedure in Oracle

    Hi,
    I have a problem with calling a stored procedure from kettle.
    I have a test procedure like
    create Or replace procedure testProc
    IS
    Begin
    DBMS_output.put_line('Hello');
    End;
    Which works fine with 'Call testProc()' and 'execute testProc' in Sql*Plus.
    The Procedure is also valid.
    When I try to start the procedure from kettle using the 'Call DB Procedure' step or the 'execute SQL script' step I get the error message:
    ...
    2007/08/03 12:47:26 - Call DB Procedure.0 - Because of an error, this step can't continue:
    2007/08/03 12:47:26 - Call DB Procedure.0 - Unable to call procedure
    2007/08/03 12:47:26 - Call DB Procedure.0 - [Oracle][ODBC][Ora]ORA-06550: line 1, column 13:
    PLS-00201: identifier 'TESTPROC' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    ...
    my transformation looks like :
    'Get rows from result' --> 'Call Db Procedure'

    Does anybody has a hint how to fix this error?
    Thanks a lot for your help.
    Regards
    Judith

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    You're running the transformation using a different database user than you used for creating the test proc?

    I also remember some changes in the post 2.5 era to that step.

    Regards,
    Sven

  3. #3
    Join Date
    Aug 2007
    Posts
    9

    Default

    Hi Sven,

    no it is the same user - the owner of the procedure. I'm using kettle 2.5.0. I've read the threads about this topic and I got the patch with the changes for this step.

    Any other idea?

    Regards
    Judith

  4. #4
    Join Date
    Aug 2007
    Posts
    9

    Default

    Hi again ,

    I think I found the source of my problem. We moved our database couple days ago and the DSN pointed still to the old database (where the procedure don't exist). I changed it and I got a little surprise:

    The procedure works fine in the step 'Execute SqL script' (Call testProc() But I got an Error when I try to run the procedure with 'Call DB Procedure' as followed

    2007/08/03 13:35:28 - Get rows from result.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2007/08/03 13:35:28 - Call DB Procedure.0 - Because of an error, this step can't continue:
    2007/08/03 13:35:28 - Call DB Procedure.0 - Unable to prepare database procedure call
    2007/08/03 13:35:28 - Call DB Procedure.0 - [Oracle][ODBC]Syntax error or access violation.


    But I can also use the 'Execute Sql script' step

    Thanks for your help

    Regards
    Judith

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.