need help in creating a batch script to run a procedure

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
lalat06bag
Posts: 51
Joined: 10 Jan 2018 15:21

need help in creating a batch script to run a procedure

#1 Post by lalat06bag » 02 Oct 2018 09:48

I want to create a batch script to run a database procedure and give me the results in a flat file.
I am using exadata. I want to run the batch from a remote windows server.
1. Below is the procedure
sp_analyze_wt.sql

CREATE OR REPLACE PROCEDURE x.sp_analyze_x
AUTHID DEFINER
AS
BEGIN
dbms_stats.gather_schema_stats(ownname=>'x', estimate_percent=>100, degree=>4, cascade=>TRUE, options=>'GATHER');
END;
/

CREATE OR REPLACE PUBLIC SYNONYM SP_ANALYZE_x FOR x.SP_ANALYZE_x;

AUDIT GRANT ON x.SP_ANALYZE_x BY ACCESS WHENEVER SUCCESSFUL;
AUDIT GRANT ON x.SP_ANALYZE_x BY ACCESS WHENEVER NOT SUCCESSFUL;

GRANT EXECUTE ON x.SP_ANALYZE_x TO xUSR;
GRANT EXECUTE ON x.SP_ANALYZE_x TO x;
/*

GRANT EXECUTE ON x.SP_ANALYZE_x TO x_BATCH;
GRANT EXECUTE ON x.SP_ANALYZE_x TO x_EXECUTE;
*/

2. to run the above procedure, exec sp_analyze_x

3 . result is below

select owner, table_name, to_char(last_analyzed,'yyyy-mm-dd hh:mm:ss') from all_tables
where
last_analyzed is not null
and
owner = 'x'
order by table_name

I need to create a batch script which would run the above procedure and give me the result in a flat file.


Post Reply