How to acheive ALT+Enter of xls in Unix ksh/sqlplus

Shell Programming and Scripting
1

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

2

Sree,

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

3

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

4

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

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

Here's one sqlplus way to do it:

  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
6

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.

7

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.

  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
8

Tmarikle,

Also, please note that desc1:value1 desc2:value2 desc3:value3 are not fixed - they are as coming from the table...so they keep changing for every cell.
There might be a case where there are 10 different descriptions with 10 different sized values for a single record. So for this record alone there will be 10 new lines within a single cell called Description where each of the Description with values come up with.

9

what this does is it transliterates/replaces any single space with the newLine.
so that won't work if your 'fields' have embedded space(s) - but it they don't [i.e. all your fields are of the form: fieldName:fieldValue] the awk should be ok [or you can do the same with 'tr'.

10

But, the values in the description column might have embedded spaces in themselves.

11

yep, that will be the problem.

Once again I'm pretty sure there's a way to do natively with the sqlplus, but...

If changing sqlplus output and post-processing the option is an option......

Say if you change the output sqlplus to the form:

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

... one could easily write a post-processor filter put the fieldName:fieldValue pairs onto the separate lines.

12

And another way to accomplish the same thing allowing spaces if you wish:

#! /bin/ksh
# Newlines as delimiters only
IFS=$(echo '\012\001') 
set -A RESULTS_ARRAY $(
   print "
      connect user/pass@db

      set pages 0 lines 100 trimspool on verify off echo off feedback off

      select   table_name || ':' || column_name
      from     all_tab_columns
      where    owner = 'SYS'
      and      table_name like '%SOURCE'
      order by table_name, column_name;
   " | sqlplus -s /nolog
)

for i in ${RESULTS_ARRAY[@]}
do
    printf "%-30s%-30s%s\n" "\$i=$i" "Left hand side=${i%:*}" "Right hand side=${i#*:}"
done

Results

$i=ALL_SOURCE:LINE            Left hand side=ALL_SOURCE     Right hand side=LINE
$i=ALL_SOURCE:NAME            Left hand side=ALL_SOURCE     Right hand side=NAME
$i=ALL_SOURCE:OWNER           Left hand side=ALL_SOURCE     Right hand side=OWNER
$i=ALL_SOURCE:TEXT            Left hand side=ALL_SOURCE     Right hand side=TEXT
$i=ALL_SOURCE:TYPE            Left hand side=ALL_SOURCE     Right hand side=TYPE
$i=USER_SOURCE:LINE           Left hand side=USER_SOURCE    Right hand side=LINE
$i=USER_SOURCE:NAME           Left hand side=USER_SOURCE    Right hand side=NAME
$i=USER_SOURCE:TEXT           Left hand side=USER_SOURCE    Right hand side=TEXT
$i=USER_SOURCE:TYPE           Left hand side=USER_SOURCE    Right hand side=TYPE

There are plenty of ways to do this.