OraFAQ Forum: Server Administration » Auto Insert with Procedure or trigger

Home » RDBMS Server » Server Administration » Auto Insert with Procedure or trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Auto Insert with Procedure or trigger [message #55554]

Fri, 31 January 2003 04:37

mangesh
Messages: 28
Registered: May 2002

Junior Member

HI all ,
I need to write a procedure to insert the the data in to the table
the table structure is as follows :
TABLE BUCKETDETAIL_ALL
(
BUCKETID NUMBER,
STARTDATE DATE,
ENDDATE DATE
)

total records in the table are always 54 ( i.e. count)
value of bucketID is 1 to 54. The the 1st record ( for buckid =1) is always 1 ,3/10/2003,4/14/2003
and reming records should get inserted automatically as per following formula
STARTDATE of BUCKETID n = ENDFDATE of BUCKETID (n-1)
ENDDATE of BUCKETID n = (STARTDATE of BUCKETID n) +7
also BUCKETID get increamented by 1
n=2 till 54
e.g. .....

BUCKETID-----------STARTDATE----------ENDDATE
1-------------------------3/10/2003--------------- 4/14/2003----<-1st record(fix)

2------------------------- 4/14/2003--------------- 4/21/2003---<-2nd record based on 1st record

so on ...till 54 BUCKETID

Pls Help me in

Thanks in Advance

Report message to a moderator

Re: Auto Insert with Procedure or trigger [message #55557 is a reply to message #55554]

Fri, 31 January 2003 06:09

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

mag@itloaner1_local > ed
Wrote file afiedt.buf

  1  create TABLE BUCKETDETAIL_ALL
  2  (
  3  BUCKETID NUMBER primary key,
  4  STARTDATE DATE,
  5  ENDDATE DATE
  6* )
mag@itloaner1_local > /

Table created.
mag@itloaner1_local > create or replace procedure autoinsert
  2  as
  3  pk         number:=1;
  4  sdate      date:='3/10/2003';
  5  edate      date:='4/14/2003';
  6  begin
  7     begin
  8             insert into bucketdetail_all values (pk,sdate,edate);
  9             commit;
 10     end;
 11  for mag in 1..53 loop
 12     pk:=pk+1;
 13     sdate:=edate;
 14     edate:=edate+7;
 15     insert into bucketdetail_all values (pk,sdate,edate);
 16  end loop;
 17  commit;
 18  end;
 19  /

Procedure created.

mag@itloaner1_local > select * from  bucketdetail_all;

no rows selected

mag@itloaner1_local > exec autoinsert;

PL/SQL procedure successfully completed.

mag@itloaner1_local > select * from  bucketdetail_all;

  BUCKETID STARTDATE  ENDDATE
---------- ---------- ----------
         1 03/10/2003 04/14/2003
         2 04/14/2003 04/21/2003
         3 04/21/2003 04/28/2003
         4 04/28/2003 05/05/2003
         5 05/05/2003 05/12/2003
         6 05/12/2003 05/19/2003
         7 05/19/2003 05/26/2003
         8 05/26/2003 06/02/2003
         9 06/02/2003 06/09/2003
        10 06/09/2003 06/16/2003
        11 06/16/2003 06/23/2003
        12 06/23/2003 06/30/2003
        13 06/30/2003 07/07/2003
        14 07/07/2003 07/14/2003
        15 07/14/2003 07/21/2003
        16 07/21/2003 07/28/2003
        17 07/28/2003 08/04/2003
        18 08/04/2003 08/11/2003
        19 08/11/2003 08/18/2003
        20 08/18/2003 08/25/2003
        21 08/25/2003 09/01/2003
        22 09/01/2003 09/08/2003
        23 09/08/2003 09/15/2003
        24 09/15/2003 09/22/2003
        25 09/22/2003 09/29/2003
        26 09/29/2003 10/06/2003
        27 10/06/2003 10/13/2003
        28 10/13/2003 10/20/2003
        29 10/20/2003 10/27/2003
        30 10/27/2003 11/03/2003
        31 11/03/2003 11/10/2003
        32 11/10/2003 11/17/2003
        33 11/17/2003 11/24/2003
        34 11/24/2003 12/01/2003
        35 12/01/2003 12/08/2003
        36 12/08/2003 12/15/2003
        37 12/15/2003 12/22/2003
        38 12/22/2003 12/29/2003
        39 12/29/2003 01/05/2004
        40 01/05/2004 01/12/2004
        41 01/12/2004 01/19/2004
        42 01/19/2004 01/26/2004
        43 01/26/2004 02/02/2004
        44 02/02/2004 02/09/2004
        45 02/09/2004 02/16/2004
        46 02/16/2004 02/23/2004
        47 02/23/2004 03/01/2004
        48 03/01/2004 03/08/2004
        49 03/08/2004 03/15/2004
        50 03/15/2004 03/22/2004
        51 03/22/2004 03/29/2004
        52 03/29/2004 04/05/2004
        53 04/05/2004 04/12/2004
        54 04/12/2004 04/19/2004

54 rows selected.

mag@itloaner1_local >

Report message to a moderator

Re: Auto Insert with Procedure or trigger [message #55564 is a reply to message #55554]

Fri, 31 January 2003 09:40

mangesh
Messages: 28
Registered: May 2002

Junior Member

Thkns Mahesh ,
I did it in diff way with ref to ur procedure
create or replace procedure insert_BKTDT_ALL is
strdt DATE;
begin
delete from ODS_BUCKETDETAIL_ALL ;
insert into ODS_BUCKETDETAIL_ALL (ENTERPRISE,BUCKETNAME,BUCKETID,STARTDATE,ENDDATE)
(select 'ICM MP','SCP_WEEKLY_BUCKETS',1,EFFSTARTDATE,EFFSTARTDATE+35 from ODS_PLANMASTER_ALL where planid=1);
select ENDDATE into strdt from ODS_BUCKETDETAIL_ALL where BUCKETID=1;

for i in 2..54 loop
insert into ODS_BUCKETDETAIL_ALL (ENTERPRISE,BUCKETNAME,BUCKETID,STARTDATE,ENDDATE)values
('ICM MP','SCP_WEEKLY_BUCKETS',i,strdt ,strdt+7);
strdt := strdt+7;
commit;
end loop;

end;
/

Many thanks

Report message to a moderator

Previous Topic:	ORA-01157.
Next Topic:	ArchiveLog Mode

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 23:00:32 CDT 2024