How to extract data without any formatting to an ascii file???

[bi]

There has to be an easy way to use sqlplus to select all data from a table and send it to a simple ascii text file with no formatting, no commas, nothing but the data, one record per line. I just can't figure it out.

I'm getting records whose row sizes are twice the length they should be because of all the column formatting sqlplus puts in.

I have set heading off, feed off, term off, space off, and trimspool on.

I have checked Oracle's sqlplus doc, I've looked at all kinds of web topics and all I see is information about exporting a table (which I can't use because I need the ascii text file) and creating forms, which I don't want.

Can someone please help?

Thanks.

 

[BJCooperIT]

What column formatting are you seeing?
Perhaps your settings have an error you are not seeing due to the [tt]set term off[/tt]. Try turning it on and test it.

Did you mean you used [tt]set space 0[/tt]?

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}

 

[THoey]

Easy way? Probably not. The only way I can think of is building your own SQL statement to do it. To create a single line per record where the data runs together:

SELECT MyField1 || MyField2 || MyField3 DataLine
FROM MyTable;

To create with a single space:

SELECT MyField1 || ' ' || MyField2 || ' ' || MyField3 DataLine
FROM MyTable;

BTW, DataLine is just an alias I used. You don't need it or could call it anything.








Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Beantree]

you can link the table via ODBC to ACCESS and use it's export feature using fixed width style to accomplish this.

 

[bi]

Thanks for all your replies. I'm getting closer. THoey, I used your first suggestion, but what I need are spaces if the field doesn't have the total number of characters that are allowed in the field. (For example, if the field is 8 characters long and the record only has 5 characters for that field, I need 3 spaces at the end of the data in that field.) How can I accomplish that?

BJ, yes, I meant space 0.

And Beantree, I don't have Access.

 

[Beantree]

Access will provide the full width of the field (at least if the Oracle datatypes map nicely to the Access types).

Too bad you don't have. Sometimes it can make for simple solutions.

 

[BJCooperIT]

OK, let's see if this helps:
[/code]
set heading off, feed off, term off, echo on
set trim on, trimspool on
set space 0
create table test_it
( first_name varchar2(20),
middle_initial char(1),
last_name varchar2(25)
);
insert into test_it values('SAMUEL','','DIXON');
insert into test_it values('CHRISTINE','V','GILLIAM');
insert into test_it values('WAYNE','C','KYLE');
insert into test_it values('BARBARA','J','JUGLER');
insert into test_it values('ROBERT','','WETHERHOLD');
insert into test_it values('SUE','A','FELTMAN');

select first_name,
nvl2(middle_initial,middle_initial||'.',''),
last_name
from test_it
order by last_name, first_name, middle_initial;
drop table test_it;
[/code]

OUTPUT
[tt]
SAMUEL DIXON
SUE A.FELTMAN
CHRISTINE V.GILLIAM
BARBARA J.JUGLER
WAYNE C.KYLE
ROBERT WETHERHOLD
[/tt]

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}

 

[R1972]

Hi,

How abt the following statement

select lpad(nvl(column,' '),fixed_size)) from <table>

select lpad(nvl('xxx', ' '),5) from dual

returns
-------
'xxx ' - 5 chars.
if null - ' ' - five spaces.


Regards,
Raj