Home » SQL & PL/SQL » SQL & PL/SQL » Procedure failing-Need to know where? (10g)
Procedure failing-Need to know where? [message #405542] Thu, 28 May 2009 05:42 Go to next message
J1357
Messages: 33
Registered: November 2008
Member
I'm having a table which stores add time of a file(File_add_time).
This is a timestamp field.Now requirement is to remove the any file
older than 6 months from db through job.

I've written code but it deletes those files which have been added recently.

Can some point the error out ?

  • Attachment: 1.sql
    (Size: 0.96KB, Downloaded 119 times)
Re: Procedure failing-Need to know where? [message #405545 is a reply to message #405542] Thu, 28 May 2009 05:48 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Could you post the DDL and DML for these and also you haven't pointed out what sort of error you getting

If you could print
TRUNC (TO_NUMBER (SUBSTR ((lt_delete_time - ld_image_add_time),
                                   1,
                                   INSTR (lt_delete_time - ld_image_add_time,
                                          ' '
                                         )
                                  )
                          )
               ) 
using DBMS_OUTPUT.PRINT_LINE you can debug

Regards,
Ashoka BL

Re: Procedure failing-Need to know where? [message #405546 is a reply to message #405542] Thu, 28 May 2009 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your code inline.
Post it formatted and keep your lines in 80 characters width.
Also post your Oracle version with 4 decimals.

Regards
Michel
Re: Procedure failing-Need to know where? [message #405548 is a reply to message #405542] Thu, 28 May 2009 05:56 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's the value of lv_file_id?
Re: Procedure failing-Need to know where? [message #405554 is a reply to message #405542] Thu, 28 May 2009 06:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What are you doing in that query?

lt_delete_time is simply ld_image_add_time plus 6 months.
This will frequently be more than 180 days after ld_image_add_time.

You're then doing this function (which I assume came to you in a dream, as there's no earthly logic to it) to presumably try to get a number of days between the two:
 trunc(to_number(substr((lt_delete_time-ld_image_add_time),1,instr(lt_delete_time-ld_image_add_time,' '))))


If you're trying to delete images that were added more than 6 months ago, just do this:
PROCEDURE pr_check_image_validality IS
BEGIN
  DELETE a
  WHERE add_months(image_add_time,6) < trunc(sysdate);

  commit;
END;
/
Previous Topic: Deadlock
Next Topic: Do I need temporary table?
Goto Forum:
  


Current Time: Wed Dec 07 06:44:18 CST 2016

Total time taken to generate the page: 0.06305 seconds