Reports Using SQL Script

Speaker Notes

Slide 1:

Using a script file allows flexibility in design. In this presentation we will look at designing a report in a script file using a series of parameters to set up the look.

Note that we can use single or multiple tables or we can set up a view to use with the report that contains the information from multiple tables.

Slide 2:

To start writing the script, key in edit with the script name at the SQL> prompt. If the script does not exist, you will get the message box saying it cannot find the file and asking if you want to create it. Yes will bring up the editor to create the script. The script will have the .sql extension and the name you used in the edit.

Remember when creating script, if you leave a space on the line after the / you will not see those annoying Input truncated messages.

Slide 3:

Note that the column heading Job Code did not appear over the Job. I could have made it better by using the bar between the words, but even then it would not have fit completely.

When working with scripts you go back and forth to the editor with one-line commands. If you highlight a previous line and while you are still highlighting it you press the right mouse key, the line will be reproduced for you at the SQL prompt. A handy editing feature!

Slide 4:

The donor table and the first_pay table both had a name column/field, therefore the header that was set up in the script applies to both name columns.

The CLEAR COLUMNS statement that we saw at the beginning of the script can be issued at the SQL prompt to clear the columns that were set up in the script. Then we could SELECT * FROM donor etc. with no impact on the columns.

Slide 5:

After running a script, you need to clean up after yourself or the impact will continue in other work you do.

Slide 6:

When the SELECT that includes the name of the table is not included in the script, the script will be applied to the last table that was active. So, if I have just been working with donor and then do @ rptslide1 (remember, rptslide1 did not contain a SELECT for a table), the report will be run using donor.

Note that in the script there is no semi-colon after the SELECT.

Slide 7:

Note it is TTITLE and that the title not only creates the title, it also creates the date and page number on the line above.

Note that the space between @ rptslide3 is optional. It could be keyed as @rptslide3.

Slide 8:

I decided to put Job Code on two lines, so I inserted a bar between the words. That meant I could format it for 5 characters. When doing formatting, note that Oracle inserts one line between columns.

I also had to change the setting of LINESIZE. With 65, I got word wrap. With 80, everything fit on one line. To determine line size, add up the characters in the FORMAT and don't forget the between columns character.

Slide 9:

BREAK means that it will break when the jobcode changes. You can see in the example that the second, third and records all have jobcode of CI, the jobcode only appears once.

Breaking will not work unless the table is ordered by jobcode. Otherwise the breaking would only happen if two jobcodes that were the same happen to be next to each other.

Note again that in the script there is no semi-colon after the select.

Slide 10:

The COMPUTE can also be done on AVG etc.

The results are on the next slide.

Slide 11:

Notice the layout with the ***** and the sum. This line also includes the two totals that were requested.

Notice that there is also a page break based on the default number of lines. We will see more about defaults later.

Slide 12:

To change the order that the information appears, I use a view. The view can present some of the information, all of the information and in any order needed. Frequently a view is used as the input to the report scripts we have been looking at.

Slide 13:

Output from rptslide7 which uses the view_first_pay with jobcode in the first column.

Slide 14:

Note I have computed two totals here, in the handouts I only did one. You can compute as needed.

Slide 15:

You can compute SUM, AVG, MAX and MIN. I tried computing multiples on one column and it showed the last.

Slide 16:

Notice that the BREAK is done on one line. First the report break is listed and then the minor or jobcode break. Each has the word ON before it but there is only one BREAK line.

The SKIP 2 means skip two lines after the break and before the next line.

Note there can be more than two levels of breaks just list the major break first and then the intermediate break and then the minor break or if you are using report totals list the report break first, then the major break, then the intermediate break and then the minor break.

Slide 17:

The output from @ rptslide10.

Note the minor total lines based on a break in jobcode and the SKIP 2 which leaves two lines between the minor line(jobcode total line) and what ever follows.

Slide 18:

Please see the notes for additional examples.

This will use SHOW ALL to show the things that can be set within Oracle. Some of them have been covered in previous slides and some will be covered in the handout/notes.

See manual, help or reference books for detailed explanations.

Slide 19:

Continuation of show all.

Slide 20:

clear columns clears the headings and formatting.

clear breaks clears the breaks and totaling.

ttitle off clears the heading

Slide 21:

CLEAR COLUMNS can be used to reverse the setting.

Slide 22:

This is the way the developer can change column size if the default is too large. It is suggested that developers could put together a script that they run when the start Oracle with any standard column settings they want to use.