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

Is it possible to use sql UPDATE statement in the execution steps?

SOLVED
Go to solution
Highlighted
kndlny
Regular Collector

Is it possible to use sql UPDATE statement in the execution steps?

Hi,

 

Does anyone know whether it is allowed to use sql "UPDATE" statement in the execution steps by selecting "SQL Statement" as an Execution Type?

 

The same sql statement works fine in oracle sql developer, but when I use it in the executions step, it will produce ORA-01007 error.

Am I supposed to define variables or add something else instead of writing a regular update statement?

 

Here is my query:

 

UPDATE table_name

SET parameter_name = '[TOKEN_NAME]'

WHERE condition

 

Thanks

8 REPLIES
Utkarsh_Mishra
Honored Contributor
Solution

Re: Is it possible to use sql UPDATE statement in the execution steps?

Hi,

 

You can use PL/SQL function types execution step.

 

In this call the function, which contain your update step.

 

ex. Function_name('[TOKEN_NAME]')

 

or can use SQL type execution step and can call the function like

 

ex. SELECT FUNCTION_NAME('[TOKEN_NAME]') FROM DUAL

 

In both case you need to define your code inside any Oracle function or package.

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
kndlny
Regular Collector

Re: Is it possible to use sql UPDATE statement in the execution steps?

Ok. I've just found out that only "SELECT" statement is allowed. So, here is my question.

 

While a project is being proceeded through workflow steps, at some point I've used pre-defined "Create Request" execution so that project manager could create new requests as tasks and assign resourses etc. Each time a new request is created, a field(i.e. Title) in the project should be updated based on the value entered in the new request.

 

Detailed info can be found in the attachment.

 

Thanks

Utkarsh_Mishra
Honored Contributor

Re: Is it possible to use sql UPDATE statement in the execution steps?

Hi,

 

If the Tittle field is in Project request and same need to be updated in Demand Request then...

 

Simply create a execution step in "Project Workflow"  after the "CREATE WORK REQUEST" step and on succed event, and in this execution step call your update function.

 

Else if field need to be updated in Demand Request from Project then...

 

Create an update execution step and put it in the Demand workflow then "MAKE IT AS A FIRST STEP". So whenever a Demand Request is created from a Project then your Tittle field will be updated.

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
kndlny
Regular Collector

Re: Is it possible to use sql UPDATE statement in the execution steps?

Hi Utkarsh,

 

I've created a function, but could not get it work.

 

I always get "Can not perform a DML operation inside a query error"

 

Here is my function

 

CREATE OR REPLACE
FUNCTION UPDATE_WORK_TITLE(title IN VARCHAR2, vtitle IN VARCHAR2, request_number IN NUMBER) RETURN VARCHAR2 IS
BEGIN

UPDATE kcrt_req_header_details
SET parameter21= title, visible_parameter21=vtitle
WHERE request_id=request_number;

  RETURN NULL;
END UPDATE_WORK_TITLE;

 

thanks

kndlny
Regular Collector

Re: Is it possible to use sql UPDATE statement in the execution steps?

Hi,

 

thats pretty much what I am doing, but i can not get the function work.

Utkarsh_Mishra
Honored Contributor

Re: Is it possible to use sql UPDATE statement in the execution steps?

Inside execution step it will works, just make sure that the function return proper value accroding to the step transition.

 

But if you simply try to call this function from any Oracle client/tool.. then there it will show the error. So ignore it.

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
kndlny
Regular Collector

Re: Is it possible to use sql UPDATE statement in the execution steps?

Hi Utkarsh

 

You were right. Thanks a lot

Jim Esler
Honored Contributor

Re: Is it possible to use sql UPDATE statement in the execution steps?

When we want to update a database entry, we often use an execution step to build a script with 'ksc_begin_script', then execute it with 'ksc_local_exec sqlplus ...'.

//Add this to "OnDomLoad" event