Re: sql processing of local tables

Richard Booker <rbooker_at_21stcentury.net> wrote:

>Is it possible to do SELECT statements on the server and write result
>tables to my C: drive and then use SQL to further process them there?

No. Unless you have a local Oracle database and you use the distributed database feature. This allows you, via your local database on your PC, to select data from a remote database server, pull the result set down and storing it as a local table for further manipulation.

This basic concept extends to other software too. You can do the same using MS Access - via an Oracle ODBC driver you select data from the remote Oracle database and store the results in a local MS Access table.

Using MS Excel you can do the same thing via MS Query and that Oracle ODBC driver. This time around the data pulled down is stored in an Excel worksheet.

>I need to produce a flat file (ASCII fixed) for another agency from an
>Oracle 8i database residing on a web server in our DMZ.

I assume this needs to be in a CSV format? SQL*Plus is ideal for that. You deactivate/supress headings and pages and feedback, and spool the results to file. You use the concat symbol to concat columns and add field seperators.

Or you can use MS Access and Excel to do the same (pull down the data after which it is saved in CSV format).

>Because the format of the flat file is so different from our Oracle
>tables, I will need to do a quite a bit of massaging of the data.

You can not run SQL on a flat file. Okay, there is a SQL CSV/text ODBC driver, but it sucks big time ito flexibility and performance.

Think carefully about local data manipulation vs. using the database server for that.

File based processing is slow by nature - the old approach of input, processing and output is used. Read a line/record from the file. Process it. Write it out. Read the next line. Until EOF.

With a database, you have random and direct access to data. You have relationships between data. With a single SQL statement, you can do some pretty impressive and complex processing, that will do the job of 100's of lines of input-process-output code, pretty fast.

A database (properly designed and configured) can process and manipulate data _far_ beyond the limits of what you can do on a PC. Do as much as possible in Oracle. As little as possible on the PC.

That is what client-server is after all about.

--
Billy