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.