3

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.

Tom
  • 43,810
  • 29
  • 138
  • 169
  • Do you mean removing these characters when they are part of the data (as opposed to streaming the SQL*Plus output without the characters that it supplies)? – dpbradley Jun 09 '10 at 14:15
  • Yes, remove them from the output columns. – Tom Jun 09 '10 at 14:16
  • If you've got the OWA_PATTERN package installed, could you write your own wrapper function to mimic regexp_replace()? Possibly a generic strip/trim function to simplify the queries - so if you find another character you want to remove you can change the function rather than all the queries. – Alex Poole Jun 09 '10 at 14:56
  • Would TRANSLATE help your Oracle 8 problem? – DCookie Jun 09 '10 at 15:04

2 Answers2

4

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;
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
dpbradley
  • 11,645
  • 31
  • 34
0

Without trying it, does

SET TAB OFF

have the desired effect?

Mark Baker
  • 209,507
  • 32
  • 346
  • 385