I'm practicing Pentaho Spoon and I have 2 queries that use PL/SQL. The first query joins multiple tables in my Oracle database to calculate a value, then insert the value into a table in the database. The second query basically does the same thing, except it uses the value calculated by the first query.
When I build them into transformations in Pentaho, I use 2 components: Execute SQL Script and Table Input for each transformation. The query I made is placed inside Execute SQL Script. Finally, the job simply includes:
START -> first transformation -> second transformation -> SUCCESS.
I ran the job and it worked. However, I feel like this is such a plain way of building a job and I'm not sure if it is really how things should be done. Is what I did the right way? If it's not, please help me correct it.
Using Execute SQL Script in Pentaho Spoon to run PL/SQL is a common and valid approach, but whether it’s the "best" way depends on the specific requirements and the complexity of your transformations.
If you are using bind variables for your queries, then it is great for both Pentaho (because it is working), and for Oracle Database, which will make use of the in-memory query cache.
Your job is functioning correctly, so it’s a valid approach for your scenario.