Oracle Select Has Rows Missing After Insert

[GolfZuluFoxtrot]

Hi all.

 

I've observed an oddity with Oracle 11g. Maybe if someone's experienced anything similar then they can provide some input -- it's got me stumped!

1. PHP Application reads a file and insert rows into a db table with a 'run id' that is the same for all records inserted in the session (approx 2000 records inserted in a loop)
   
2. In the same session (DB connection kept open), the same application selects the rows it just inserted using the same 'run id' column. There is an index for this column
   
3. Only approx half the rows are returned by the select
   
4. If I run the select later, from SQL*Plus (using the same sql statement as the application) all the rows are returned
   

 

The DB connection has 'autocommit' switched on. So that fries one of my theories. The only other thing I can think of is that for some reason the index wasn't populated in time - not sure if this holds water though.

 

Open to any ideas...

 

Thanks, ZF.

[PFMaBiSmAd]

Only approx half the rows are returned by the select

 

Which half? The first, some out of the middle, the last, every other row, or rows where the data might look like html tags and don't display in the browser but appear in the 'view source'?

 

There a good chance that It's your code or something about the data that makes it 'appear' that it is missing from the select query. Post your code and show us what some of the 'missing' data looks like.

[GolfZuluFoxtrot]

Hi.

 

Thanks for your reply.

 

It was the first half that was returned. The code calls the PEAR:DB numRows function to see how many rows are returned by the query - this is approx half of the rows inserted seconds before. The same SQL run some time later (in a different session) yielded all the rows, so I know they were inserted fine and that the 'select' sql is correct.

 

The code is used in a batch application, so it doesn't go near a website. The counters are stored in a log file, so we know what they were.

 

Worth addidng that this code has run flawlessly for years, I'm at a loss to explain why (one one single occasion) the select didn't return the data it was supposed to..

 

// MODULE TO INSERT THE DATA

 $b2_insert_sql = "insert into mytable (id, ctfdate, //snip a whole bunch of fields// )" .
						 " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'". IS_APPROVED ."',?,?,?,?,?, ". NOT_REVERSED .", ". NOT_CHARGED_BACK .", ". NOT_REPRESENTED .",?)";
 $prh_b2_insert = $db->prepare("$b2_insert_sql");

 // inside a loop later //
 $insert_data = array("$b2_rcd_id", "$run_ctf_date", //snip a whole bunch of fields// );

	 $result = $db->execute($prh_b2_insert, $insert_data)
	 if (!DB::isError($result)) { // keep counters for inserted txns // }

// IN A DIFFERENT MODULE (Same ORA session)

 $b2_sql = "select id, matchedflag, failureflag, presentmentflag ".
		 "from mytable ".
		 "where ctfdate = '$active_date' ";
 $b2_result = $db->query("$b2_sql");
 $num_rows = $b2_result->numRows(); // << This counter doesn't agree with the counters above
 // process each row

There is an index on the CTFDATE field.

 

$run_ctf_date and $active_date are the same value

Edited November 29, 2012 by GolfZuluFoxtrot