Using SQL*Plus on Oracle 6 to export data

[ericcarlson]

I have a client with an Oracle 6 database with SQL*Plus 3.0. They would like to export their data in a pipe (|) delimited format with column headers. Can anyone provide any tips on how to easily accomplish this? Pipe-delimited may not be so important as long as I can export in fixed-width format with the column headers.

The problem that I have right now is that when PAGESIZE is set to zero the column headers go away. Is there a way to turn on the column headers without breaking the results into pages?

The goal is to automate the export of 100+ tables.

I appreciate any tips you might have. Thanks.

-Eric

 

[SantaMufasa]

Eric,

Just so that we are speaking the same language, let me preface my reply with some nomenclature clarifications.

Some of Oracle's worst decisions have been made in the naming of products. And premier amongst their worst naming decisions was of their dump and restore utilities, which they stupidly named, "export" and "import" ("exp" and "imp").

Everyone else in the software industry use the terms "export" and "import" for code that allows easy data interchange with other software applications. For Oracle, however, their "exp" and "imp" utilities are really "dump" and "restore" utilities: The only software that can read and process the results of an Oracle "exp" ("export") is the same (or higher) version of Oracle "imp" ("import"). Conversely, the only file that an Oracle "imp" can read and process is a dump file that either the same version (or earlier version) of Oracle "exp" created.

Therefore, what you want is a solution that does not involve "export" as your subject heading implies. What you want is a query that reads a table and produces either comma-separated values (".csv") where the separator, instead of a comma, is a pipe symbol ("|"), or the result is fixed-length output.

I have a script (called "GENASCII.sql") that does what you want, except that it currently does not print out the column headings. I will modify the script for you and post it here.

Unfortunately, today produced that heaviest snowfall in our community in about the past 10 years. I've been snowblowing our "2-foot-deep gift" all day long, and I still have about 1/2 hour more to do before I'm done with that. And since it is currently 9:30 p.m., I must do it first so that my neighbors do not lynch me for the snowblower noise.

So, I'll get back to you the the results as soon as I can.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

Eric, set the pagesize to a large number instead of zero

e.g set pages 50000

set colsep '|'

will get you a pipe separated output


In order to understand recursion, you must first understand recursion.

 

[ericcarlson]

I've tried the "set colsep" idea except SQLPlus tells me:
unknown SET option "colsep"

I'm guessing that this version of SQLPlus does not support the "colsep" option?

-Eric

 

[taupirho]

Guess you're right in which case, and its a bit messier, you could do:

select field1 || '|' || field2 || '| etc ...
from table1


In order to understand recursion, you must first understand recursion.