Export into an ASCII file 1

[Lourry]

Hello all,

I'm new to Oracle and need to output all the records from an Oracle table to an ASCII file. The problem is I need to format the ASCII file in a certain way...

Each record has to start with the word BAND in uppercase letters then the first name field, last name field, address field, city field and so on.

Example of 2 records in the ASCII file:
BAND "Mary" "Smith" "123 ABC Street" "Acity"
BAND "John" "Smith" "234 VBD Street" "Bcity"

How can I achieve this? should this be in a .sql file? Please help!

Thanks in advance!
-Lory

 

[jaggiebunnet]

There are 2 or 3 ways to do this. But if it is just one table simplest would be to have a script (a .sql file) that outputs a spool file, such as

SPOOL myfile.txt

select 'BAND "' || name || '" "' || street || '" "' || town
from   mytable;

SPOOL OFF

and run from sqlplus.

 

[SantaMufasa]

Lory,

Following is a continuation of Jaggiebunnet's correct solution. This is a slight variation on my GenASCII.sql script that I've posted on other threads when the need was for Comma-Separated-Value (.csv) output. Here I've tailored the script to generate the output you wish following your supplying the name of the (input) table and the (output) flat file:

REM ***********************************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM ***********************************************************************************
set echo off
accept x prompt "Enter the table to ASCII: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt 
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select 'BAND '||
select decode (rownum,1,'''"''||','||'' "''||') || column_name || '|| ''"''' 
  from user_tab_columns
 where table_name = upper('&x');
prompt from &x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Here is a sample invocation (using one of my tables, of course):

SQL> @GenASCIILourry
Enter the table to ASCII: s_dept
Enter the flat file to write: Lourry.txt

Following output is generated script that writes text output from table "s_dept"

set echo off
set feedback off
set heading off
set pagesize 0
spool Lourry.txt
Select 'BAND '||
'"'||ID|| '"'
||' "'||NAME|| '"'
||' "'||REGION_ID|| '"'
from s_dept
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "Lourry.txt"

BAND "10" "Finance" "1"
BAND "31" "Sales" "1"
BAND "32" "Sales" "2"
BAND "33" "Sales" "3"
BAND "34" "Sales" "4"
BAND "35" "Sales" "5"
BAND "41" "Operations" "1"
BAND "42" "Operations" "2"
BAND "43" "Operations" "3"
BAND "44" "Operations" "4"
BAND "45" "Operations" "5"
BAND "50" "Administration" "1"

Output file = "Lourry.txt"

Let us know if this meets your requirements.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:12 (05Jul04) UTC (aka "GMT" and "Zulu"), 09:12 (05Jul04) Mountain Time)

 

[jaggiebunnet]

Mr Fancy Pants

 

[SantaMufasa]

Jaggiebunnet,

[Laughing Out Loud So Hard It Scared The Cat] If you could see me right now, you would know there's nothing "fancy" about my pants. [still laughing]

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:29 (05Jul04) UTC (aka "GMT" and "Zulu"), 09:29 (05Jul04) Mountain Time)

 

[Lourry]

Thanks JaggieBunnet and SantaMufasa for your quick replies!

Yes, this is exactly what I need. Thank you so much.
I'll give this a try and if I have further questions I'll let you know.

Thanks again

 

[Lourry]

The script works...however, there is still a couple problems.

Since my table has about 200 columns, my select statement is very very long. I cannot do select * because I need the fields in a specific order. So I've got this SELECT statement that looks like:

SELECT 'BAND "'||field1||'" "'||field2||'" "'||field3||' (goes on for the rest of the fields)' from mytable;

When I run it, it gives me an error saying the input line is too long (>2499 characters). So, what I did was split the SELECT statement approx in the middle with just a carriage return and ran it. It spooled out the ascii file but where I had split the SELECT statement, it will become a new line in the ascii file. I need the ascii file to have 1 record per line, it cannot be over several lines. The next thing I tried was to set the linesize but some of the records are not as long as the other ones, it will have this huge blank space after it.

Is there a way to do the SELECT statement so that it will output each record per line? Or have it continuous over several lines? Each record must be 1 continuous line.

Also, in the ascii file, after each record, there is blank line after it. Is there a way to get rid of it? Example: After the first record, go to the next line immediately after and start the next record then the line immediately after start the next record.....

Please help! Thanks in advance!
-Lory

 

[SantaMufasa]

Lory,

First of all, I the error that you are receiving, ["input line is too long (>2499 characters)"] is because your SQL command line is too long, not because your output is too long. You should never need to code wider than a screen width withoug a carriage return in SQL. If you use my script that I posted, above, you should eliminate the error you are receiving.

To avoid the "extra blank line", you should set the following SQL*Plus settings:

set pagesize 0
set linesize <some number greater than your widest output line>
set trimspool on

These settings should take care of business for you and not generate any blank lines. If you still encounter blank lines, then post your script here and we'll get it all resolved to your satisfaction.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:21 (08Jul04) UTC (aka "GMT" and "Zulu"), 14:21 (08Jul04) Mountain Time)

 

[Lourry]

THanks SantaMufasa!!

It works perfectly now. Thanks again : )