Hi All,
I have created a script which generates FIXED-WIDTH file by executing Oracle query.
SELECT RPAD(NVL(col1,CHR(9)),20)||NVL(col2,CHR(9))||NVL(col3,CHR(9) FROM XYZ
It generates the data file with proper alignment. But if same file i transfer to windows server or Mainframe server, it comes with improper alignment. I have tried to change from CHR(9) to ' '. But still problem exist. Can someone help me to solve it.
Regards,
ACE
try to put a delimiter , say a pipe in between the columns and try to replace it with a space once the file is transfered to the wondows machine.
Well I don't see a properly aligned output at all -
SQL>
SQL>
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(10)
COL2 VARCHAR2(10)
COL3 VARCHAR2(10)
SQL>
SQL> select rownum, t.* from t;
ROWNUM COL1 COL2 COL3
---------- ---------- ---------- ----------
1 a b c
2 a b
3 a c
4 b c
5 a
6 b
7 c
8
8 rows selected.
SQL>
SQL> SELECT RPAD(NVL(col1,CHR(9)),20)||NVL(col2,CHR(9))||NVL(col3,CHR(9)) x FROM t;
X
--------------------------------------------------------------------------------
a bc
a b
a c
bc
a
b
c
8 rows selected.
SQL>
SQL>
Maybe you wanted to do something like this ?
SQL>
SQL> var filler varchar2(1)
SQL>
SQL> exec :filler := '~';
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT RPAD(NVL(col1,:filler),20)||RPAD(NVL(col2,:filler),20)||RPAD(NVL(col3,:filler),20) x FROM t;
X
--------------------------------------------------------------------------------
a b c
a b ~
a ~ c
~ b c
a ~ ~
~ b ~
~ ~ c
~ ~ ~
8 rows selected.
SQL>
SQL> exec :filler := ' ';
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT RPAD(NVL(col1,:filler),20)||RPAD(NVL(col2,:filler),20)||RPAD(NVL(col3,:filler),20) x FROM t;
X
--------------------------------------------------------------------------------
a b c
a b
a c
b c
a
b
c
8 rows selected.
SQL>
SQL> exec :filler := '#';
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT RPAD(NVL(col1,:filler),20)||RPAD(NVL(col2,:filler),20)||RPAD(NVL(col3,:filler),20) x FROM t;
X
--------------------------------------------------------------------------------
a b c
a b #
a # c
# b c
a # #
# b #
# # c
# # #
8 rows selected.
SQL>
tyler_durden
NB: this textbox's formatting options mess up the 2nd case when the bind variable is a blank space; it should work fine on command line sqlplus. Of course, the font in your viewer application (text editor or sqlplus terminal window etc.) must be fixed width.
Sorry, initially i forgot to give data length of each column
COL1 VARCHAR2(20)
COL2 VARCHAR2(1)
COL3 VARCHAR2(1)
That doesn't matter, the idea is still the same. Adapt the query to your table structure.
tyler_durden
FTP transfers aside, Oracle's default colsep character is usually a space, but could vary on your system if the DBA modified it. Meanwhile, tab is sometimes replaced by 3 spaces in some terminals, which only compounds the problem. Is there any reason you'd want to use nvl() to impose a tab character on a blank column? As tyler suggested, use a character to represent a NULL value, but I would recommend not using a typical delimiter, such as tab, space or pipe...