I'm making a PS script that calls a sql*plus script and then send the file by mail.
Yet the issue is the result exported in the CSV is truncated at the last column because it's an xml and there is a line break between each markup
On Oracle SQL the results are in a single line.
In the csv with only these as options :
SET lines 2500;
SET pages 1;
The output of the spool file at the column 'info' looks like this :
'<?xml version='1.0' encoding='UTF-8'?>
<error_response>
<id>8cad0277-3a60-4cfd-a9c4-c272f69038ce</id>
<timestamp>2023-03-23T09:25:03.001Z</timestamp>
<error_lang>EN</error_lang>
<error_type>MULE:UNKNOWN</error_type>
<error_code>520</error_code>
<error_msg>
Now what i want is everything to be in a single line
I have tried severals things but it truncate it up to the 520 - '<?xml version='1.0' encoding='UTF-8'?>
Here are all the things i tried :
SET Wrap OFF;
SET Longchunksize 90000;
SET Pagesize 0;
SET lin[esize] 5000;
SET TrimSpool ON;
SET long 2000000;
SET lines 32737;
column info format a120
None worked still getting truncated to the 520 xml How can i get all the data contained in the info column ?
The line breaks are in your data, SQL*Plus isn't adding them. You need to replace line breaks with some special character that cannot appear in your data, then once you get the data to its final destination, replace them with line breaks to restore the formatted look.
I like to use non-printing control characters nobody ever sees:
Upon extract:
SELECT REPLACE(info,CHR(10),CHR(21)) info
FROM ...
At the final destination:
INSERT INTO target
SELECT REPLACE(info,CHR(21),CHR(10))
FROM ...
Also make sure you set linesize really big, which you have.