Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Script output format

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script output format
# 1  
Old 07-03-2008
Script output format
Hi Gurus,

1. I have a script to run a SQL report. The script ran fine, but the format of the output is not satisfactory in that I see too much white space in between the lines in the output file (logfile). Please see the example below:

Code:
010192S.CD                01117CV000A.CD                     500


130098S.CD                13135CV001A.CD                     500


130098S.CD                13135CV002A.CD                     500

2. Here is my testing shell script:

Code:
#!/usr/bin/sh

USERPASS=`/bin/cat /u01/app/oracle/admin/owdv/scripts/cronjobs/cuucpp.txt`
SCRIPT=/u01/app/oracle/admin/owdv/scripts/cronjobs/NEW_INTEGRITY_BOM_CD_DI.sql
LOGFILE=/u01/app/oracle/admin/owdv/scripts/cronjobs/logs/new_integrity_bom_cd_di.log
...

echo "$USERPASS@owdv\n@$SCRIPT\nexit"|/u01/app/oracle/product/9.2.0.1/bin/sqlplus >$LOGFILE

3. Below is my SQL script:

Code:
ttitle left 'CD Items in BOM 500 but associated DI Items missing ' skip 1 -
left '========================================================='
select * from z_bom_cd_tab
where not exists
  (select null from z_bom_di_tab
     where substr(bom_DI_IXKITL,1,(instr(bom_DI_IXKITL,'.')) -1) = substr(bom_CD_IXKITL,1,(instr(bom_CD_IXKITL
,'.')) -1))
and substr(BOM_CD_IXKITL,1,(instr(BOM_CD_IXKITL,'.')) -1) not like '%ORTHOKIT%'
and substr(BOM_CD_IXKITL,1,(instr(BOM_CD_IXKITL,'.')) -1) not like '%DFIRMKIT%'
order by BOM_CD_IXKITL;

4. Can someone advice how I can get rid of these white spaces in the logfile?

Thanks in advance!
Last edited by radoulov; 07-03-2008 at 11:52 AM.. Reason: added code tags
# 2  
Old 07-03-2008
Check the sqlplus set statement: the linesize and the trimspool options.
Last edited by radoulov; 07-03-2008 at 05:12 PM..
# 3  
Old 07-03-2008
Probably your linesize... Try "set linesize 72" at top of SQL script.... Your lines are probably wrapping cause they are filled with trailing whitespace. TRIMSPOOL also a possibility....

Also... It might be more elegant to SPOOL your output inside the sql script itself and pass the name of the spool file there instead of re-directing stdout to your logfile. You get more control that way....

at top of sql script

"set linesize 72"
"spool $1"

at bottom

"spool off"

Then when you run from the shell script, you pass the file name which becomes $1....

"sqlplus USERPASS@connect scriptname LOGFILENAME"

etc....
# 4  
Old 07-03-2008
Script Output Format
Setting linesize to 72 seems to have solved the problem. However, the logfile seems interrupted with some unwanted lines in the middle. Please see the sample below. Any idea? Thanks!

/u01/app/oracle/admin/owdv/scripts/cronjobs/logs-> more new_integrity_bom_cd_di.log
...
48149S.CD 481147S.CD 500
48149S.CD 481565S.CD 500
481SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
500
54011CP.CD 54011C0020D.CD 500
54011CP.CD 54011C0035D.CD 500
54011CP.CD 54011C0136D.CD 500
...
# 5  
Old 07-03-2008
Script Output Format
1. I changed to spool the output file inside my sql script, and the problem solved.

2. I am trying to redirect the errors to a file, and encountered an error that the file can not be created.

Code:
/u01/app/oracle/admin/owdv/scripts/cronjobs-> . test_bom_cd_di_2.sh
ksh: /u01/app/oracle/admin/owpr/scripts/cronjobs/logs/shell_new_integrity_bom_cd_di.err: cannot create

3. But when redirecting the error file to /tmp, I have no problem.

Please advice. I appreciated your input. Smilie

Thanks!
Last edited by radoulov; 07-03-2008 at 05:13 PM.. Reason: added code tags
# 6  
Old 07-03-2008
Two possibilities:

1. The directory /u01/app/oracle/admin/owpr/scripts/cronjobs/logs/ does not exist.

2. You have no write permission for that directory.
# 7  
Old 07-03-2008
Script Output Format
You are absolutely right! Thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Script to generate HTML output format listing like orasnap

Hi, Is there any UNIX scripts out there that generates a listing output of some sort similar to OraSnap At the moment, I have a script that I run on multiple servers that has multiple databases and just querying the database sizes of those databases. It generates a text files that contains... (0 Replies)
Discussion started by: newbie_01
0 Replies

2. Shell Programming and Scripting

Script Output coming in wrong format....

Hi team, getting output logs wrong in different format from telnet script ... getting Output.txt macro_outdoor_dist-6.0.0(v4_0_2) DN:1.3.903 (1101:100:11w:500:3:2:103:aa) macro_outdoor_dist-8.1.0(v3_1_0) DN:1.3.409 (N/A)... (3 Replies)
Discussion started by: Ganesh Mankar
3 Replies

3. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

4. Shell Programming and Scripting

awk Script to format output

Hi all, I'm fairly new to this and learning along the way, so bare with me... I'm trying to format the output from a script to be more read-friendly. The output contains more servers and more processes but for as example it will do: Server: Test1 ... (4 Replies)
Discussion started by: Bobsonm
4 Replies

5. Shell Programming and Scripting

UNIX shell script to format output report

i am new to unix shell scripting, could someone please help me. i was asked to develop a unix script and requirement of the script is as follows: 1. In source directory, if any new files are being dropped by external system then an email should be sent out with a message saying "files are... (3 Replies)
Discussion started by: crefi1545
3 Replies

6. Shell Programming and Scripting

Output not in correct format - cd script

I have a script that looks like this: dirname2=/usr/tmp/filelist/*/* for dirname2 in /tmp/filelist/*/*; do (cd $dirname2/catalog ||echo "file does not exist" && echo "$dirname2" |cut -d '/' -f 7,8 && echo $i && ls -la |awk 'NR>3 {SUM += $5} END { print "Total number of kb " SUM }');done... (2 Replies)
Discussion started by: newbie2010
2 Replies

7. Shell Programming and Scripting

Help with perl script to output data in table format...

Hello, I need help with a perl script that will process a text file and match virtual server name to profile(s). the rest will be ignored. Virtual server name follows the word "virtual" in the begging of the line. There could be multiple profiles assigned to one virtual server. For example, ... (3 Replies)
Discussion started by: besogon
3 Replies

8. Shell Programming and Scripting

shell script to format command output

Hello team, I am running below command which is giving following output. bash-3.00$ ps -eo pid,pcpu,args | sort +1n | grep -i java 12 0.0 grep -i java 8804 0.0 /opt/app/ccr/home/ccr/WebSphere/AppServer/java/bin/sparcv9/java -XX:+UnlockDiag 9241 0.0... (7 Replies)
Discussion started by: coolguyamy
7 Replies

9. Solaris

Duplex Script - output format issue.

Hi Gurus, I had downloaded the below script from the net and used it to get the Link and duplex settings in my Sun servers. In all except one(Sol-5.10 on X86) i am getting output format like below: root: /var/ADMIN/bin/speed_duplex.sh Interface Speed Duplex --------- ... (2 Replies)
Discussion started by: Hari_Ganesh
2 Replies

10. Shell Programming and Scripting

Format output using awk in script.

Guys, I have a script which hits the database and pulls the information that I need into files. Now I want to format these files to make them easy to read. The sample format of the file will be like.... <Start_of_File> Header1 .....xsdfsfa...adfa...... Header2 ....afefas .aefaefsdf...... (8 Replies)
Discussion started by: bperl
8 Replies
Login or Register to Ask a Question