AnsweredAssumed Answered

Is it possible to execute a database procedure in JDBC connection details

Question asked by Ana Gonzalez on Apr 4, 2019
Latest reply on Apr 8, 2019 by Ana Gonzalez

I'll explain what I'm trying to achieve with PDI in case someone has done something similar and can give me a clue on how to do it.

In some of our integration processes we are using PDI to update Oracle tables which have a trigger to automatically inform the user doing the update, so the table has the normal columns, and  a column MODIFIED_BY which is informed using this trigger, even if you explicitly inform the column during the update, the trigger will overwrite the value.

This trigger reads the user from what in Oracle (I suposse in other databases is the same) is called a context, which is dependent of the session, and there is a package with procedures to set the user in the context, so when you use the normal SQL Plus session you do this:

BEGIN PKG_SET_CONTEXT.PROCEDURE_SET_USER('MYUSER'); END;
UPDATE MY_TABLE SET COLUMN1='WHATEVER' WHERE ...;

And since you are in the same session in SQL Plus when you perform the update, when afterwards you query MY_TABLE for the rows updated you see they have the column MODIFIED_BY with the value MYUSER.

 

Now, with PDI in a transformation using some step like the Update which connects to the database I don't see a way to execute the procedure automatically. Each step connecting to the database is a different session, so I can't use a step calling this DB procedure and afterwards use the Update step, or the Table output step, because they open a different session in the database.

I have tried setting up a new JDBC Connection and in the Advanced options, where you have the option to Enter the SQL statements, calling the procedure, but PDI gives an error: ORA-00900: invalid SQL statement, I have tried two ways:

BEGIN PKG_SET_CONTEXT.PROCEDURE_SET_USER('MYUSER'); END;

And

EXECUTE IMMEDIATE PKG_SET_CONTEXT.PROCEDURE_SET_USER('MYUSER');

Since I would like to do it for different processes, I would like that the user could be read from a parameter in the transformation, but since we don't need it for many processes, we could even hardcode the user if the parameter option is not possible.

 

Our PDI set up is quite simple, we are using the CE version, and don't have a cluster, it's just an unique deployment. I'm preparing this for the 8.2 version if possible.

Regards

Outcomes