OWB SQL Reports in Code Samples
By David Allan on Oct 01, 2010
The SQL reports on OWB for SQLDeveloper have been uploaded into the code sharing here. There are a number of reports you can take, change, enhance or whatever and also share your reports or changes by putting back into the code sharing site for others to benefit from. There are other useful odds and ends in the code samples such as KMs and other experts and best of all.... we all (including you) can access and update. If you just want to take the SQL, you can open the OWBReports.xml file and search for the various queries.
There are useful execution reports plus some deployment ones (see what mappings are deployed, their parameters, when they were deployed, their state etc.).
For example the mappings deployment report you can see the parameters with their default values, etc.
Plus I can see the deployments, when, type of object and status.
To see the SQL for these right click on report, select 'Edit';
You then get an editor where you can change, copy or view the SQL for the master and detailed parts of the report. The child reports use the standard mechanism within SQLDeveloper to relate a detailed report to the master (use :BIND variables).
There are other reports such as seeing the registered locations which are handy with details of all the properties plus see execution reports for mappings which are master detail reports which drill to throughput, audit information, errors, parameters and timing analysis. There are also some rudimentary ones for looking at process flows that let you see the execution audit for process flow activities. These were created so I could quickly analyze what was going on just by going right at the public views rather than using the OWB designer or browser. I'm sure many of you have better ones that it would be useful to share and make a richer set.
If you are using 11gR1 of OWB you will have to set the workspace by calling the owbsys.wb_workspace_management.set_workspace from the SQL worksheet for the connection, likewise if you want a different workspace from the default...if you have many workspaces.
So check out these reports on the Oracle code samples site, change, fix, add and share!