Quick update to package from previous post.
Here is the zip of the download: zip(updated)
I added these procs to collect select statements on a source production database and to copy them back (as clobs) to the test database:
collect_select_statements(max_number_selects, include_pattern1,...,include_pattern10, exclude_pattern1,...,exclude_pattern10) This proc is run on the the source database to collect select statements including statements that have the include patterns and excluding those who have the exclude patterns. Patterns use LIKE conditions %x%. copy_select_statements(link_name) copies select statements from remote source database pointed to by link_name's db link.
I intended to have this functionality in the package from the beginning but I got bogged down getting ORA-22992 errors trying to copy a clob from a remote table into a variable. So, now I just execute collect_select_statements on the remote database over the link and run copy_select_statements to copy the populated table back to the test database:
execute TEST_SELECT.
collect_select_statements@myqalink
(10,include_pattern1=>'%TARGETTABLE%');
execute TEST_SELECT.
copy_select_statements('myqalink');
