Skip to Main Content
  • Questions
  • How to extract data into a flat file with linesize more than 32767

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vishnu.

Asked: January 12, 2017 - 6:00 pm UTC

Last updated: January 13, 2017 - 6:47 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hello,

I am trying to use SQL*plus to extract the result of a SQL into a flat file. The flat file will be used in an ETL tool for further processing.

I am running into the below issues:

1) Linesize limitation
My extracted record length is more than 32767, so I tried using SET WRAP ON which unnecessarily introduces a new line character(\n) when it encounters the scenario of records more than 32767. This \n will hamper my downstream data processing.

Example:
Source data : "aaaaaaaaaaaaa|1111111111111111|bbbbbbbbbbbbbbbbbbbbb|cccccccccccccccccccccccc"
Assuming linesize set to 20 and WRAP ON

Expected result :
aaaaaaaaaaaaa|11111
11111111111|bbbbbbb
bbbbbbbbbbbbbbb|ccc
ccccccccccccccccccc
\n

Actual result:
aaaaaaaaaaaaa|11111\n
11111111111|bbbbbbb\n
bbbbbbbbbbbbbbb|ccc\n
ccccccccccccccccccc\n
\n


So for this case, I would like to know if there is way for either increasing/removing the linesize limitation or a way to prevent the unnecessary new line characters being introduced when WRAP is enforced.

2) COLSEP -
Since my downstream application in ETL is expecting a ( Ctrl + A character - 0x01(hex equivalent) & \u001(unicode equivalent) as a delimiter, I would like to know if there is a way to mention this as a COLSEP character.

Thanks in advance for your help.!

and Connor said...

Sorry, as far as I know, 32767 is as wide as we go (sqlplus or utl_File). You'd need to write your own routine to go wider than that.

Maybe something like this:

SQL> col x new_value y
SQL> select chr(1) x from dual;

X
-
☺

SQL> set colsep &&y
SQL> select * from tab;

TNAME                         ☺TABTYPE☺ CLUSTERID
------------------------------☺-------☺----------
A                             ☺TABLE  ☺
AK_TEMP                       ☺TABLE  ☺
ALL_DATA                      ☺TABLE  ☺
AQ$DEMO_QT                    ☺VIEW   ☺
AQ$_DEMO_QT_F                 ☺VIEW   ☺



Is this answer out of date? If it is, please let us know via a Comment