Shell Programming and Scripting

Hi,

I am using sqlplus (called inside my ksh) to create a file which is mailed to the users as an xls.
There is one DESCRIPTION column which is currently coming as wrapped, but, the users want a new line for each of the fields present in the desricription column.
Below is an example -

DESCRIPTION
-------------
Name:aaaaaa Base:123mn Loc:000454
Sequence:00000987 Shadow:Y

This is wrapped.
How the User wants this column is as follows -


DESCRIPTION
-------------
Name:aaaaaa
Base:123mn
Loc:000454
Sequence:00000987
Shadow:Y


This should be wrapped too but each field with its value should appear in a new line (like we give ALT+Enter in a xls to have a new line inside a cell).

Is this possible to do this in sqlplus?

I had tried giving the following Column command, but it is not working -
COLUMN DESCRIPTION FORMAT WORD_WRAP


Thanks,
Sree

Sree,

Perhaps, you should be posting this to an sql forum.

Agreed...Still ....if any leads could come through......

I bet you can do natively in sqlplus, but ..... you can pipe the output of sqlplus into this 'nawk':

Code:

sqplus ....... | nawk 'RS=FS'

Here's one sqlplus way to do it:

Code:

  1  SELECT text
  2        ,REPLACE (text
  3                 ,' '
  4                 ,CHR (10) ) text
  5  FROM   (SELECT 'desc1:value1 desc2:value2 desc3:value3' text
  6*         FROM   DUAL)
SQL> /

TEXT                                   TEXT
-------------------------------------- --------------------------------------
desc1:value1 desc2:value2 desc3:value3 desc1:value1
                                       desc2:value2
                                       desc3:value3

But there are other columns as well that are selected in my sql -

My query goes like this -

select npp as NPP,error_message as ERROR,error_type as TYPE,data_selection as DA
TA_SELECTION,description as DESCRIPTION,item_cd as ITM_CD from export_test;


Could you please throw more light on nawk. Also, what does RS=FS signify?


Thanks.

one more thing: sqlplus provides formatting capabilities for specific columns. If your values are going to be similar in size, the formatting command may do the trick.

Code:

  1  SELECT 'desc1:value1 desc2:value2 desc3:value3' wrapped_text
  2  FROM DUAL
SQL> column wrapped_text format a20 word_wrapped
SQL> /

WRAPPED_TEXT         
-------------------- 
desc1:value1         
desc2:value2         
desc3:value3