Shell Programming and Scripting

I have to create some fixed width files using UNIX. The data source is an Oracle SQL query. If I use straight SQL and spool to a file everything comes out as expected.

Example Desired result (. indicate blank space):
V278814831..................................1743049591.10N
V154369133..................................1743049841.10N

If I perform the file creation using UNIX:

Code:

sqlplus -s test/testpwd@testdb @spool_script2_test.sql >jss.dat

The file is created but every 8 spaces are automatically converted to tabs.
Example result (. indicate blank space, > indicate a tab equal to 8 spaces):
V278814831> > > > ..1743049591.10N
V154369133> > > > ..1743049841.10N

How can I prevent this automatic conversion? I have found way to revert the file to the desired state for example:

Code:

expand -8 jss.dat >jss_new.dat

but I don't want to do this. I want to stop the tab conversion in the first place. How can I do this?

I have talked with several co-workers and no one knows the answer, only workarounds.

Please help...

To get spaces in terminal output:

Code:

set tab off

The point is we dont know how the file is created, all we know is you use an SQL script we have no idea what it does without seeing it...

I'm with Franklin. It is a terminal driver issue.

spool in oracle writes a file using a separate fd, meaning it bypasses terminal I/O.
Redirection > takes terminal output from the terminal driver and sends it to a file

[Chapter 41] 41.4 How UNIX Handles TAB Characters

The only way around it is to change the terminal or use spool inside the SQL.
I like changing the SQL code, not the terminal settings, because terminal changes can mess up lots of other things.

@vbe, Here is the script, nothing fancy

Code:

set termout off;
set linesize 1938;
set pagesize 0;
set trims off;
set feedback off;

select
CASE WHEN col1 IS NULL THEN rpad(' ', 1) ELSE rpad(trim(col1), 1) END ||
CASE WHEN col2 IS NULL THEN rpad(' ', 9) ELSE rpad(trim(col2), 9) END ||
CASE WHEN col3 IS NULL THEN rpad(' ', 12) ELSE rpad(trim(col3), 12) END ||
CASE WHEN col4 IS NULL THEN rpad(' ', 9) ELSE rpad(trim(col4), 9) END ||
CASE WHEN col5 IS NULL THEN rpad(' ', 12) ELSE rpad(trim(col5), 12) END ||
CASE WHEN col6 IS NULL THEN rpad(' ', 33) ELSE rpad(trim(col6), 33) END x
from test_table
where col = '99';

 exit;

@ jim mcnamara
I'm relatively new to UNIX, can you spool to a UNIX directory from within a SQL script? If so, how? That's what I wanted to do in the first place but my co-workers said it's not possible.

Thanks

You can use the Oracle "SPOOL" command from SQL on a unix server.
It is no different from other platforms.
The "SPOOL" method is much better than using unix Shell commands to redirect output intended for a screen to a file.

Code:

SPOOL filename
...
... sql statements
...
SPOOL OFF

Thanks to all...

Tried the spool and it works. I guess I just learned a valuable lesson... My co-workers pretendto know UNIX. Next time I won't take what they say as gospel and try it for myself, which is what i usually do. I don't know what came over me.