Re: How to config a job to be executed?

helge.hauan_at_applica.no (Helge Hauan) wrote in message news:<ddb41d2b.0301080000.21feae4_at_posting.google.com>...
> Hi.
> In the project I'm working on now each developer have its own user
> account towards the database. The application we are doing generates
> logging information into a logTable. What we want is to delete old log
> information (older than 30 days) from that table. Each user accounts
> have it's own 'instance' of logTable named 'LogTable', same name for
> every account. By this, I mean there, is not a common logTable, but
> one for each user account.
>
> The make system we are using drops all tables, sequences, stored
> procedures, views, and so on.
>
> So to my question.
> Tried to implement a job by doing this:
> EXECUTE dbmsjob.remove(1);
> EXECUTE dbmsjob.isubmit(1, 'sp_delete_history_log',
> trunc(sysdate+1,'DD'), 'trunc(sysdate+1,''DD'')');
>
> This sp_dele... is a stored procedure which will be executed every
> midnight for the user schema that creates it.
>
> This seems to be a problem because it cannot be executed for more than
> one user (schema), as long as jobNo(1) only belongs to one user. I
> know about dbmsjob.submit but wants to make it possible from our
> makesystem to use a fixed jobNo, regardless of the user running make.
>
>
> I want to enable all developers doing remove and isubmit of jonNo = 1,
> but can You please help me with a solution? Is it a user access
> problem, or can the same task be achieved by other solutions?
>
> Best regards,
> Helge Hauan

Helge,
If I understand it correctly, the job is suppossed to execute every night to expecute the sp_delete_history_log (which deletes something from logTable of each user). You can set up a loop for all users(developers not the system) and work thru the loop for each user and execute the thing in one job, in lieu of each job for each user. Also, do you need a semicolon (;) at the end of jobname? make sure that.
EXECUTE dbmsjob.isubmit(1, 'sp_delete_history_log;', trunc(sysdate+1,'DD'), 'trunc (sysdate+1,''DD'')');

now sp_delete_history_log can take username(developer username) as arg in and execute that. Hope this helps you. Received on Wed Jan 08 2003 - 11:09:15 CST