Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

How to run pl/sql function when request fields are updated?

SOLVED
Go to solution
Highlighted
roxor
Member

How to run pl/sql function when request fields are updated?

Hello,

how can I run pl/sql function each time when request fields are updated?

I have created simple request. Then I have pl/sql function.

What I need:
When I update request fields via web service I need to run this pl/sql function each time.

How to do it? It has to be automatic without clicking on any buttons. I have tryied to use workflow with only one step - execution of pl/sql function but this is not working.

Please tell how to create right workflow for this purpose?
Or how to run pl/sql function from request rule mapped to change of field?

Thanks a lot.
r.
7 REPLIES
Andrea Loi
Member

Re: How to run pl/sql function when request fields are updated?

Execution Type: PL/SQL Function
Validation WF - Standard Execution Results
Processing Type: Immediate
Execution: Your_Function([Your_Token],[Your_Token])
Your_Function should return one of the validation values (I remember "Success" or "Failure").

I am afraid you need at least two workflow steps: one execution and one decision to go to after execution.

roxor
Member

Re: How to run pl/sql function when request fields are updated?

Hi Andrea. Thanks a lot for you reply.

Sorry but I still didn't get it:)

So your opinion is that Step 1 should be execution, Step 2 should be decision?

How I understand this for my own:
If I add decision as Step 1, it could be for example Execution button in request. When user clicks on this button, workflow goes to Step 2 - Execution of my pl/sql function.

Problem is that I need this without interaction of user. Execution has to be automatic related to updated request field via web service. When is field changed, we want to run pl/sql function related to this field.

I didn;t get from your response how to link execution to update of field in request and how to do it without user interaction.

Thanks in advance for any suggestions.
r.
Abdullah Rafiq
Regular Collector

Re: How to run pl/sql function when request fields are updated?

You can also create a rule in the request type that gets triggered when a value in the field changes.

your rule would be something like
select function_name(pass_value), function_name(pass_value) from dual

this would run the function and it would pass back a value which you can store into another field.

keep in mind that a function can only return 1 value.
Abdullah Rafiq
Regular Collector

Re: How to run pl/sql function when request fields are updated?

hey I just saw your latest post saying that this should be able to be triggered via web services. I haven't tried this through web services. so i am not sure if my proposed solution will work.
roxor
Member

Re: How to run pl/sql function when request fields are updated?

Hello Abdullah,

thanks for response. I was hoping that this is the way but now I found out that rule is not good solution. It is due to restrictions when you call function in select sql statement. My function is updating database tables and it seems to be invalid operation for calling from select statement.

So it seem that I need to create workflow with execution step which call this function. But I don't understand how to run this when request is updated -> when is something changed in request - for example one field.
Andrea Loi
Member
Solution

Re: How to run pl/sql function when request fields are updated?

Hi roxor,

what about executing workflow transition from webservices?
See executeWorkflowTransition on Web Services Programmerâ s Guide.

Or creating a DB trigger?
In this case you should have a good documentation, to remove the trigger before next PPM upgrade.

My last idea is to prepare a scheduled job that reads updated requests and executes the DB function.
A rule could fill another field like "ThisRequestShouldBeUpdated".

Have a good day
Andrea


roxor
Member

Re: How to run pl/sql function when request fields are updated?

Thanks for your reply Andrea.

Executing workflow transition from webservices perfectly suits my needs in this case.

I have used executeWFTransitions because I am working with request right now and it is working exactly as I need. Thank you very much.

Also thanks for all other suggestions I have learned a lot with their help.
//Add this to "OnDomLoad" event