Hello Folks,
I'm working on a requirement to automate the process of generating report(csv file) using metadata info stored in an Oracle table and E-mail it to respective people.
Meta data table:
Report_ID,Report_SUB_ID,Report_DB,Report_SQL,Report_to_email_Id
1,1,DEV,'select * From emp', xyz@abc.com
1,2,DEV,'select * From dept',abc@xyz.com
1,3,DEV,'select * From sal',abc@qwr.com
2,1,UAT,'select * From emp1', test@test.com
2,2,UAT,'select * From dept1', yyy@xxx.com
3,1,PRD,select * From xyz', ppp@mmm.com
To implement this I'm planning to store the metadata entries into Unix shell variables and create a loop and process each record(report_SUB_ID,DB, SQL etc) in the loop indivudally.
Then,planning to execute each SQL in the respective DB and spool the output of the SQL query to a csv file on the unix server and then attach it and send it to the respective user.
Can you please advise if my approach is right ? If not , please advise whats the best possible way to achieve this?
If yes , please suggest what type of shell variables I have to create and how can I looop through these values.
Thanks in Advance
Regards,
Venkat