Hopefully you've learned by now that I tend to lure you into things by telling you not to worry and just follow the instructions. Well, using the built-in SQL tools will let you build simple reports, but with a bit of practice you can actually create pretty sophisticated reports . Note Most shops usually use a third-party tool such as Brio, SQR, or Crystal Reports for the bulk of their report generation. Users can easily learn some of these, IT staff can use others for intricate reports, and all of them fit nicely in the Oracle environment. However, be aware that Oracle has released a significantly improved Oracle Discoverer. Discoverer presents a different model for users to access the database data, along the idea of having "workbooks" that contain the data for individual business areas. I encourage you to investigate this tool further. We are going to jump right into using the power of SQL*Plus. Take a look at the following report, which shows the very first customer for the "Guerrilla Oracle Waiver Exam Testing Institute":
And here's the SQL*Plus code: rem rem Tests Taken report rem written by Jerome Engles rem rem purpose: this is an ad hoc report to list all tests taken, rem by type of test rem set headsep ttitle 'All Tests Taken' btitle 'Guerrilla Exams' Column TEST_ID heading 'Test Identification ' Column TEST_ID format 999999999 Column STUDENT_ID heading 'Student Identification ' Column STUDENT_ID format 999999999 Column LOCATION heading 'Location' format a30 Break on TEST_ID Set LINESIZE 80 Set PAGESIZE 55 Set NEWPAGE 0 SPOOL C:\TEST.LST SELECT TEST_ID, STUDENT_ID, LOCATION FROM TEST_HISTORY ORDER BY TEST_ID; End of spooling
Here's how things match up:
From this simple example you should have the general idea, so let's look at some rules: By convention, the headsep (head separator) character is the broken vertical bar, but if your keyboard doesn't support it, you can use another key. Just be careful not to use something that may appear in one of your headings. The head separator tells the system to put the heading on more than one line. You'll see this in the next example, where we expand our simple report. You can add comments either with the rem command at the beginning of the line, or by enclosing them within these keystrokes: /* ... */ You can use this notation anywhere ! |