Jump to content


Photo

Oracle Select Has Rows Missing After Insert

oracle sql php

  • Please log in to reply
2 replies to this topic

#1 GolfZuluFoxtrot

GolfZuluFoxtrot

    Newbie

  • New Members
  • Pip
  • 2 posts

Posted 29 November 2012 - 05:40 AM

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!
  • 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)
  • 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
  • Only approx half the rows are returned by the select
  • 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.

#2 PFMaBiSmAd

PFMaBiSmAd

    Advanced Member

  • Staff Alumni
  • 16,767 posts
  • LocationColorado, U.S.A.

Posted 29 November 2012 - 08:18 AM

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.
Signature: (not a comment about anything you posted unless specifically indicated)
Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

Programming is just problem solving, but it is done in another language. You must learn enough of the programming language you are using to be able to read and write code.

#3 GolfZuluFoxtrot

GolfZuluFoxtrot

    Newbie

  • New Members
  • Pip
  • 2 posts

Posted 29 November 2012 - 09:12 AM

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, 29 November 2012 - 09:22 AM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com