sqlplus remove \r \n \t from spool

Question

Hi all,

Is there any sql*plus command to remove \r \n and\t from the result set that's going out to the spool file? That is, "trim" every record?

We've used set trim on in the past, but it doesn't seem to bue what we need right now. I'm trying to avoid calling oracle's translate, chr functions in the sql query.

For example,

set termout off
set spool somefile.dat
set lin  600

select data from mytable;

set spool off;
exit;

My query returns this

|DATA|
|\n \t\t\t\t\t thisistheactualdata \t\t\t\t\t\t\n|

And I'd like to keep this in my spooled file

thisistheactualdata

update

Well, we ended up doing something like this.

set tab off;
spool /home/oracle/out.dat

set linesize 20
set termout off
set trim on
select regexp_replace(l,'(\t|\n)','') from test;

spool off;
exit;

But got some bad news: We need to run this in oracle 8, and regexp_replace doesn't seem to be available. :(

Thanks in advance.

Answer 1

Without trying it, does

SET TAB OFF

have the desired effect?

Answer 2

I don't think you're going to be able to do this with a SQL*Plus directive. SQL*Plus is a pretty thin client and isn't designed to touch the data from the result set itself.

If you don't want to use the built-in SQL functions to modify the output then I think you're stuck with post-processing a spooled file.

EDIT by DCookie: Since I missed the OP's original request for a non-TRANSLATE/CHR based solution (I focused on the OP's lament that they were stuck with 8i), in fairness to dpbradley I'm going to withdraw my answer and include it as part of this one, since it was clearly considered. Here's what I wrote:

You might try the TRANSLATE function for your Oracle 8 situation:

SELECT TRANSLATE(L,'A'||CHR(10)||CHR(9)||CHR(13),'A') FROM test;