Hi,
Please let me know if you have any thoughts on how to read a table that has all the oracle sql files or shell scripts at the job and step level to identify all the tables that does merge, update, delete, insert, create, truncate, alter table (ALTER TABLE XYZ RENAME TO ABC) and call them out separately to write to table and to a file as well. We have thousands of jobs and trying to write a script to gather all these details into a table and file.
Table Name: Steps
JOB_ID STEP_ID Script_Name
12345 1 xfer_process.sh
12345 2 merge_process.sql
12345 3 load_step.sql
Table Name: Schedule
JOB_ID SCHEDULE
12345 DAILY 8 AM
5678 EVERY WEDNESDAY 10 AM
8989 DAILY 4 PM
Output
JOB ID STEP_ID Script_Name Dependent_Job Succeeding _Job Dependent_Job_Schedule Succeeding _Job_Schedule SCHEMA_XF Read SCHEMA_XF Build SCHEMA_XF Insert SCHEMA_XF Update SCHEMA_XF Delete SCHEMA_XF Truncate SCHEMA_XF Merge SCHEMA_XF Swap
Column SCHEMA_XF Build means create table as select * from table a
Column SCHEMA_XF Swap means alter table a rename to b
Thank you