{"id":878,"date":"2016-04-28T18:08:27","date_gmt":"2016-04-28T18:08:27","guid":{"rendered":"http:\/\/blog.paranoidprofessor.com\/?p=878"},"modified":"2016-04-28T18:08:27","modified_gmt":"2016-04-28T18:08:27","slug":"oracle-and-batch-reporting","status":"publish","type":"post","link":"https:\/\/blog.paranoidprofessor.com\/index.php\/2016\/04\/28\/oracle-and-batch-reporting\/","title":{"rendered":"Oracle and batch reporting"},"content":{"rendered":"

My first real exposure to a real database was Sybase.\u00a0 It actually is a fairly nice “little” database that you can come to grips with.\u00a0 I mean little when compared to behemoth that is Oracle.\u00a0 I don’t want to go against the underdog, but it is pretty amazing the stuff that Oracle packs into their solution.\u00a0 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.<\/p>\n

How I always end up back at the command prompt is a mystery to me.\u00a0 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.<\/p>\n

This little command line tool is called sqlplus.\u00a0 With this, I can extract some data from the database and manipulate it myself and then load it right back in.\u00a0 The task tends to be a small one off, so there is not much point in writing a complicated program using JDBC.<\/p>\n

As Sqlplus is a command line tool it is just as plain as you might imagine.\u00a0 When you log in, you are simply left at a prompt to enter your SQL commands.\u00a0 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.<\/p>\n

Setup<\/strong><\/h3>\n

At the end of the article is a list of all the scripts used to create these tables along with populating them.<\/p>\n

Starting sqlplus is trivial if your environment is already properly setup.\u00a0 Sorry, setting up Oracle is beyond the scope of this article.<\/p>\n

\n
sqlplus user\/password@tnsid<\/code><\/pre>\n<\/div>\n
<disclaimer><\/strong><\/em>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> <\/strong><\/em>This is not much better if used on the command prompt it could also be found by someone examining the password history.\u00a0 That isn’t an important issue for a developer on a development machine just to keep in mind for production.<\/p>\n
Command Line<\/strong><\/h3>\n
More than just a simple command line tool, this one can also be used in scripts.\u00a0 Simply run sqlplus but also pass the name of a parameter file to run as its argument.<\/p>\n
\n
sqlplus user\/password@tnsid\u00a0 @mysqlquery.sql<\/code><\/pre>\n<\/div>\n
Sqlplus will take this file as a list of input and begin to process each line in the file as if you were typing it.\u00a0 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.<\/p>\n
Nobody is perfect, not even the defaults that were chosen by oracle for outputting query data.<\/p>\n
\n
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\r\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\r\nData Mining and Real Application Testing options\r\n\r\nSQL> SELECT * FROM dbo.conair_emp;\r\n\r\nFIRST\r\n----------------------------------------\r\nLAST                                             IQ\r\n---------------------------------------- ----------\r\nEMAIL\r\n--------------------------------------------------------------------------------\r\nmax\r\nmusterman                                        98\r\nmax@musterman.com\r\n\r\nmathew\r\ndoe                                             115\r\nmathew@doe.com\r\n\r\nFIRST\r\n----------------------------------------\r\nLAST                                             IQ\r\n---------------------------------------- ----------\r\nEMAIL\r\n--------------------------------------------------------------------------------\r\n\r\nmark\r\ndoe                                             115\r\nmark@doe.com\r\n\r\nluke\r\ndoe                                             115\r\n<\/code><\/pre>\n<\/div>\n
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.<\/p>\n
When changing the default output format it is actually possible to create simple reports at the same time.<\/p>\n
Simple reporting<\/strong><\/h3>\n
The most important part of any report, formal or informal is some way to save the actual report data.\u00a0 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.\u00a0 So, perhaps the most important statement is how to redirect the output to a file.\u00a0 This is done using the spool command.\u00a0 us has the spool command which when activated will redirect the output to the filename given in the current directory.<\/p>\n\n\n\n\n\n
Command<\/th>\nParameter<\/th>\nDescription<\/th>\n<\/tr>\n
spool<\/td>\n<filename><\/td>\nPuts all screen output into the file named <filename><\/td>\n<\/tr>\n
spool<\/td>\noff<\/td>\nstops logging output to the spool file.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Never forget to turn off your spooling or otherwise your entire interactive session will end up in your output.<\/p>\n
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.\u00a0 The simplest and most effective way to make the output more friendly is to change the format for each field of the report.\u00a0 This is done by using the “column” command.<\/p>\n
When using A32 or 999 is mainly just setting a length for the fields that will be displayed.\u00a0 There is no real formatting take place.<\/p>\n\n\n\n\n\n
Command<\/th>\nParameter<\/th>\nDescription<\/th>\n<\/tr>\n
column<\/td>\nformat<\/td>\nThe actual column format to use<\/td>\n<\/tr>\n
column<\/td>\nformat A32<\/td>\nFormats the output as a alpha numeric field 32 characters long.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
That doesn’t mean it is not possible for some simple formatting of numeric values.\u00a0 The comma and period can be used to format both larger numbers or fractional values to display in a more user friendly manner.<\/p>\n\n\n\n\n\n
Command<\/th>\nParameter<\/th>\nDescription<\/th>\n<\/tr>\n
column<\/td>\nformat 999.99<\/td>\nFormats the output as an floating value that will take up at most three columns (ie 0 – 999) with at most two decimal places<\/td>\n<\/tr>\n
column<\/td>\nformat 9,999.99<\/td>\nFormats the output as a floating point value that is at most four digits with two decimal places.\u00a0 This will also put a comma in for the thousands separator.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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.
\n<\/code><\/p>\n
Thus by simply adding formatting to the fields, the report becomes instantly friendly.<\/p>\n
\n
column first format A15\r\ncolumn last format A15\r\ncolumn iq format 9,999.99\r\ncolumn email format A32\r\n\r\nspool report1.rpt\r\nselect * from conair_emp;\r\nspool of<\/code><\/pre>\n<\/div>\n
The information no longer wraps on the screen, the columns are not much wider than the actual information and everything looks pretty tidy.
\n<\/strong><\/em><\/p>\n
\n
FIRST           LAST            IQ        EMAIL\r\n--------------- --------------- --------- --------------------------------\r\nmax             musterman           98.00 max@musterman.com\r\nmathew          doe              2,115.00 mathew@doe.com\r\nmark            doe              1,515.00 mark@doe.com\r\nluke            doe                115.00 luke@doe.com\r\njohn            doe                978.00 john@mydomain.com\r\njohn            lennon             115.00 johnlennon@beatles.com\r\npaul            mccartney        9,115.00 paulmccartney@beatles.com\r\ngeorge          harrison           115.00 georgeharrison@beatles.com\r\nringo           star               115.00 ringostar@beatles.com\r\n\r\n9 rows selected<\/code> \r\n<\/pre>\n<\/div>\n
This is by no means everything that can be done for the different columns of our report.\u00a0 It is also possible to change the headings at the same time we are changing the formats.\u00a0 This might be not as necessary in this particular report as the column names pretty much match the values that are selected.<\/p>\n
\n
column first heading 'First name' format A15\r\ncolumn last  heading 'Last name'  format A15\r\ncolumn iq    heading 'Hourly wage|in thousands' format 9,999.99\r\ncolumn email heading 'Email Addr' format A32\r\n\r\nspool report1.rpt\r\nselect * from conair_emp;\r\nspool of<\/code><\/pre>\n<\/div>\n
Not every column name was quite perfect, this more a feature of the idiot<\/del> person who actually created the database to begin with.<\/p>\n
\n
First name      Last name         Hourly wage  Email Addr \r\n                                  in thousands\r\n--------------- ----------------- ------------ --------------------------------\r\nmax             musterman                98.00 max@musterman.com\r\nmathew          doe                   2,115.00 mathew@doe.com\r\nmark            doe                   1,515.00 mark@doe.com\r\nluke            doe                     115.00 luke@doe.com\r\njohn            doe                     978.00 john@mydomain.com\r\njohn            lennon                  115.00 johnlennon@beatles.com\r\npaul            mccartney             9,115.00 paulmccartney@beatles.com\r\ngeorge          harrison                115.00 georgeharrison@beatles.com\r\nringo           star                    115.00 ringostar@beatles.com\r\n\r\n9 rows selected<\/code> \r\n<\/pre>\n<\/div>\n
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.<\/p>\n
If the report was just a very small report or used for some very casual internal purpose then this would probably be enough.\u00a0 We have our data, each column is understandable and the important values are formatted in some manner.<\/p>\n
This isn’t the limit of what can be done for reporting.\u00a0 Just like most reports it is possible to add titles and footer information to the report.\u00a0 this is done using the ttitle and btitle commands.\u00a0 This report will actually be printed to paper so we change the length so it can be properly paginated.\u00a0 This is done using the pagesize command.<\/p>\n
\n
set pagesize 20\r\nset heading on \r\nset linesize 80\r\nset trimspool on\r\n\r\nttitle center \"company wage report \" skip 2\r\nbtitle left \"-----------------------------------------------------------------------------\" skip 1 -\r\nleft \"confidential information - internal \" -\r\nright \"page \" format 999 sql.pno\r\n\r\ncolumn first heading 'First name' format A15\r\ncolumn last  heading 'Last name'  format A15\r\ncolumn iq    heading 'Hourly wage|in thousands' format 9,999.99\r\ncolumn email heading 'Email Addr' format A32\r\n\r\nspool report1.rpt\r\nselect * from conair_emp;\r\nspool of<\/code><\/pre>\n<\/div>\n
I 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.\u00a0 however, this command will ensure that the page is the proper length and is padded to the end of the page.<\/p>\n
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 company wage report\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Hourly wage\r\nFirst name\u00a0\u00a0\u00a0\u00a0\u00a0 Last name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 in thousands Email Addr\r\n--------------- --------------- ------------ --------------------------------\r\nmax\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 musterman\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 98.00 max@musterman.com\r\nmathew\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 doe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2,115.00 mathew@doe.com\r\nmark\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 doe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1,515.00 mark@doe.com\r\nluke\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 doe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 115.00 luke@doe.com\r\njohn\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 doe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 978.00 john@mydomain.com\r\njohn\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lennon\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 115.00 johnlennon@beatles.com\r\npaul\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 mccartney\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9,115.00 paulmccartney@beatles.com\r\ngeorge\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 harrison\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 115.00 georgeharrison@beatles.com\r\nringo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 star\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 115.00 ringostar@beatles.com\r\n\r\n\r\n\r\n-----------------------------------------------------------------------------\r\nconfidential information - internal\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 page\u00a0\u00a0\u00a0 1\r\n\r\n9 rows selected.<\/code><\/pre>\n<\/div>\n
There are two more commands that can be used depending on how big the report is expected to be.\u00a0 The first is the “clear screen” command.\u00a0 This will just clear the screen when it is encountered.\u00a0 this is probably quite useful for some very tiny report that will give a few lines of information.\u00a0 The cleared screen makes the information easy to find on your terminal session.<\/p>\n
The second command is the “termout” command.\u00a0 This when this command is set to off, then no more output will be displayed on the terminal screen.\u00a0 This would be a good command when hundreds or thousands of lines of output are expected.<\/p>\n
It is also possible to calculate totals and have breaks between types of data, but I will leave that for another time.<\/p>\n
Extracting data<\/strong><\/h3>\n
Actually most of the general reporting could be used in the extracting of data.\u00a0 We might want to do some formatting of our data but more likely than not we might not be as interested in the headings.\u00a0 It is possible to do a simple query and basically drop the headings and we have an output format.<\/p>\n
Dropping the headings is done by setting the pagesize to zero.\u00a0 This will then cause no page breaks and it will automatically suppress the headings.<\/p>\n
\n
max                     musterman                       98 max@musterman.com\r\nmathew                  doe                           2115 mathew@doe.com\r\nmark                    doe                           1515 mark@doe.com\r\nluke                    doe                            115 luke@doe.com\r\njohn                    doe                            978 john@mydomain.com\r\njohn                    lennon                         115 johnlennon@beatles.com\r\npaul                    mccartney                     9115 paulmccartney@beatles.com\r\ngeorge                  harrison                       115 georgeharrison@beatles.com\r\nringo                   star                           115 ringostar@beatles.com\r\n\r\n\r\n9 rows selected.\r\n<\/code><\/pre>\n<\/div>\n
This actually creates a very simple fixed length record that can be parsed by your program.\u00a0 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.\u00a0 Sure you can parse it but why when you can have a comma separated file.<\/p>\n
Here too, oracle helps.\u00a0 it is possible to set the field separator.<\/p>\n
set colsep ‘;’<\/p>\n
This will add a semicolon to our output automatically turning it into csv data.<\/p>\n
\n
max                    ;musterman              ;        98;max@musterman.com\r\nmathew                 ;doe                    ;      2115;mathew@doe.com\r\nmark                   ;doe                    ;      1515;mark@doe.com\r\nluke                   ;doe                    ;       115;luke@doe.com\r\njohn                   ;doe                    ;       978;john@mydomain.com\r\njohn                   ;lennon                 ;       115;johnlennon@beatles.com\r\npaul                   ;mccartney              ;      9115;paulmccartney@beatles.com\r\ngeorge                 ;harrison               ;       115;georgeharrison@beatles.com\r\nringo                  ;star                   ;       115;ringostar@beatles.com\r\n\r\n9 rows selected.\r\n<\/code><\/pre>\n<\/div>\n
Yes, now our data has now been turned into the ugliest comma separated data ever.<\/p>\n
Note: the semicolon is the csv file delimiter in excel in some countries.<\/p>\n
Extra Credit<\/strong><\/h3>\n
There is another way to run these scripts without adding a quit command.\u00a0 Simply echo the password into the sqlplus command when running a script.\u00a0 Sqlplus recognizes that it is in batch mode and quits when the commands are finished.<\/p>\n
echo secretpass | sqlplus myuser@cdatabase @commands.sql<\/p>\n
This might be marginally safer but not by much.\u00a0 If this were a script, then your password is still in the script to be found by someone.<\/p>\n
There is also a better way to create our csv file.\u00a0 It requires a bit more effort when creating our script.\u00a0 Simply concatenate each field that you wish to have into a single string.<\/p>\n
SELECT first || ‘,’ || last|| ‘,’ || iq|| ‘,’ || email FROM dbo.conair_emp;<\/p>\n
\n
max       ,musterman ,98,max@musterman.com\r\nmathew    ,doe       ,2115,mathew@doe.com\r\nmark      ,doe       ,1515,mark@doe.com\r\nluke      ,doe       ,115,luke@doe.com\r\njohn      ,doe       ,978,john@mydomain.com\r\njohn      ,lennon    ,115,johnlennon@beatles.com\r\npaul      ,mccartney ,9115,paulmccartney@beatles.com\r\ngeorge    ,harrison  ,115,georgeharrison@beatles.com\r\nringo     ,star      ,115,ringostar@beatles.com<\/code><\/pre>\n<\/div>\n
The 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.\u00a0 If the table had been created with varchar fields instead of char fields, then the data would not contain the trailing spaces.<\/p>\n
\n
max,musterman,98,max@musterman.com\r\nmathew,doe,2115,mathew@doe.com\r\nmark,doe,1515,mark@doe.com\r\nluke,doe,115,luke@doe.com\r\njohn,doe,978,john@mydomain.com\r\njohn,lennon,115,johnlennon@beatles.com\r\npaul,mccartney,9115,paulmccartney@beatles.com\r\ngeorge,harrison,115,georgeharrison@beatles.com\r\nringo,star,115,ringostar@beatles.com<\/code><\/pre>\n<\/div>\n
It 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.<\/p>\n
 <\/p>\n
Setup Scripts<\/strong><\/h3>\n
create.sql<\/p>\n
\n
drop table conair_emp;\r\ncommit;\r\n\/\r\ncreate table conair_emp\r\n(\r\nfirst char(10) not null,\r\nlast char(10) not null,\r\niq float not null,\r\nemail char(30) not null\r\n);\r\ncommit;\r\n\/\r\nprompt\r\nprompt test table conair_emp created\r\ndescribe conair_emp<\/code><\/pre>\n<\/div>\n
\u00a0<\/strong><\/em><\/p>\n
fill.sql<\/p>\n
\n
begin\r\ndelete from conair_emp;\r\ncommit;\r\ninsert into conair_emp (first,last,iq,email) values('max','musterman',98,'max@musterman.com');\r\n\r\ninsert into conair_emp (first,last,iq,email) values('mathew','doe',2115,'mathew@doe.com');\r\ninsert into conair_emp (first,last,iq,email) values('mark','doe',1515,'mark@doe.com');\r\ninsert into conair_emp (first,last,iq,email) values('luke','doe',115,'luke@doe.com');\r\ninsert into conair_emp (first,last,iq,email) values('john','doe',978,'john@mydomain.com');\r\n\r\ninsert into conair_emp (first,last,iq,email) values('john','lennon',115,'johnlennon@beatles.com');\r\ninsert into conair_emp (first,last,iq,email) values('paul','mccartney',9115,'paulmccartney@beatles.com');\r\ninsert into conair_emp (first,last,iq,email) values('george','harrison',115,'georgeharrison@beatles.com');\r\ninsert into conair_emp (first,last,iq,email) values('ringo','star',115,'ringostar@beatles.com');\r\ncommit;\r\nend;\r\n\/<\/code><\/pre>\n<\/div>\n
 <\/p>\n
report1.sql<\/p>\n
\n
clear screen\r\n\r\ncolumn first format A15\r\ncolumn last format A15\r\ncolumn iq format 9,999.99\r\ncolumn email format A32\r\n\r\nset term on\r\nspool report1.rpt\r\nSELECT * FROM dbo.conair_emp;\r\n\r\nspool off<\/code><\/pre>\n<\/div>\n
 <\/p>\n
report2.sql<\/p>\n
\n
clear screen\r\n\r\ncolumn first heading 'First name'  format A15\r\ncolumn last heading 'Last name'  format A15\r\ncolumn iq heading 'Hourly wage|in thousands'  format 9,999.99\r\ncolumn email heading 'Email Addr'  format A32\r\n\r\nset term on\r\nspool report2.rpt\r\nSELECT * FROM dbo.conair_emp;\r\n\r\nspool off<\/code><\/pre>\n<\/div>\n
 <\/p>\n
report3.sql<\/p>\n
\n
SET PAGESIZE 20\r\nSET HEADING on\r\n\r\nSET LINESIZE 80\r\nset trimspool on\r\nset termout off\r\n\r\nttitle center \"company wage report \" skip 2\r\nbtitle left \"-----------------------------------------------------------------------------\" skip 1 -\r\nleft \"confidential information - internal \" -\r\nright \"page \" format 999 sql.pno\r\n\r\ncolumn first heading 'First name'  format A15\r\ncolumn last  heading 'Last name'  format A15\r\ncolumn iq    heading 'Hourly wage|in thousands'  format 9,999.99\r\ncolumn email heading 'Email Addr'  format A32\r\n\r\nspool report3.rpt\r\nSELECT * FROM dbo.conair_emp;\r\nspool off\r\n<\/code><\/pre>\n<\/div>\n
 <\/p>\n
export1.sql<\/p>\n
\n
SET PAGESIZE 0\r\nSET LINESIZE 999\r\nset trimspool on\r\n\r\nspool export1.out\r\nSELECT * FROM dbo.conair_emp;\r\nspool off\r\n<\/code><\/pre>\n<\/div>\n
 <\/p>\n
export2.sql<\/p>\n
\n
SET PAGESIZE 0\r\nSET LINESIZE 999\r\nset trimspool on\r\nset colsep ';'\r\n\r\nspool export2.out\r\nSELECT * FROM dbo.conair_emp;\r\nspool off\r\n\r\nquit<\/code><\/pre>\n<\/div>\n
 <\/p>\n
export3.sql<\/p>\n
\n
SET PAGESIZE 0\r\nSET LINESIZE 999\r\nset trimspool on\r\nset colsep ';'\r\n\r\nspool export3.out\r\nSELECT first || ',' || last|| ',' || iq|| ',' || email FROM dbo.conair_emp;\r\nspool off\r\n\r\nquit<\/code><\/pre>\n<\/div>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
My first real exposure to a real database was Sybase.\u00a0 It actually is a fairly nice “little” database that you can come to grips with.\u00a0 I mean little when compared to behemoth that is Oracle.\u00a0 I don’t want to go … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2,20],"tags":[],"_links":{"self":[{"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/posts\/878"}],"collection":[{"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/comments?post=878"}],"version-history":[{"count":24,"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/posts\/878\/revisions"}],"predecessor-version":[{"id":906,"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/posts\/878\/revisions\/906"}],"wp:attachment":[{"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/media?parent=878"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/categories?post=878"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.paranoidprofessor.com\/index.php\/wp-json\/wp\/v2\/tags?post=878"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}