Home » SQL & PL/SQL » SQL & PL/SQL » How can I fiddle with date in procedure (10g r2)
How can I fiddle with date in procedure [message #405850] Sat, 30 May 2009 03:06 Go to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hi All,

Pls have a look onto my scenario.

I have two tables rec_lock and details with the below structure.

SQL> desc rec_lock
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------
 NO                                        NOT NULL NUMBER(10)
 LOCK_DATE                                           DATE

SQL> desc details
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------
 NO                                                 NUMBER(10)
 COUNTER                                            NUMBER(6)
 DOC    


The below is the procedure which is removing the record from the details table everytime its counter reaches 50 and inserting into the rec_lock table, and if no is already there in the rec_lock then it only updates the lock_date, uptill now everythin is working fine...

procedure lock_rec as
   n details%rowtype;
   cursor c1 is select * from details where counter > 50;
   NullValues EXCEPTION;
   PRAGMA EXCEPTION_INIT(NullValues, -01400);
 begin
   open c1;
   loop
     fetch c1 into n;
       begin
         insert into rec_lock values(n.no,n.doc);
         delete from rec_lock where no=n.no;
       exception
         when dup_val_on_index then
           update rec_lock set lock_date=n.doc where no=n.no;
           delete from details where no=n.no;
       end;
       exit when c1%notfound;
       commit;
       end loop;
         close c1;
        EXCEPTION
         WHEN NullValues THEN
            dbms_output.put_line('No new or repeat found in details table');
 end;



Now the scenario goes like, counter field gets incremented on regular basis whenever its associated no gets accessed by the application.


Currently whenever counter reaches the 50 its is moved to the rec_lock table, But I need to restrict it with the condition, If the counter in details table reaches 50 in the last three days then only the row should move to the rec_lock table, but I have only one date field "DOC" which gets updated everytime user access the table.

PLS SUGGEST WHAT IS THE EFFECTIVE AND EASY WAY TO ACHIVE THIS.

Thanks in advance.
Re: How can I fiddle with date in procedure [message #405927 is a reply to message #405850] Sun, 31 May 2009 22:19 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Any suggestion pls.
Re: How can I fiddle with date in procedure [message #405932 is a reply to message #405850] Mon, 01 June 2009 00:18 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you'll have to alter the table in order to add another DATE datatype column. It would be updated when counter reaches 50 (and you wouldn't update it later, except - possibly - after records are being moved from one table to another). The "doc" column would be used the same way you use it now.
Re: How can I fiddle with date in procedure [message #406023 is a reply to message #405850] Mon, 01 June 2009 13:31 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hi,

I thought of achieving the goal by creating one more table(info) which will store the everydays count and date against the number and delete the same from the details table on everday basis, so that the details table will have the singel day count of the number.

Then using the procedure I can fetch the data from the info table and put the same records into the rec_locked table based on the condition sysdate -3 and sum(counter) >50;

The procedure should get the count of the associated each number only once so that i could put the conditional insert into the rec_locked, however the below test procedure repeats the compute of the sum of counter in info, thus returns the wrong set of results.

create or replace procedure p1 as
   n info.no%type;
   c info.cnt%type;
   a number(10);
   x number(3):=0;
   cursor c1 is select no,sum(cnt) from info where doc <=sysdate-3 group by no;
   NullValues EXCEPTION;
   PRAGMA EXCEPTION_INIT(NullValues, -01400);
 begin
   open c1;
   loop
     fetch c1 into n,c;
       begin
          select sum(c) into a from info where no=n;
          x:=n;
         dbms_output.put_line(n||' =  '||a);
       exception
         when dup_val_on_index then
           dbms_output.put_line('Number is aleady there in the table.');
       end;
       exit when c1%notfound;
       commit;
       end loop;
         close c1;
        EXCEPTION
         WHEN NullValues THEN
            dbms_output.put_line('Nothing found');

 end;
/




PLEASE SUGGEST!
Re: How can I fiddle with date in procedure [message #406082 is a reply to message #405850] Tue, 02 June 2009 02:39 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Any suggestions please
Re: How can I fiddle with date in procedure [message #406085 is a reply to message #406082] Tue, 02 June 2009 03:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you've got a mistake in this line:
select sum(c) into a from info where no=n;

c is a local variable that you fetch the cursor into - I think you want SUM(cnt)

This is one of the many reasons why experienced developers prefer meaningfully named variables, idealy with a suffix or prefix to denote that they are local variables.
Previous Topic: Depersonalize data in Oracle table
Next Topic: Problem with BLOB data
Goto Forum:
  


Current Time: Sun Dec 04 17:06:10 CST 2016

Total time taken to generate the page: 0.10848 seconds