Jump to content

Oracle Select Has Rows Missing After Insert


Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by GolfZuluFoxtrot
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.