How to properly configure a scheduled loop workflow, populating temp tables for each loop

[LukasPe1]

Hi, I am trying to build a workflow which will be used to export data through an API.

I have to make multiple joins and also do retrieval of xml fields within clob columns so I have chosen to write sql queries in javascript activities for this.

After the javascript retrieval I am populating temporary tables with the data, and then read those tables later in the workflow to build the object being sent to the API. I don't want to send too big requests to the API, therefore I have chosen to loop through the records, until none is left to process. I run this workflow every 30 minutes with a scheduler.

This setup works just fine the first time the scheduler is triggered and there are records to process, but not after that first successful run.

I have tried inserting to an extension table within the workflow the following code

var strSchema   = "temp:" + activity.name;
var strTable    = "wkf" + instance.id + "_" + vars.selectCount + "_" + Date.now();
vars.targetSchema = strSchema;
vars.tableName    = strTable;

setSchemaSqlTable(strSchema, strTable);
buildSqlTable(strSchema);

var selectLogsToTmp="INSERT INTO " + strTable
selectLogsToTmp+=retrieveSqlQuery("broadLogRcp")
var result=sqlExec(selectLogsToTmp)

vars.recCount = sqlGetDouble("SELECT COUNT(1) FROM " + strTable);
vars.description = vars.recCount
vars.selectCount++;
task.setCompleted();

 
I have also tried creating new tables with Data schemas, truncating and inserting in each iteration of the loop:

sqlExec("TRUNCATE TABLE " + vars.tmpTableName)

var result=sqlExec("INSERT INTO " + vars.tmpTableName + vars.selectQueryToTmp)
vars.recCount = sqlGetDouble("SELECT COUNT(1) FROM " + vars.tmpTableName);
vars.description = vars.recCount
vars.selectCount++;
task.setCompleted();

The workflow runs fine the first time the scheduler is triggered, looping through every record. But when the scheduler is triggered after that first successful run I get the oracle error below when the workflow reaches INSERT-code in the js-activity for the first iteration. I have the same workflow on many ACC instances but it looks like this problem only occurs on some of them. Could anyone please point me in the right direction? 

12/11/2024 13:30:06	logsToSync	WDB-200002 SQL statement 'INSERT INTO tmp_propositions_to_api....
12/11/2024 13:30:06	logsToSync	ORA-210000 Oracle error: ORA-08103: object no longer exists

 

[Amine_Abedour]

Hello @LukasPe1 

Please finding here a superb article written by @Marcel_Szimonisz (kudos to him) that addresses very well the topic.

Br,

[LukasPe1]

Hi and thanks for your reply Amine,

I could of course change the logic so that I store the information in a json vars.-obj or change the logic so that all the records are retrieved from the beginning and then remove a delta for each iteration from the temp dataset (instead of retrieving new data for each iteration like I do now).

However I think it should be possible to solve this with temp tables as well, but I might be missing something.