Hi

Every time I am facing some issues, please help on this. Test cases are given below

CREATE TABLE AA
(
ID NUMBER NOT NULL,
NAME VARCHAR2(80 BYTE) NOT NULL,
BANK_ADDRESS VARCHAR2(1000 BYTE)
);

INSERT INTO AA VALUES
(1,’MGR’, ‘ICICI BANK
MOUNT ROAD
CHENNAI’);

INSERT INTO AA VALUES
(2,’SIVAJI’, ‘SBI
ANNA ROAD
CHENNAI’);

INSERT INTO AA VALUES
(3,’KABALI’, ‘RBS OLYMBIA GUINDY CHENNAI’);

--data stored in the database same as like as it is.

SELECT * FROM AA;
OUTPUT displayed in UNIX server USING SQLPLUS / login
===================================
ID NAME BANK_ADDRESS
----------------------------------
1 MGR SBI
ANNA ROAD
CHENNAI
2 SIVAJI ICICI BANK
MOUNT ROAD
CHENNAI
3 KABALI RBS OLYMBIA GUINDY CHENNAI

I have created the spool file query

aa.sql
SET TRIMSPOOL ON
SET TRIMOUT ON
SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 10000
SET PAGESIZE 0
SET TERM OFF
SET ECHO OFF
SET LONG 10000

SPOOL local_path/bank_aa.txt
SELECT 'HDR.AA.' || to_char(sysdate,'YYYYMMDDHH24MISS') from dual;
SELECT distinct
ID ||'|'||
NAME ||'|'||
BANK_ADDRESS
FROM AA
UNION ALL
select 'TRL.AA.' || count(*) from AA;
SPOOL OFF;
exit;

I had received the bank_aa.txt file like this.

HDR.AA.20160726130606
1 | MGR | SBI
ANNA ROAD
CHENNAI
2 | SIVAJI | ICICI BANK
MOUNT ROAD
CHENNAI
3 | KABALI | RBS OLYMBIA GUINDY CHENNAI
TRL.AA.3

Total data is 3 but extract files is having 7 data line. With this extract file I cant load the data into target database.

I need the output file like below
HDR.AA.20160726130606
1 | MGR | SBI ANNA ROAD CHENNAI
2 | SIVAJI | ICICI BANK MOUNT ROAD CHENNAI
3 | KABALI | RBS OLYMBIA GUINDY CHENNAI
TRL.AA.3

Can you pls help on this

Thanks
Partheeban.J

drop table t purge;
create table aa (
    id number not null, name varchar2 ( 80 byte ) not null, 
    bank_address varchar2 ( 1000 byte )
  ) ;
insert into aa values
  ( 1,'MGR', 'ICICI BANK
MOUNT ROAD
CHENNAI'
  ) ;
insert into aa values
  ( 2,'SIVAJI', 'SBI
ANNA ROAD
CHENNAI'
  ) ;
insert into aa values
  ( 3,'KABALI', 'RBS OLYMBIA GUINDY CHENNAI'
  ) ;

SELECT 'HDR.AA.' || to_char(sysdate,'YYYYMMDDHH24MISS') from dual;

SELECT distinct ID ||'|'||NAME ||'|'||replace(replace(BANK_ADDRESS, chr(10), ' '), chr(13)) 
FROM AA;

ID||'|'||NAME||'|'||REPLACE(REPLACE(BANK_ADDRESS,CHR(10),''),CHR(13))  
1|MGR|ICICI BANK MOUNT ROAD CHENNAI                                    
3|KABALI|RBS OLYMBIA GUINDY CHENNAI                                    
2|SIVAJI|SBI ANNA ROAD CHENNAI