如何将PLSQL脚本结果存储到Kettle转换变量中？

Got it, let's break down how to get that Statement variable from your PLSQL block into a Kettle transformation variable. Here's a step-by-step solution that works reliably:

Step 1: Modify Your PLSQL Block to Expose an Output Parameter #

Your current anonymous block just sets a local variable—we need to make that value accessible to Kettle by adding an OUT parameter. Update your code like this:

DECLARE
  Statement VARCHAR(2000);
BEGIN
  -- Keep your existing logic to generate the Statement here
  Statement := 'select * from Foo';
  
  -- Assign the value to the OUT parameter (the ? is a placeholder Kettle uses)
  ? := Statement;
END;

Step 2: Use the "Execute SQL Script" Step Instead of Table Input #

The Table Input step is designed to run SELECT queries and return result sets, but we need to capture a single variable value. The Execute SQL Script step is perfect for this:

• Drag the step into your transformation and select your target database connection.
• Paste the modified PLSQL code into the "SQL script" text area.
• Switch to the Parameters tab:
  • Click "Add" to create a new parameter.
  • Set Direction to Output, Type to VARCHAR, Length to 2000, and Target field name to something like generated_query (this is the field name Kettle will use to hold the value).

Step 3: Save the Field to a Kettle Transformation Variable #

Now that we have the value in a Kettle field, use the Set Variables step to move it into a transformation variable:

• Add the Set Variables step after the Execute SQL Script step.
• Set the Mode to Field to variable.
• For the mapping:
  • Source field: Select generated_query (the field from the previous step).
  • Target variable name: Enter the name of your existing Kettle variable (e.g., MY_DYNAMIC_STATEMENT).
  • Variable scope: Choose Transformation if you need to use it within this transformation, or Job if you want to pass it to parent/child jobs.

Alternative: Use a Result Set If You Prefer Table Input #

If you really want to stick with the Table Input step, you can adjust your PLSQL to return the Statement as a single-row result set:

DECLARE
  Statement VARCHAR(2000);
BEGIN
  Statement := 'select * from Foo';
  -- Return the statement as a row from DUAL
  OPEN ? FOR SELECT Statement AS dynamic_query FROM DUAL;
END;

Then configure the Table Input step to run this code, map the dynamic_query field, and follow the same Set Variables step above to store it as a Kettle variable.

Key Notes #

• Make sure your database driver supports PLSQL OUT parameters (Oracle's standard driver does this without issues).
• Double-check the variable scope—if you set it to Transformation, only steps after this in the same transformation will be able to access it.

内容的提问来源于stack exchange，提问作者Alexander Heim