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