Creating Batch???

[kokiri]

Need your experties. I'm not sure what options are available for the task I need to accomplish.
I need to run simple query every hour for next few weeks and result need to be insert into temp table. I heard that this can be done thru batch file.

Collecting fields are: sysdate, field1, and field2.

Please help.
Thanks.

 

[sem]

Use DBMS_JOB package. Of course you may also use OS scheduler (at, cron), but in most cases it's more suitable when you need some OS filesystem interaction. As you need "pure Oracle" call, DBMS_JOB is the best choice.

Regards, Dima

 

[dbtoo2001]

Example:
-- submits a job, this one runs a stored procedure to analyze the schema,
different owners on different days, running the job 7 days a week.
--
-- stored procedure
CREATE OR REPLACE Procedure SYS.RUN_DAILY_ANALYZE (placeholder in varchar2
) As
v_instdate varchar2(9);
cursor c1 is
SELECT rtrim(TO_CHAR(sysdate,'DAY')) FROM dual;
Begin
open c1;
fetch c1 into v_instdate;
close c1;
if v_instdate = 'MONDAY' then
DBMS_UTILITY.ANALYZE_SCHEMA ('OE','ESTIMATE','',20);
DBMS_UTILITY.ANALYZE_SCHEMA ('QA','ESTIMATE','',20);
elsif v_instdate = 'TUESDAY' then
DBMS_UTILITY.ANALYZE_SCHEMA ('AP','ESTIMATE','',20);
DBMS_UTILITY.ANALYZE_SCHEMA ('AR','ESTIMATE','',20);
elsif v_instdate = 'WEDNESDAY' then
DBMS_UTILITY.ANALYZE_SCHEMA ('FA','ESTIMATE','',20);
DBMS_UTILITY.ANALYZE_SCHEMA ('GL','ESTIMATE','',20);
elsif v_instdate = 'THURSDAY' then
DBMS_UTILITY.ANALYZE_SCHEMA ('APPLSYS','ESTIMATE','',20);
elsif v_instdate = 'FRIDAY' then
DBMS_UTILITY.ANALYZE_SCHEMA ('INV','ESTIMATE','',20);
DBMS_UTILITY.ANALYZE_SCHEMA ('FA','ESTIMATE','',20);
elsif v_instdate = 'SATURDAY' then
DBMS_UTILITY.ANALYZE_SCHEMA ('PO','ESTIMATE','',20);
else DBMS_UTILITY.ANALYZE_SCHEMA ('PA','ESTIMATE','',20);
end if;
End;
--
-- submit the job
DECLARE jobno number;
placeholder varchar2(1);
BEGIN
DBMS_JOB.SUBMIT(jobno,
'SYS.RUN_DAILY_ANALYZE(''placeholder'');',
TO_DATE('Friday, July 25, 2003 12 00 00 AM', 'DAY, MONTH DD, YYYY HH:MI:SS AM'),
'SYSDATE+1');
COMMIT;
END;
--
-- change the job to broken before you can get rid of the job from the queue, if it is executing.
-- It will not interrupt the job running, must set job to broken then kill the job.
-- jobno is the job number assigned by oracle
begin
dbms_job.broken(jobno,TRUE);
commit;
end;
--
-- removes the job from the queue.
begin
dbms_job.remove(jobno);
commit;
end;

 

[kokiri]

Hi dbtoo2001,

Thanks for your response and your sample codes. I wasn't able to run your sample data due to permission. I'm waiting for grant permission. I've looked at your code and I wasn't able to figure it out where the temp table to collect query result.

I would like to collect query results every hours for next few weeks.

Thanks.

 

[kokiri]

I'm still waiting on permission.
Meanwhile, I've created a procedure which will append the data to temp table.
I need to run this procedure every hour for next few weeks and this is what I've typed in sql+. procedure name is batch_test.

dbms_job.submit1, 'batch_test;', trunc(sysdate), 'trunc(sysdate)+1+1/24';

Please help.

Thanks.

 

[sem]

var jobno number
dbms_job.submitjobno, [/b]'begin batch_test; end;'[/b], sysdate, 'trunc(sysdate,''HH'')+1/24');
commit;
print jobno


Regards, Dima

 

[kokiri]

Hi Dima,

Thanks for your code and I'm not sure how to fix the error.

I received the following error:
1 var jobno number
2 dbms_job.submitjobno, 'begin batch_test; end;', sysdate, 'trunc(sysdate,''HH'')+1/24');
3 commit;
4* print jobno
SQL> /
var jobno number
*
ERROR at line 1:
ORA-00900: invalid SQL statement

I've tried to remove job and received the following error:

SQL> dbms_job.removejobno);
SP2-0734: unknown command beginning "dbms_job.r..." - rest of line ignored.
SQL> commit;

Commit complete.

SQL> print jobno

JOBNO

Thanks for your help.

 

[sem]

Oops, this sql*plus script had an error: should be

var jobno number
exec dbms_job.submitjobno, 'begin batch_test; end;', sysdate, 'trunc(sysdate,''HH'')+1/24');
commit;
print jobno


Regards, Dima

 

[kokiri]

Hi Dima,

Thanks for your reply. I'm still getting an error:
1 var jobno number
2 exec dbms_job.submitjobno, 'begin batch_test; end;', sysdate, 'trunc(sysdate,''HH'')+1/24');
3 commit;
4* print jobno
SQL> /
SP2-0552: Bind variable "JOBNO" not declared.

Thanks.

Di

 

[sem]

I still can not understand how you make all these command run in one shot (as they're numbered as lines of one procedure call). Can you explain what tool you use and how exactly you invoke those commands?

Regards, Dima

 

[kokiri]

Hi Dima,

I've tried again and this time some how it works. I don't get it.

Thanks for your help.

Di

 

[sem]

What version of sql*plus do you use? If it's quite old, try to execute those commands one-by-one or store them into a file and execute it using start (@) command.

Regards, Dima

 

[kokiri]

9.2.0.1.0

Thanks.

 

[SantaMufasa]

Kokiri/Dima,

I'll suggest a theory on what happened, then Kokiri can confirm/deny that it happened:

In Kokiri's last post of Oct. 31, the way he got the line numbers on his code was by issuing at the SQL> prompt the SQL*Plus command, &quot;get <filename>&quot;.

In Kokiri's first post of Nov. 3, the way he got it to work successfully was by issuing the command at the SQL> prompt, &quot;@<filename>&quot;.

Could that be the case, Kokiri?

Dave
Sandy, Utah, USA @ 17:09 GMT, 10:09 Mountain Time

 

[sem]

Ok, it may be even more simple:

1. open editor (Notepad ) by ed
2. paste text copied from browser
3. close editor (Save-Yes)


Regards, Dima

 

[kokiri]

Hi SantaMufasa,

Actually, I did not use @xxx nor get filename. I typed provided codes using sql+ edit tool.

Thanks.