Skip to main content

Hello everyone,

We have frequently encountered the following pattern in IFS SQL traces:

DECLARE
    result_ background_jobs_handling_svc.entity_dec;

BEGIN
    result_ := background_jobs_handling_svc.crud_delete('*', 8189760, 'DO', deferred_job## => '');
END;

We would like to know how to execute the following directly from SQL Developer:

background_jobs_handling_svc.crud_delete('*', 8189760, 'DO', deferred_job## => '');

 

When attempting to execute it using a SELECT statement with dual:

 
select Background_Jobs_Handling_SVC.CRUD_Delete('*', 81897606, 'DO', deferred_job## => '') 
from dual;

 

We receive the following error:

https://docs.oracle.com/error-help/db/ora-00902/00902. 00000 -  "invalid datatype"*Cause:    The datatype entered in the CREATE or ALTER TABLE           statement is not valid.*Action:   Correct the syntax.Erreur à la ligne 1, colonne 8

 

 

 

Any idea please?

Hi ​@IFSRUNINNOVAL,

Error you are getting is due to perform the delete inside a select. it has to be in a PLSQL block. However, it’s not a possible to invoke a projection calls inside plsql logic. Check the following topic for more details.

IFS Cloud 22R2: Calling CRUD methods in a service package(SVC) from another Custom Service package | IFS Community

 

As an alternate, use the server plsql package call ( Deferred_Job_API.Remove__) for your need.

Here’s a sample script to delete a background job.

declare 
   objid_               VARCHAR2(2000);
   objversion_          VARCHAR2(2000);
   info_                VARCHAR2(32000);
   background_job_id_   NUMBER;

   CURSOR get_objidversion IS
    SELECT objid, objversion
      FROM DEFERRED_JOB
     WHERE job_id = background_job_id_;
begin
  background_job_id_ := 282; --background job id to delete
  OPEN get_objidversion;
  FETCH get_objidversion INTO objid_, objversion_;
  CLOSE get_objidversion;
  Deferred_Job_API.Remove__(info_, objid_, objversion_, 'DO');  
end;

Hope it helps.

Damith

Thank’s ​@dsj again :) 

I have attempted to generate multiple background jobs by refreshing the cache

 

I now have multiple posted jobs : 

 

I have tried to delete : 82588549

 

the script was succefful, and the second launche it’s shows : 

ORA-20115: DeferredJob.The "Deferred Job" has already been removed by another user.
 

But when looking at the screen (after refreshing the page) : 

my job is still posted : 

 

​@IFSRUNINNOVAL Probably it’s due to the commit is missing. by bad :)

Add a commit to the end of the script and try again.

…

...

Deferred_Job_API.Remove__(info_, objid_, objversion_, 'DO');

COMMIT;

end;

 

 

Thank you ​@dsj 

Reply


Terms & ConditionsCookie settings