Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Using a stored procedure in transformation

  1. #1
    Join Date
    Jan 2015
    Posts
    27

    Default Using a stored procedure in transformation

    Hello!
    I am trying to convert a stored procedure written in oracle in a transformation. The stored procedure consists of vairable declarations and SQL code consists of an insert into statment which inserts about 300 columns into a table, and then a select statement which selects same number of columns to extract from different tables and insert into the previously mentioned table.

    INSERT INTO EQTY_GTR_POSITION_PSA_R
    (
    PSA_POSITION_ID,
    ...........................
    ...........................
    ..............................
    )
    SELECT
    PSA_POSITION_ID,
    ................
    .................
    ..................
    FROM EQTY_GTR_POSITION_PSA

    WHERE MOST_RECENT_POSITION_FL <> 'N'
    AND (MOST_RECENT_POSITION_FL = 'Y' OR UPDATE_TS > FROMTSTAMP.

    where FROMTSTAMP is a defined variable. When i run this transformation as putting select code in an input table and insert into code in an out table. I get the error, FROMTSTAMP is an invalid idenitifer. Any idea how i can define the definition of FROMTSTAMP in the transformation?

  2. #2
    Join Date
    Apr 2012
    Posts
    253

    Default

    You could just call the stored procedure from PDI. otherwise you need to replace FROMTSTAMP with ? and send FROMTSTAMP in as a variable.

  3. #3
    Join Date
    Jan 2015
    Posts
    27

    Default

    But wont calling the stored procedure from PDI be same as running it from Oracle? I mean what would the difference in performance then? Would calling it from PDI make it run faster?

  4. #4
    Join Date
    Apr 2012
    Posts
    253

    Default

    No. Calling the stored procedure in Oracle will be much faster. The database does data things, PDI orchestrates.

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.