Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Call Stored Procedure is not working

  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Default Call Stored Procedure is not working

    I try to archive some tables from production schema to archive schema. So I created a procedure as the following:

    ******************************************************
    CREATE OR REPLACE PROCEDURE ARCHIVE_TABLE IS
    last_archived date;
    stmt varchar(8000);
    Begin

    select max(end_time) INTO last_archived from PROD.DATALOAD_LOGS ;

    EXECUTE IMMEDIATE 'CREATE TABLE ' || 'PRODUCTION_' || to_char(last_archived, 'yyyyMMdd') || ' as select * from PROD.PRODUCTION' ;
    END ARCHIVE_TABLE;
    /
    ********************************************************

    I ran it in Toad, it worked fine. But when I try to run it using spoon thru 'Call DB Procedure', it doesn't work (no table is archived). But didn't give me any error message. I also tried to run the procedure using 'Execute SQL script' in spoon, not working either and not give any error message.

    Can somebody help? Thanks!!

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

    Default

    You have to trigger it by a RowGenerator e.g.

    RowGenerator (issuing 1 row) ----> Call DB proc


    Regards,
    Sven

  3. #3
    Join Date
    Jul 2007
    Posts
    4

    Default

    Thanks for your quick reply!

    I use kettle 2.4.0.. but I can't find the 'Row Generator' function. I can find 'Row Normaliser', 'Row denormaliser', 'Row flattener'. Is 'Row Generator' a function from 2.5.0?

    Thanks again!

  4. #4

    Default

    Hi,

    Sven talk about the Row generator step (you find it in the Input group steps)
    Call Db Step need to be trigered like this:

    Row generator-->Call Db

    Of course, you can put Call Db step after any input step.

    Rgds

    Samatar

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.