Strange behavior on a simple Delete/Truncate Statements

[mega_L]

Hello!

I'm using CF8 and Oracle 11g and In one of my app., I applied 4 simple delete/truncate statements.

These statements are used to clean up 4 temporary tables everytime this app. starts.

For a few weeks I have not seen anything wrong with it until one day the result produced by this app show duplicates data.

On my investigation I found out that 4 of my Truncate statements did not truncate the data, so previous data were process again and again...

Then I made some changes:

FROM:

<cfquery name="OriginalQuery" datasource="mytestDB">

  Truncate table temp_table1

  Truncate table temp_table2

  Truncate table temp_table3

  Truncate table temp_table4

</cfquery>

INTO:

<cfquery name="OriginalQuery1" datasource="mytestDB">

  Truncate table temp_table1

</cfquery>

<cfquery name="OriginalQuery2" datasource="mytestDB">

  Truncate table temp_table2

</cfquery>

<cfquery name="OriginalQuery3" datasource="mytestDB">

  Truncate table temp_table3

</cfquery>

<cfquery name="OriginalQuery4" datasource="mytestDB">

  Truncate table temp_table4

</cfquery>

Then I got the correct result again but only for a few weeks before the same problem coming back again.

Obviously, my statements are correct because I got the right data for weeks before it stopped working.

When I did delete statement directly in Oracle SQL Developer, I need to apply COMMIT;

such as:

Delete from Table A;

commit;

But when using Truncate I was told I don't need to use commit.

I changed the 4 statements in my CF application from truncate to delete and I got the same result.

In the first few weeks, I got the right result but it comes back.

Has anyone experience this? please help!

[BKBK]

Truncating the tables simply deletes the rows of data in them. The tables themselves remain intact. It just might be that some other process subsequently repopulates the tables.

[Dan_Bracuk]

Are these true temporary tables or just normal tables with temp as part of their name.

[mega_L]

These are normal tables.

I even warpped the 4 delete statements in str. proc but the problem persist:

I did this in Oracle 11g:

create or replace
PROCEDURE sp_TEST_DELETE 
AS
BEGIN

   DELETE FROM test_tempaddr;
   DELETE FROM test_tempaddr_2;
   DELETE FROM test_addr;
   DELETE FROM test_addr_3;
  
   COMMIT;

END;

I call this procedure from my CF this way:

<cfstoredproc procedure="sp_TEST_DELETE" datasource="#application.dsn#"> 

</cfstoredproc>

[Dan_Bracuk]

Given that these are normal tables then BKBK's suggestion of another process re-populating the tables might be the case.  Or, it could be near simulaneous use by two people.  Is this scenario conceivable:

User 1 - truncate

User 2 - truncate

User 1 - populate

User 2 - populate

?