CODEWITHSUNDEEP
sqllinuxoracle-databaseunixclob
user9646260
user9646260

Reputation: 21

TO_CLOB cutting off report

I have a select statement with multiple concats using ||. The statement worked fine until the data became too big so I used TO_CLOB for the select to get around "String concatenation too long" error. The report is now generated but only partially with the data being cut out after 1 rows and the 3rd column of 2nd row.

Here's a short version of my code:

SET pagesize 0 SET echo off SET feedback off SET verify off PROMPT value1, value2, value3, difference SELECT TO_CLOB('field1' || ',' || num1 || ',' || num2 || ',' || diff || CHR(10) || 'field2' || ',' || num1 || ',' || num2 || ',' || diff || CHR(10) || ....... (there's about 80 such lines here) ....... ) from table

OUTPUT I'm getting as the report:

value1, value2, value3, difference field1, num1, num2, diff field2, num1, num2

and the rest is blank. The rest of about 80 rows are not being generated.

PS: I tested the query on sql developer and it works fine but does this when run in linux.

Please let me know if there's something I'm missing

Upvotes: 0

Views: 447

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6084

If your intention is to generate a CSV file from the command line, I would highly recommend trying out SQLcl. It works just like SQLPlus, but has much more functionality similar to what you would find in SQL Developer but through a command line interface. It is Java based so you will need at least Java 8.

By setting the sqlformat option, you can quickly generate a CSV file with ease. Since it is 99% compatible with SQLPlus commands, you can still spool and set whatever other format options you need. You can see from the example that it will even properly escape " as "" if there is a " in the columns being selected to generate a properly structured CSV file.

Edit: Andrew Sayer pointed out in the comments that this functionality is also available in the normal SQLPlus client since client version 12.2 using the set markup csv on command.

Here is a quick demonstration:

SQL> SELECT LEVEL, LEVEL + 1 AS next_level, 'Level: "' || LEVEL || '"' AS level_text
  2        FROM DUAL
  3  CONNECT BY LEVEL <= 10;

   LEVEL    NEXT_LEVEL     LEVEL_TEXT
________ _____________ ______________
       1             2 Level: "1"
       2             3 Level: "2"
       3             4 Level: "3"
       4             5 Level: "4"
       5             6 Level: "5"
       6             7 Level: "6"
       7             8 Level: "7"
       8             9 Level: "8"
       9            10 Level: "9"
      10            11 Level: "10"

10 rows selected.

SQL> set sqlformat csv

SQL> SELECT LEVEL, LEVEL + 1 AS next_level, 'Level: "' || LEVEL || '"' AS level_text
  2        FROM DUAL
  3  CONNECT BY LEVEL <= 10;

"LEVEL","NEXT_LEVEL","LEVEL_TEXT"
1,2,"Level: ""1"""
2,3,"Level: ""2"""
3,4,"Level: ""3"""
4,5,"Level: ""4"""
5,6,"Level: ""5"""
6,7,"Level: ""6"""
7,8,"Level: ""7"""
8,9,"Level: ""8"""
9,10,"Level: ""9"""
10,11,"Level: ""10"""

10 rows selected.

Upvotes: 0

MT0
MT0

Reputation: 167962

You need to get Oracle to concatenate the individual values as CLOB data types; wrap each field in TO_CLOB (otherwise, it will try to concatenate then as strings and then after concatenating will try to convert the single string to a CLOB, which would have already failed if they totalled more than 4000 characters):

SELECT TO_CLOB( field1 ) || ',' || TO_CLOB( field2 ) || CHR(10)
       || TO_CLOB( field3 ) || ',' || TO_CLOB( field4 ) AS value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name ( field1, field2, field3, field4 ) AS
SELECT LPAD( 'A', 4000, 'A' ),
       LPAD( 'B', 4000, 'B' ),
       LPAD( 'C', 4000, 'C' ),
       LPAD( 'D', 4000, 'D' )
FROM   DUAL;

Outputs:

| VALUE                                       |
| ------------------------------------------- |
| AAA ... x4000 ... AAA,BBB ... x4000 ... BBB |
| CCC ... x4000 ... CCC,DDD ... x4000 ... DDD |

It may be sufficient just to convert the first value in the list to a CLOB:

SELECT TO_CLOB( field1 ) || ',' || field2 || CHR(10)
       || field3 || ',' || field4 AS value
FROM   table_name;

db<>fiddle here

Upvotes: 0

Related Questions