This discussion is archived
8 Replies Latest reply: Jan 6, 2013 5:41 PM by rp0428 RSS

funny problem - any ideas ?

983087 Newbie
Currently Being Moderated
Hi Folks,

I'm seeing a funny issue and running out of ideas, any help would be appreciated.

Here is a pl/sql block :
declare 
 ctr  number := 0;
 ctr1  number := 0;
begin

insert into TableT ( x, y, z) 
  select <3 columns> from table1, table2, table3, table4, table5 
  where <join conditions for tables 1 thru 5> ;
-- this inserts 67xxx records. This number varies randomly during runs, no two runs return the same number e.g 67258, 67120, 67118 etc 

for rec in ( 
select <3 columns> from table1, table2, table3, table4, table5 
where <join conditions for tables 1 thru 5> ) loop 
   ctr := ctr+1;
end loop;
dbms_output.put_line(ctr); -- shows the right number that should be returned by the select query i.e. 67105 EVERY time its run


select count(*)  into ctr1 from table1, table2, table3, table4, table5 
where <join conditions for tables 1 thru 5>  
dbms_output.put_line(ctr1); -- shows the right number that should be returned by the select query i.e. 67105 EVERY time its run


end;
/
The query in the for loop is an exact cut-paste job from the query in the insert.

Theres nothing special about tableT its just a straight create table with NO clauses other than column definitions all of which are number,varchar2(20) and date specified at creation time, no constraints, no triggers. DB is 11.1.0.7 if thats relevant.

I've racked my brains, cant think of a reason for this. Any ideas ?

TIA !!!!

Edited by: 980084 on Jan 5, 2013 8:20 AM

Edited by: 980084 on Jan 5, 2013 8:32 A
  • 1. Re: funny problem - any ideas ?
    Hoek Guru
    Currently Being Moderated
    Welcome to the forum.

    Make sure your examples are tested and will run on our databases (check your declaration part, for example).
    See: {message:id=9360002} (especially # 7, 8 and 9)
    Without the queries and the data (albeit a smal amount, but reproducing your findings), it is hard to give usefult further pointers.
  • 2. Re: funny problem - any ideas ?
    983087 Newbie
    Currently Being Moderated
    Hi Hoek,
    Thanks for your reply, I cant really demonstrate the problem with test data since if I create new test tables and sample data, the problem does not show up.
    I'm really looking for suggestions of areas to look into, I understand you can't point out the actual problem with what I've posted.
    Norm
  • 3. Re: funny problem - any ideas ?
    ranit B Expert
    Currently Being Moderated
    980084 wrote:
    insert into TableT ( x, y, z)
    select <3 columns> from table1, table2, table3, table4, table5
    where <join conditions for tables 1 thru 5> ;
    Are you sure you are not missing anything in the anonymous block you have posted?
    Then, I guess_ in every run your Insert statement will keep on adding rows unless you place a Delete or Truncate statement... right ?

    I mean to say, who empties your table 'T'. Else how will you check how many records got inserted in the last run? (Please don't tell you'll subtract the count and check for each run)
  • 4. Re: funny problem - any ideas ?
    983087 Newbie
    Currently Being Moderated
    Sure, there is a truncation that happens at the beginning of the job, but the issue is that a variable number of rows get inserted for subsequent runs with no change in data.
  • 5. Re: funny problem - any ideas ?
    rp0428 Guru
    Currently Being Moderated
    >
    Sure, there is a truncation that happens at the beginning of the job, but the issue is that a variable number of rows get inserted for subsequent runs with no change in data.
    >
    What do you mean 'Sure, there is a truncation . . .'?

    That is a pretty significant piece of the issue don't you think?

    The most troubling thing is that it also means you aren't posting the entire process that you are using.

    Post the entire process and the actual DDL and DML being used.
  • 6. Re: funny problem - any ideas ?
    Hoek Guru
    Currently Being Moderated
    980084 wrote:
    Hi Hoek,
    Thanks for your reply, I cant really demonstrate the problem with test data since if I create new test tables and sample data, the problem does not show up.
    I'm really looking for suggestions of areas to look into, I understand you can't point out the actual problem with what I've posted.
    Norm
    Hi Norm,

    Too bad you cannot reproduce the problem. It raises more questions and guesses/speculations which is the opposite of what you're looking for.
    Are you getting the problem in an 'isolated environment' (are you the only one doing DML, is this a test database or...)?
    How did you determine the number of rows inserted through your SQL approach (INSERT...SELECT)? SQL%ROWCOUNT?
    Any triggers on table1..5?
    Your example seems to be narrowed down a bit too much, expecially since you cannot reproduce when you try to recreate the problem.
  • 7. Re: funny problem - any ideas ?
    983087 Newbie
    Currently Being Moderated
    Hoek,

    You're right it is narrowed down quite a bit as there is a lot going on before and after the snippet I posted, but it would be extraneous to the problem I would think.
    The problem simply put is: why would the insert and the select count(*) give different results when there is no processing happening between those two statements, the select query used in both is identical and there is nothing special or out of the ordinary about the table being inserted into.

    After this post I inserted a for cursor loop with the same select statement and inserted the rows one at a time, that works correctly too, so whats special about the insert into ...select from that we are missing.

    I'm fishing really for areas to look into.....

    Thanks for your attention to this, I appreciate it.

    Norm
  • 8. Re: funny problem - any ideas ?
    rp0428 Guru
    Currently Being Moderated
    >
    I'm fishing really for areas to look into.....
    >
    You're also throwing what you catch back into the water.

    You've been given the bait you need to use to make the 'catch' you are looking for but you have to put the bait on your hook; you can't just leave it lying in the boat.

    The problem is in the part you aren't posting so if you don't plan to post the DDL and full process code you are using just say so so people don't waste their time trying to help you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points