Hi,
In my fex file i use the output of a hold file to run a loop and within it i will perform some sequence of operation and at the final step i execute a DML (update) statement to a oracle DB. Even when i have commit after my DML, i see that the change is made to the DB only at the end of execution of entire loop (rather updating after each iteration of loop)... There is no issue by this way when i run the loop for limited values, however when the loop iterates for 1000 iteration, the DML is not getting executed atall (or it is not commited). My assumption is that Webfocus is pilling up all the update and executing at the end of loop, which might lock the oracle table. Can anyone help me understand how does Webfocus executes DML and Commit? Thanks much in advance.


Here is the code i am having problem with..


SQL SQLORA PREPARE SQLOUT FOR
SELECT DECODE(RN.BD,1,'FN',4,'MFS') AS BD, RN.REP_NUMBER AS REP_NUMBER, RN.SSN AS SSN, RN.MULT_HOUSEHOLD_IND AS MULT_HOUSEHOLD_IND
FROM AWSAPP.RPT_DRVR_TBL RN
WHERE ssn in ('160585617', '460966989' , '460966989');
END
TABLE FILE SQLOUT
PRINT
BD
REP_NUMBER
SSN
MULT_HOUSEHOLD_IND
ON TABLE HOLD AS REPLIST FORMAT ALPHA
END
-RUN
-top.loop
-READ REPLIST NOCLOSE &BD_NUM.A3. &REPNUM.A10. &REP_SSN.A9. &MULT_IND.A1.

-IF &IORETURN NE 0 GOTO end.loop;

SET SQLENGINE=SQLORA
ENGINE SQLORA SET DEFAULT_CONNECTION CT_ADWSAQ
SQL UPDATE AWSAPP.RPT_DRVR_TBL SET RPT_NAME='&OUTPUTFILE', RPT_STATUS = 'SUCCESS', NUM_OF_ROWS=TO_NUMBER('&LINENUM') , RPT_CREATION_TS = SYSDATE WHERE SSN='&REP_SSN' AND BD = DECODE('&BD_NUM','FN',1,'MFS',4);
SQL COMMIT;
END
-RUN

-GOTO top.loop;
-end.loop

This message has been edited. Last edited by: Kerry,

I would suggest separating the commit from the update command.

SET SQLENGINE=SQLORA
ENGINE SQLORA SET DEFAULT_CONNECTION CT_ADWSAQ
SQL UPDATE AWSAPP.RPT_DRVR_TBL SET RPT_NAME='&OUTPUTFILE', RPT_STATUS = 'SUCCESS', NUM_OF_ROWS=TO_NUMBER('&LINENUM') , RPT_CREATION_TS = SYSDATE WHERE SSN='&REP_SSN' AND BD = DECODE('&BD_NUM','FN',1,'MFS',4);
END

-RUN

SQL SQLORA
COMMIT;
END
-RUN

Waz,
You are the man. Your code works perfect.Should i always have only one DML per SQLORA? Anyways, thanks a lot for the solution.

quote:

Should i always have only one DML per SQLORA

Yes.

Check out the documentation on SQL support, it will help.