Concat

[Tinti]

Hi

I've to extract the values of a wide table (about 120 attributes) to a file. On the file the values has to be separated by semicolon and leading and trailing blanks has to be eliminated.

I've tried to do it with following command file:

SPOOL L:\FSD_7061\OE91100\FSD7061_DM_RPBS_OE91100_DYYYYMM.txt

PROMPT DATUM;CIF;CUST_NAME;CUST_TYPE;CUST_TYPE_TXT;NUMBER_CLIENT_FLAG;CUST_SEG;CUST_SEG_CSEG;CUST_SEG_TXT;
	
SELECT TO_CHAR (DATUM,'DD.MM.YYYY')
 || ';' || CIF
 || ';' || CUST_NAME
 || ';' || CUST_TYPE
 || ';' || CUST_TYPE_TXT
 || ';' || NUMBER_CLIENT_FLAG
 || ';' || CUST_SEG
 || ';' || CUST_SEG_CSEG
 || ';' || CUST_SEG_TXT
FROM TABLE TEST
WHERE REGION = '91100'
;

SPOOL OFF
/

But it doesn't work. The file looks like following example:

DATUM;CIF;CUST_NAME;CUST_TYPE;CUST_TYPE_TXT;NUMBER_CLIENT_FLAG;CUST_SEG;CUST_SEG_CSEG;CUST_SEG_TXT;
30.11.2006;************;************;142;Employee wife;N;016;PER;Staff member;18
30.11.2006;************;************;161;Retired person male;N;016;PER;Staff mem
30.11.2006;************;************;141;Employee husband;N;016;PER;Staff member
30.11.2006;************;************;141;Employee husband;N;016;PER;Staff member
30.11.2006;************;************;162;Retired person female;N;016;PER;Staff m
30.11.2006;************;************;162;Retired person female;N;016;PER;Staff m
30.11.2006;************;************;133;Employee collective (Individual signatu
30.11.2006;************;************;131;Employee male;N;016;PER;Staff member;03
30.11.2006;************;************;142;Employee wife;N;016;PER;Staff member;30
30.11.2006;************;************;133;Employee collective (Individual signatu
30.11.2006;************;************;152;Child female;N;016;PER;Staff member;07.
30.11.2006;************;************;151;Child male;N;016;PER;Staff member;07.06

Thanks a lot for every support!

Best Regards

 

[Bo Pedersen]

I guess this is a problem with the line size. Try to increase it. F.e.x. set linesize 999.

If you can use , as the separator, you can simply use the Export Results -> CVS File feature in the SQL Window.

Bo Pedersen

 

[Joachim Rupik]

You can use a SET COL[WIDTH] [Width] command.
This command determines the maximum column width in a result set. If Width = 0, the width is unlimited. The default is 80.

(PL/SQL Developer 7.0 User

 

[Tinti]

Thanks for the fast reply. I've tried both versions. But the output is still the same. It seems that my "set statements" is ignored.

SET LINESIZE 999
SET PAGESIZE 0
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET ECHO OFF

SPOOL L:\FSD_7061\OE91100\FSD7061_DM_RPBS_OE91100_DYYYYMM.txt

PROMPT DATUM;CIF;CUST_NAME;CUST_TYPE;CUST_TYPE_TXT;NUMBER_CLIENT_FLAG;CUST_SEG;CUST_SEG_CSEG;CUST_SEG_TXT;CUST_SINCE;CUST_UNTIL;ON_OFFSHORE;DOM;DOM_TXT;NAT;NAT_TXT;CENTRAL_PROCESSING_CODE;IC;FP;CUST_OFFICE;CREDIT_OFFICE;VVF;VVA;DATE_OF_BIRTH;DATE_OF_FOUNDATION;RET;CIF_ART;CIF_STATUS;CUSTODY_FLAG;RM_PID;RM_NAME;RM_VNAME;RM_INSTR;DEPARTEMENT;DEPARTEMENT_NAME;REGION;REGION_NAME;SEKTOR;SEKTOR_NAME;TEAM;TEAM_NAME;JOB_CODE;RM_TEL_NR;E5000;E5001;E5010;E5200;E5201;E5202;E5011;E5205;E5206;E5207;E5241;E5002;E5016;E5210;E5211;E5017;E5213;E5214;E5420;E5606;E5608;E5852;E5609;E5215;E5217;E5019;E5003;E5020;E5004;E6714;REV_R0001;REV_R0002;REV_R0003;REV_R0004;REV_R0005;B5220;B5245;B5221;B5222;B5226;B5310;O5311;B7220;U5005;U6054;NNM;MANS;BEW_R0001;BEW_R0002;BEW_R0003;BEW_R0004;BEW_R0005;B5000;B5001;B5010;B5011;B5045;B5046;B5047;B5040;B5041;B5042;B5070;B5100;B5102;B5185;B5111;B5103;B5012;B5003;B5021;B5020;B5019;B5800;B7001;B5211;B5294;B5259;B5290;B6873;B5243;BEST_R0001;BEST_R0002;BEST_R0003;BEST_R0004;BEST_R0005;B5237;B5239;B5316;B5233;B5324;RATIO_R0001;RATIO_R0002;RATIO_R0003
	
SELECT TO_CHAR (DATUM,'DD.MM.YYYY')
 || ';' || CIF
 || ';' || CUST_NAME
 || ';' || CUST_TYPE
 || ';' || CUST_TYPE_TXT
 || ';' || NUMBER_CLIENT_FLAG
 || ';' || CUST_SEG
 || ';' || CUST_SEG_CSEG

The code above is packed in a pdc-file which I run in a command window.

Could it be that I've to change something in my preferences?

 

[Joachim Rupik]

Please use:

set col 0

instead:

set linesize 999

as I wrote in previous message.