Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to acheive ALT+Enter of xls in Unix ksh/sqlplus
# 8  
Old 10-16-2006
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  
Old 10-16-2006
Quote:
Originally Posted by Sree_2503


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


Thanks.
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  
Old 10-16-2006
But, the values in the description column might have embedded spaces in themselves.
# 11  
Old 10-16-2006
Quote:
Originally Posted by Sree_2503
But, the values in the description column might have embedded spaces in themselves.
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:
Code:
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  
Old 10-17-2006
Still another way...
And another way to accomplish the same thing allowing spaces if you wish:
Code:
#! /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
Code:
$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.
Last edited by tmarikle; 10-17-2006 at 01:25 PM.. Reason: Extra curly brace removed
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Line break in sqlplus output through ksh script

Hi, I am new to shell script programming. I have written a ksh script to run the sql File placed in server directory and spool the output in destination directory. Below Command: $ORACLE_HOME/bin/sqlplus -s $ora_uid @$sqlfile_loc$testquery.sql > $opfiledirectory It is generating the output... (6 Replies)
Discussion started by: Sumit Arora
6 Replies

2. Shell Programming and Scripting

Merging Multiple XLS into Different tabs in xls/ xlsx

HI, I have multiple files per dept in folder for eg : In a folder File1_Dept100.xls File2_Dept100.xls File3_Dept100.xls File1_Dept200.xls File2_Dept200.xls File3_Dept200.xls Output should be : Dept100.xls which has File1, File2, File3 in different tabs Dept200.xls which has... (1 Reply)
Discussion started by: venkyzrocks
1 Replies

3. Shell Programming and Scripting

KSH - type ENTER to continue

Hello, Is ther any way to run a loop which promt an echo"waiting for user to hit ENTER" sleeps for an iterval and once user hit the ENTER key the loop exit and the script continue to the next stage? I need this ASAP !!! Thanks!! (9 Replies)
Discussion started by: LiorAmitai
9 Replies

4. Solaris

sqlplus output from ksh.

Hi All, I have the below simple script. It runs just fine by itself when I manually invoke it. But once I put it in the crontab with entry: * * * * * /users/myuser/test.ksh >> /users/myuser/log/test.txt" It does NOT print the returned value ($REMAIN) from the DB!? The result in the... (3 Replies)
Discussion started by: steve701
3 Replies

5. Shell Programming and Scripting

Enter an input and reference another line in ksh script

Hi I have a file like so: Code: Frank Peter Tony Robert Mike 1 2 3 4 5 5 4 2 3 1 4 3 1 5 2 My out should look like this: Peter Tony Mike and so on.... I have the first part done to ask the user to... (8 Replies)
Discussion started by: bombcan1
8 Replies

6. Shell Programming and Scripting

Mail to: cc: How can i acheive this with ksh

Hi All, I have to write a script to send mail, in that i have to dynamically add the recepient in TO and my cc is a common one. For all mails my cc recepients are same. Only problem with TO recepient. Please i am looking for your inputs. (1 Reply)
Discussion started by: Arunprasad
1 Replies

7. Shell Programming and Scripting

Simulate ENTER key in ksh scripting

Using SCO 5, Ksh here I am trying to automate some process that i am doing manually. During some point of time, I will have to press enter to clear away some messages. This is how I do manually. *one command* r r r r r I enter the command once, then press enter key 1 more... (6 Replies)
Discussion started by: khaos83_2000
6 Replies

8. Shell Programming and Scripting

KSH Sqlplus problem

Hi, trying this =============================== Temp=`sqlplus -s user/passwd <<EOF WHENEVER SQLERROR EXIT 1 set serverout on set feedback off set heading off select nam from tabel1 where x=y; EOF` echo Temp>>$logfile ================================= The select statement is... (2 Replies)
Discussion started by: amitkr
2 Replies

9. Shell Programming and Scripting

How to pass variable to SQLPLUS in a ksh script?

Hi, I am writing a ksh script which will use sqlplus to run a sql and pass 2 variables as the SQL request. In the ksh script, I have 2 variables which are $min_snap and $max_snap holding 2 different numbers. Inside the same script, I am using SQLPLUS to run an Oracle SQL script,... (6 Replies)
Discussion started by: rwunwla
6 Replies

10. Shell Programming and Scripting

Need help with ksh script that uses sqlplus, called from PHP

I have a ksh script that connects to sqlplus and dumps the query results into a file. The script works file when I run it from the command line, however, when I call it from PHP using system(), exec(), or shell_exec() commands, the script doesn't seem to run the query. It will create the text file... (7 Replies)
Discussion started by: j2owilson
7 Replies
Login or Register to Ask a Question