On Fri, Oct 27, 2000 at 02:28:59PM +1000, Doug Stalker wrote:
> I need to dump the output of an Oracle SQl query to a file, but
> unfortunately it replaces all NULL field with a carrage return.
You might be better off doing the SQL query in a Perl program
instead of doing it in sqlplus and dumping the output out.
The DBI module from CPAN is your friend. You basically set up your
SQL query, execute it and then use a nice while loop to do things with
each returned row.
The following template should be useful, I don't remember the connect
parameters offhand.
#!/usr/bin/perl
use DBI;
$dbh = DBI->connect("oracle", username, password, \%attr); # see perldoc
$op = "select field1, field2, field3, .... fieldn from table"; # note no ;
$sth = $dbh->prepare($op);
$sth->execute();
while ( ($field1, $field2, $field3, ... , $fieldn) = $sth->fetchrow() ) {
# each $field is undef if it is NULL
... do something with the data here ...
}
You will find time spent learning and developing this quite
useful and educational for later SQL work. It sure beats counting
spaces and double guessing which fields in the output are really null
and which might be blank strings and things. DBI can hook into other
databases as well and is really cool.
> But I don't know how to implement this. Can it be done with a sed
> script or 3, or will it require something more complicated?
Perl and DBI give you better power and understanding for not much more
complication.
Failing that I seem to remember there is a function you can use in
Oracle SQL to print null values out as some other string, so maybe you
can adjust your query so that the NULLS announce themselves
properly. I am *no* expert when it comes to Oracle SQL.
Whatever you do you shouldn't be reduced into counting spaces.
Finally replace your Oracle database with a free database like Postgres
or MySQL depending on your needs :)
Good luck,
Stuart.
--
SLUG - Sydney Linux User Group Mailing List - http://slug.org.au/
More Info: http://slug.org.au/lists/listinfo/slug
