My first real exposure to a real database was Sybase. It actually is a fairly nice “little” database that you can come to grips with. I mean little when compared to behemoth that is Oracle. I don’t want to go against the underdog, but it is pretty amazing the stuff that Oracle packs into their solution. In addition to a relational database with all the friendly editors for creating tables and procedures, they have tools for the command line junkie as well.
How I always end up back at the command prompt is a mystery to me. Yet, despite finding Oracle to be a pretty large system they did provide a simple little command line tool which can be used for querying data from the database.
This little command line tool is called sqlplus. With this, I can extract some data from the database and manipulate it myself and then load it right back in. The task tends to be a small one off, so there is not much point in writing a complicated program using JDBC.
As Sqlplus is a command line tool it is just as plain as you might imagine. When you log in, you are simply left at a prompt to enter your SQL commands. There are no GUI’s or wizards to help out, but either by design or accident those fine folks at Oracle tucked in quite a few formatting options to make this an excellent tool not only for performing queries but also for extracting data or even producing small reports.
Setup
At the end of the article is a list of all the scripts used to create these tables along with populating them.
Starting sqlplus is trivial if your environment is already properly setup. Sorry, setting up Oracle is beyond the scope of this article.
sqlplus user/password@tnsid<disclaimer>This is not even remotely safe to put the password into a script as some hacker or disgruntled employee will find the user and password information and undoubtedly do naughty things</disclaimer> This is not much better if used on the command prompt it could also be found by someone examining the password history. That isn’t an important issue for a developer on a development machine just to keep in mind for production.
Command Line
More than just a simple command line tool, this one can also be used in scripts. Simply run sqlplus but also pass the name of a parameter file to run as its argument.
sqlplus user/password@tnsid @mysqlquery.sqlSqlplus will take this file as a list of input and begin to process each line in the file as if you were typing it. Don’t forget to add a “quit” statement as the last line of your script otherwise, sqlplus will stay at the prompt after running the script.
Nobody is perfect, not even the defaults that were chosen by oracle for outputting query data.
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> SELECT * FROM dbo.conair_emp;
FIRST
----------------------------------------
LAST IQ
---------------------------------------- ----------
EMAIL
--------------------------------------------------------------------------------
max
musterman 98
max@musterman.com
mathew
doe 115
mathew@doe.com
FIRST
----------------------------------------
LAST IQ
---------------------------------------- ----------
EMAIL
--------------------------------------------------------------------------------
mark
doe 115
mark@doe.com
luke
doe 115
It is good that there are options for doing a bit of formatting the raw query data because the default values that are chosen by default are pretty hideous.
When changing the default output format it is actually possible to create simple reports at the same time.
Simple reporting
The most important part of any report, formal or informal is some way to save the actual report data. It is possible to run the sqlplus command for a given sql statement but unless the output is redirected to a file, it will be lost forever. So, perhaps the most important statement is how to redirect the output to a file. This is done using the spool command. us has the spool command which when activated will redirect the output to the filename given in the current directory.
| Command | Parameter | Description |
|---|---|---|
| spool | <filename> | Puts all screen output into the file named <filename> |
| spool | off | stops logging output to the spool file. |
Never forget to turn off your spooling or otherwise your entire interactive session will end up in your output.
Beyond saving the output, we need to get it out of the database in a format that is not the ugly default format of sqlplus. The simplest and most effective way to make the output more friendly is to change the format for each field of the report. This is done by using the “column” command.
When using A32 or 999 is mainly just setting a length for the fields that will be displayed. There is no real formatting take place.
| Command | Parameter | Description |
|---|---|---|
| column | format | The actual column format to use |
| column | format A32 | Formats the output as a alpha numeric field 32 characters long. |
That doesn’t mean it is not possible for some simple formatting of numeric values. The comma and period can be used to format both larger numbers or fractional values to display in a more user friendly manner.
| Command | Parameter | Description |
|---|---|---|
| column | format 999.99 | Formats the output as an floating value that will take up at most three columns (ie 0 – 999) with at most two decimal places |
| column | format 9,999.99 | Formats the output as a floating point value that is at most four digits with two decimal places. This will also put a comma in for the thousands separator. |
It might be possible to do this with European formatting but I didn’t find it necessary to do that and so I don’t have any further information on how do it.
Thus by simply adding formatting to the fields, the report becomes instantly friendly.
column first format A15
column last format A15
column iq format 9,999.99
column email format A32
spool report1.rpt
select * from conair_emp;
spool ofThe information no longer wraps on the screen, the columns are not much wider than the actual information and everything looks pretty tidy.
FIRST LAST IQ EMAIL
--------------- --------------- --------- --------------------------------
max musterman 98.00 max@musterman.com
mathew doe 2,115.00 mathew@doe.com
mark doe 1,515.00 mark@doe.com
luke doe 115.00 luke@doe.com
john doe 978.00 john@mydomain.com
john lennon 115.00 johnlennon@beatles.com
paul mccartney 9,115.00 paulmccartney@beatles.com
george harrison 115.00 georgeharrison@beatles.com
ringo star 115.00 ringostar@beatles.com
9 rows selected
This is by no means everything that can be done for the different columns of our report. It is also possible to change the headings at the same time we are changing the formats. This might be not as necessary in this particular report as the column names pretty much match the values that are selected.
column first heading 'First name' format A15
column last heading 'Last name' format A15
column iq heading 'Hourly wage|in thousands' format 9,999.99
column email heading 'Email Addr' format A32
spool report1.rpt
select * from conair_emp;
spool ofNot every column name was quite perfect, this more a feature of the idiot person who actually created the database to begin with.
First name Last name Hourly wage Email Addr
in thousands
--------------- ----------------- ------------ --------------------------------
max musterman 98.00 max@musterman.com
mathew doe 2,115.00 mathew@doe.com
mark doe 1,515.00 mark@doe.com
luke doe 115.00 luke@doe.com
john doe 978.00 john@mydomain.com
john lennon 115.00 johnlennon@beatles.com
paul mccartney 9,115.00 paulmccartney@beatles.com
george harrison 115.00 georgeharrison@beatles.com
ringo star 115.00 ringostar@beatles.com
9 rows selected
It is also possible to create multi-line descriptions for a column by using the pipe symbol to mark where the field name should be split.
If the report was just a very small report or used for some very casual internal purpose then this would probably be enough. We have our data, each column is understandable and the important values are formatted in some manner.
This isn’t the limit of what can be done for reporting. Just like most reports it is possible to add titles and footer information to the report. this is done using the ttitle and btitle commands. This report will actually be printed to paper so we change the length so it can be properly paginated. This is done using the pagesize command.
set pagesize 20
set heading on
set linesize 80
set trimspool on
ttitle center "company wage report " skip 2
btitle left "-----------------------------------------------------------------------------" skip 1 -
left "confidential information - internal " -
right "page " format 999 sql.pno
column first heading 'First name' format A15
column last heading 'Last name' format A15
column iq heading 'Hourly wage|in thousands' format 9,999.99
column email heading 'Email Addr' format A32
spool report1.rpt
select * from conair_emp;
spool ofI have made the arbitrary decision that the page will only be 20 lines long as I don’t want to fill my blog up with a lot of blank lines. however, this command will ensure that the page is the proper length and is padded to the end of the page.
company wage report
Hourly wage
First name Last name in thousands Email Addr
--------------- --------------- ------------ --------------------------------
max musterman 98.00 max@musterman.com
mathew doe 2,115.00 mathew@doe.com
mark doe 1,515.00 mark@doe.com
luke doe 115.00 luke@doe.com
john doe 978.00 john@mydomain.com
john lennon 115.00 johnlennon@beatles.com
paul mccartney 9,115.00 paulmccartney@beatles.com
george harrison 115.00 georgeharrison@beatles.com
ringo star 115.00 ringostar@beatles.com
-----------------------------------------------------------------------------
confidential information - internal page 1
9 rows selected.There are two more commands that can be used depending on how big the report is expected to be. The first is the “clear screen” command. This will just clear the screen when it is encountered. this is probably quite useful for some very tiny report that will give a few lines of information. The cleared screen makes the information easy to find on your terminal session.
The second command is the “termout” command. This when this command is set to off, then no more output will be displayed on the terminal screen. This would be a good command when hundreds or thousands of lines of output are expected.
It is also possible to calculate totals and have breaks between types of data, but I will leave that for another time.
Extracting data
Actually most of the general reporting could be used in the extracting of data. We might want to do some formatting of our data but more likely than not we might not be as interested in the headings. It is possible to do a simple query and basically drop the headings and we have an output format.
Dropping the headings is done by setting the pagesize to zero. This will then cause no page breaks and it will automatically suppress the headings.
max musterman 98 max@musterman.com
mathew doe 2115 mathew@doe.com
mark doe 1515 mark@doe.com
luke doe 115 luke@doe.com
john doe 978 john@mydomain.com
john lennon 115 johnlennon@beatles.com
paul mccartney 9115 paulmccartney@beatles.com
george harrison 115 georgeharrison@beatles.com
ringo star 115 ringostar@beatles.com
9 rows selected.
This actually creates a very simple fixed length record that can be parsed by your program. just break each line into its composite fields when extracting. I don’t know a lot of developers who are still interested in fixed length output data. Sure you can parse it but why when you can have a comma separated file.
Here too, oracle helps. it is possible to set the field separator.
set colsep ‘;’
This will add a semicolon to our output automatically turning it into csv data.
max ;musterman ; 98;max@musterman.com
mathew ;doe ; 2115;mathew@doe.com
mark ;doe ; 1515;mark@doe.com
luke ;doe ; 115;luke@doe.com
john ;doe ; 978;john@mydomain.com
john ;lennon ; 115;johnlennon@beatles.com
paul ;mccartney ; 9115;paulmccartney@beatles.com
george ;harrison ; 115;georgeharrison@beatles.com
ringo ;star ; 115;ringostar@beatles.com
9 rows selected.
Yes, now our data has now been turned into the ugliest comma separated data ever.
Note: the semicolon is the csv file delimiter in excel in some countries.
Extra Credit
There is another way to run these scripts without adding a quit command. Simply echo the password into the sqlplus command when running a script. Sqlplus recognizes that it is in batch mode and quits when the commands are finished.
echo secretpass | sqlplus myuser@cdatabase @commands.sql
This might be marginally safer but not by much. If this were a script, then your password is still in the script to be found by someone.
There is also a better way to create our csv file. It requires a bit more effort when creating our script. Simply concatenate each field that you wish to have into a single string.
SELECT first || ‘,’ || last|| ‘,’ || iq|| ‘,’ || email FROM dbo.conair_emp;
max ,musterman ,98,max@musterman.com
mathew ,doe ,2115,mathew@doe.com
mark ,doe ,1515,mark@doe.com
luke ,doe ,115,luke@doe.com
john ,doe ,978,john@mydomain.com
john ,lennon ,115,johnlennon@beatles.com
paul ,mccartney ,9115,paulmccartney@beatles.com
george ,harrison ,115,georgeharrison@beatles.com
ringo ,star ,115,ringostar@beatles.comThe data is really not much nicer to look at than our previous csv attempt but that is hardly the databases, nor even sqlplus’s fault. If the table had been created with varchar fields instead of char fields, then the data would not contain the trailing spaces.
max,musterman,98,max@musterman.com
mathew,doe,2115,mathew@doe.com
mark,doe,1515,mark@doe.com
luke,doe,115,luke@doe.com
john,doe,978,john@mydomain.com
john,lennon,115,johnlennon@beatles.com
paul,mccartney,9115,paulmccartney@beatles.com
george,harrison,115,georgeharrison@beatles.com
ringo,star,115,ringostar@beatles.comIt isn’t the fact you can do a query from the command prompt that is so amazing but some of the options that you can turn on to turn a small query into a small report, or a small data extract.
Setup Scripts
create.sql
drop table conair_emp;
commit;
/
create table conair_emp
(
first char(10) not null,
last char(10) not null,
iq float not null,
email char(30) not null
);
commit;
/
prompt
prompt test table conair_emp created
describe conair_emp
fill.sql
begin
delete from conair_emp;
commit;
insert into conair_emp (first,last,iq,email) values('max','musterman',98,'max@musterman.com');
insert into conair_emp (first,last,iq,email) values('mathew','doe',2115,'mathew@doe.com');
insert into conair_emp (first,last,iq,email) values('mark','doe',1515,'mark@doe.com');
insert into conair_emp (first,last,iq,email) values('luke','doe',115,'luke@doe.com');
insert into conair_emp (first,last,iq,email) values('john','doe',978,'john@mydomain.com');
insert into conair_emp (first,last,iq,email) values('john','lennon',115,'johnlennon@beatles.com');
insert into conair_emp (first,last,iq,email) values('paul','mccartney',9115,'paulmccartney@beatles.com');
insert into conair_emp (first,last,iq,email) values('george','harrison',115,'georgeharrison@beatles.com');
insert into conair_emp (first,last,iq,email) values('ringo','star',115,'ringostar@beatles.com');
commit;
end;
/
report1.sql
clear screen
column first format A15
column last format A15
column iq format 9,999.99
column email format A32
set term on
spool report1.rpt
SELECT * FROM dbo.conair_emp;
spool off
report2.sql
clear screen
column first heading 'First name' format A15
column last heading 'Last name' format A15
column iq heading 'Hourly wage|in thousands' format 9,999.99
column email heading 'Email Addr' format A32
set term on
spool report2.rpt
SELECT * FROM dbo.conair_emp;
spool off
report3.sql
SET PAGESIZE 20
SET HEADING on
SET LINESIZE 80
set trimspool on
set termout off
ttitle center "company wage report " skip 2
btitle left "-----------------------------------------------------------------------------" skip 1 -
left "confidential information - internal " -
right "page " format 999 sql.pno
column first heading 'First name' format A15
column last heading 'Last name' format A15
column iq heading 'Hourly wage|in thousands' format 9,999.99
column email heading 'Email Addr' format A32
spool report3.rpt
SELECT * FROM dbo.conair_emp;
spool off
export1.sql
SET PAGESIZE 0
SET LINESIZE 999
set trimspool on
spool export1.out
SELECT * FROM dbo.conair_emp;
spool off
export2.sql
SET PAGESIZE 0
SET LINESIZE 999
set trimspool on
set colsep ';'
spool export2.out
SELECT * FROM dbo.conair_emp;
spool off
quit
export3.sql
SET PAGESIZE 0
SET LINESIZE 999
set trimspool on
set colsep ';'
spool export3.out
SELECT first || ',' || last|| ',' || iq|| ',' || email FROM dbo.conair_emp;
spool off
quit
Quite nice post, I definitely adore this site, keep on it.
Thanks for the feedback. Sometimes I think I am speaking to an empty room.
It is perfect time to make some plans for the future and it is time to be happy.
I have read this post and if I could I wish to suggest
you few interesting things or suggestions. Maybe you could write next articles referring to this article.
I wish to read more things about it!