0

I have a task to export the data of a table to flat files.I have written a shell script to do the same. The problem here is after the first column value, i.e., 310, the cursor won't move space by space, it takes a "tab" I think. It jumps certain position. I am not able to understand this. Could you please help me solve this by removing tab and taking that as spaces.

Is trimspool ON or Linesize causing the problem?

310  LIFRZONAAC 0000000 0000003
310  LIIPACCCLA 0000000 0000000
310  LIIPACREPL 0000000 0000000
310  LIIPANRDSI 0000000 0000000
310  LIIPAXNAD  0000000 0000000
310  LIIPBNRDSI 0000000 0000000
310  LIIPCAUDIP 0000000 0000000
310  LIIPCAUDMU 0000000 0000000

====================================================================================

My control file and shell script is as below.

Control file::

LOAD DATA CHARACTERSET WE8ISO8859P1 

APPEND
PRESERVE BLANKS
INTO TABLE "MNCABEA1"
APPEND
(
SERVICIO              CHAR(9),
FAMILIA_COMPONENTE    CHAR(4),
PARAMETRO             CHAR(7),
CO_CABECERA_SC        CHAR(8),
CO_CABECERA_CARACT    CHAR(7)
)

==================================================================================== Shell script::

cat<<ENDMNCABEA1 >MNCABEA1.sql
set head off
set feed off
set pagesize 0
set trimspool on
set linesize 500

SELECT SERVICIO||FAMILIA_COMPONENTE||PARAMETRO||DECODE(CO_CABECERA_SC,'        ',REPLACE(CO_CABECERA_SC,' ','0'),LPAD(LTRIM(CO_CABECERA_SC),8,'0'))||DECODE(CO_CABECERA_CARACT,'       ',REPLACE(CO_CABECERA_CARACT,' ','0'),LPAD(LTRIM(CO_CABECERA_CARACT),7,'0')) FROM MNCABEA1;

exit
ENDMNCABEA1

sqlplus -s $USUADM@$ORACLE_SID @MNCABEA1.sql > /var/opt/aat/shr/mn/par/ext/salida/MNCABEA1_TEST
Savitha
  • 405
  • 4
  • 15
  • 25

1 Answers1

0

You also need to set tab off; see the documentation. You might also want to set trimout off to avoid having trailing spaces on the line; as you aren't doing a spool, set trimspool probably isn't doing anything.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thank you very much Alex. This resolved the issue with the tab. :-) But I am stuck in another issue now, thats the linesize. :( If I set the trimout ON, which trims the trailing blanks, it is trimming the spaces, which are part of data in the last field. :( Is it possible to set the linesize to take the size of the data instead of me setting it to 200 or 500, which may manipulate the data? – Savitha Jul 17 '12 at 10:56
  • @Savitha - no, but if you're using fixed width columns in the control file, then I think you can just make `linesize` larger than you need, and remove the `trimout off`. Although if it's fixed column sizes, it should be a fixed line length that you can set exactly, maybe. Either way I don't think the control file will mind extra trailing spaces (but it's been a while). – Alex Poole Jul 17 '12 at 11:03