We are trying insert records from a select query in to temporary table but, some of the records are not insrted in to temporary table. In simple terms, the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by and others conditions
Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is a simple bulk inserts in a simple script with 1 parts... insert in to temp table select * from xxx*. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted._
Also, we had some other observation. It only happens in its 2nd execution and other continuous run and not its first run. Hope there might be some cache problem.
Even, we are wondering! in TOAD, we tested, it happens at times. In application jar file (java), after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.* Finally we had a workaround, once we clear session cache (_checkpoint, flush shared_pool and buffer_cache_) it works fine for Re_Run. However, we do not is this solution ok? and how clearing cache works in the background and failed insert. If it is true, something wrong configuration setup, i guess.
*Anyone can explain?
1. Is this b'cos of Temporary table
2. Why all the records populated by the select query not inserted
3. Really the problem with cache if, yes what is the cause of the error
4. what exactly the problem and
5. the best and optimized solution*
Thank you in advance for your efforts and help.
Edited by: user3090519 on Mar 20, 2013 11:39 PM
What I got from that was youve a 2 part process
Part 1. Populates a temporary table via a java program (Is it a temporary table of TYPE temporary?)
Part 2. selects from the temporary table, But youre getting conflicting results selecting from the temporary table that are resolved by a combination of flushing cache and checkpointing
For me, you've got some flow problems here and it is impossible to troubleshoot without access to the code.
Why do some records not insert from your java program? Id suggest youre better served going over to a coding or SQL area.
Thank you for your response. However it is very simple process.
sql = "insert in to temp table select x,xx,xxx.. from xxx,abc,pqr";
statement = conn.createStatement();
rCnt = stmt.executeUpdate(sql);
after this execution. we took record count of (i) select x,xx,xxx.. from xxx,abc,pqr and (ii) temp table. but, it matches only 1st time. when we run the batch again next time the count mismatch. once, clear the cache as I said before, it matches again.
if you get this below error :
Text: Type mismatch between object table and value '%s' in INSERT statement.
Cause: In the INSERT statement operating on typed tables (tables of
objects), the type of a non-aggregate value
did not match the object type of the table.
then it's better to contact Oracle Support.
Dear Osama, there is no error in the script. I'd given sample but logical script which we are using. actually the select statement is very complex with multiple joins, unions, inline queries with around 700 lines. it works fine also the insert also done. however, the issue is why on the same script both insert and select count are mismatching. The expected action is, the populated records from the select statement (2nd part) should be inserted in to the temporary table (1st Part) and why the differences.
Edited by: user3090519 on Mar 21, 2013 2:51 AM