Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Less rows inserted when passing select result to stored function: why?

Squall867Jun 30 2015 — edited Jul 6 2015

This is really, but really strange! I created the following stored function to subtract from a date just working days (avoiding week ends, basically)

create or replace function minusLaborDays(endDate Date, days Number)

  return Date

as

day_of_week Number;

resultD Date;

i number;

begin

   i:=0;

   resultD := endDate;

   while i<days

   loop

  resultD := resultD - 1;

  day_of_week := to_number(to_char(resultD,'D'));

  if(day_of_week <= 5)

      then

  i := i+1;

      end if;

   end loop;

  

  

   return resultD;

end;

Now, I want to select a date (along with other values) from a table and insert it into another table.

Here's the sql:

insert /*+ append */ into TABLE2 (c1,c2,c3,my_date)

(select distinct E.c1, E.c2, E.c3, minusLaborDays(trunc(E.end_date),2) my_date from TABLE1 E);

Now, with this code I successfully insert 1900 rows.

If I remove the call to my function, so I simply insert the date as it is...I insert 1903 rows!

Why is that?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 3 2015
Added on Jun 30 2015
20 comments
2,392 views