0

I have a varchar2 column that contains line breaks and commas. However, when generating a CSV file using PL/SQL, the presence of commas causes the data to jump to the next column, and line breaks cause the data to jump to the next row. Unfortunately, I haven't been able to find a solution to maintain the desired content in the same column. Could someone please assist me in resolving this issue? Thank you in advance.

example format in my varchar2: { "AAA": "BBB", "CCC": "DDD" }

REPLACE(',', ',') did not work.

MT0
  • 143,790
  • 11
  • 59
  • 117
EasonHuang
  • 1
  • One of the answers does: the entire column text in double quotes. In the text the double quote is self-escaped as two double quotes. – Joop Eggen Jun 06 '23 at 00:29
  • 1
    Your example is json. That is a very portable format. Why would you export json as a csv ? You could just export it as json... – Koen Lostrie Jun 06 '23 at 07:36

1 Answers1

1

SQL*Plus is primarily a command tool and a rudimentary report tool. It wasn't designed for robust programmatic data feeds, but it can be adapted to produce them with a bit of effort.

In your case, since line breaks also mess up line-based Unix tools that you might be piping file data through for cleansing, it's useful to get them out of your data at the source and keep them out until it reaches it's final destination.

To do this, replace any special characters that mess up SQL*Plus output with characters it won't do anything with, but which cannot occur in your data. Device control characters are used by virtually nothing so are good candidates:

SELECT TRANSLATE(mytext,CHR(10)||',',CHR(19)||CHR(20))
  FROM table

Then on the consuming end, wherever that is, reverse the translation. You can use tr or sed in Unix, or if it's another Oracle database:

TRANSLATE(mytext,CHR(19)||CHR(20),CHR(10)||',')

You can use nested REPLACEs as well, and any other character you are sure won't occur in your data.

Of course, also make sure you set linesize, long, longchunksize, pagesize, header, etc.. and the -S flag to appropriate values to get clean output.

Paul W
  • 5,507
  • 2
  • 2
  • 13