You can also build your own reports. Have a look at Jeff Smith's blog (http://www.thatjeffsmith.com/) for lots of great information about SQL Developer and SDDM. Specifically, look at the bottom of this page for links to Jeff's posts about the reporting capability: https://www.thatjeffsmith.com/sql-developer/. One good way to build your own report is to copy one of the built-in reports and modify it.
But, what if I want something that none of the built-in reports has, or I want to do formatting that the reporting tools don't do natively? For instance, what if you want to print a report something like this?
This is a data dictionary for the HR sample schema formatted according to my client's wishes for data dictionaries. There are very good reports from the Oracle data dictionary that are built into SQL Developer, and I like the master child formatting they have where you can click on the table name and see information about the table's columns, but this is more of a report that you can print off if you need to, or read into an Excel spreadsheet.
No worries. You can write your own report in PL/SQL to produce HTML that you output with the DBMS_OUTPUT built-in package. Then in SQL Developer you create it as a PL/SQL DBMS_OUTPUT style report:
The query for this one is:
BEGIN
DATA_DICTIONARY;
END;
So how did I write this? You COULD just hand write the PL/SQL, but since this is going to produce HTML, I like to write it as a PL/SQL server page. I've written extensively about the many uses for PL/SQL server pages (PSPs) before, for instance, see this about using a PSP to produce Spreadsheet ML: https://it.toolbox.com/blogs/jjflash/spreadsheetml-in-a-pl-sql-server-page-part-1-of-2-022015.
Basically, a PSP is an HTML page with some PL/SQL embedded in it. The PL/SQL is used for flow control like LOOPs and to embed data from the database into the page. You compile it with the LOADPSP utility that comes with all Oracle databases, including Oracle XE. And the compiled PSP is actually a stored procedure that uses the PL/SQL Web Toolkit to write to the web - this is the same underlying technology as is used by Oracle Application Express - i.e. APEX. The specific toolkit procedure the compiled PSP uses is HTP.PRN which outputs all of the HTML. But we aren't going to write to the web, we want to write our HTML to DBMS_OUTPUT.
So what I did was compile my PSP with LOADPSP, then edited the resulting stored procedure, named DATA_DICTIONARY. I made three changes:
- Since the underlying SQL uses the USER_* data dictionary views, I wanted to make sure it shows the tables owned by the user that executes it. So I added "AUTHID CURRENT_USER" to the procedure definition. That makes it an Invoker's Rights procedure.
- I did a FIND/REPLACE to change all occurrences of HTP.PRN to DBMS_OUTPUT.PUT.
- I found out that when you are writing to the DBMS_OUTPUT buffer, nothing gets written until there is a call to DBMS_OUTPUT.PUT_LINE; So I added "DBMS_OUTPUT.PUT_LINE('');" as the last line of the procedure.
So I hope this inspires you to try the SQL Developer reporting features. And if the need arises, write your own reports your way.
No comments:
Post a Comment