How to properly configure a scheduled loop workflow, populating temp tables for each loop
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
