Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: SQL PLUS report formatting
Top Forums UNIX for Dummies Questions & Answers SQL PLUS report formatting Post 302510779 by max_hammer on Tuesday 5th of April 2011 02:30:55 AM
Old 04-05-2011
SQL PLUS report formatting
Hi

I am fetcthing the data from the oracle database using SQLPLUS. Here is my script
Code:
#!/bin/ksh
echo `sqlplus -s <<EOF
     set feedback off
     set linesize 5000
     set pages 0
     set space 0
     set echo off
     set trimspool on
     set colsep '|'
     SELECT col1 , col2 , col3 FROM table_1;
     exit;
     EOF`

The problem is all the data is comimg in single record like
Code:
abc|def|123|awe|wed|12|qwe|qwe|45

I want in format
Code:
abc|def|123
awe|wed|12
qwe|qwe|45

i have found a work around using RECSEPCHAR and RECSEP properties

Code:
#!/bin/ksh
echo `sqlplus -s <<EOF
     set feedback off
     set linesize 5000
     set pages 0
     set space 0
     set echo off
     set trimspool on
     set colsep '|'
     RECSEPCHAR '~'
     RECSEP EACH 
     SELECT col1 , col2 , col3 FROM table_1;
     exit;
     EOF` |  tr -s '~' | tr '~' '\n'

now if the number of recrds are large then it taking lot more time as SQLPLUS put a record of 5000 ~ charcter after each record

Is there any attribute in SQLPLUS that i can set to get data in desired format

Thanks in Advance

Regards ,
Digvijay Singh
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Formatting a report from 2 files

I have 2 files with sample data enclosed. 1. GL (already sorted in ascending order by Gl number) Gl number*glname*Year*opening balance 1000*Interest Income*2006*100.00 1005*Rental Income*2006*0.00 ... 2. Transactions (file is not sorted on GL number or any other field) Branch*Year*Gl... (12 Replies)
Discussion started by: augustinep
12 Replies

2. Shell Programming and Scripting

formatting the sql select result

Hi, I have about 12 columns and 15 rows to be retrived from sybase isql command through unix. But when i output the sql into a file and see it, the formatting is going for a toss. can someone please suggest how can i get the result correctly in the output file ? Thanks, Sateesh (2 Replies)
Discussion started by: kotasateesh
2 Replies

3. Shell Programming and Scripting

formatting into CSV format of SQL session output

I am getting a no of fields from a SQL session (e.g. select a,b,c from table). How do I convert the output values into CSV format . The output should be like this 'a','b','c', (4 Replies)
Discussion started by: mady135
4 Replies

4. Shell Programming and Scripting

sql select command output formatting in shell script

Hi, I need to connect to the database and retrieve two variables from the database and store them in a variable,out of these two variables I need to get lastdigit appended to the variable 1 retrieved and variable 2 with out any modification in short select var,data from usage; o/p=... (1 Reply)
Discussion started by: rkrish
1 Replies

5. Shell Programming and Scripting

Formatting a report using awk

Our vendor produces a report that I would like to format in a particular way. Here is the sample output from their report: # AA.INDEX 2 11 2 239 52 (7,2) 07 MAY 11 203.1 55 # ACCOUNT 2 89561 2 ... (4 Replies)
Discussion started by: thaller
4 Replies

6. Shell Programming and Scripting

Problem in formatting output of SQL query in excel sheet in shell script

Hi Guys.. Need your help to format the output of my shell script. I am using spool command to take out put in csv file. below is my code. (for example) col USERNAME for a15 col EMAIL for a30 col FULL_NAME for a20 col LAST_LOGIN for a40 col DATE_CREATED for a40 SPOOL 120.csv... (3 Replies)
Discussion started by: Agupte
3 Replies

7. Shell Programming and Scripting

Formatting Report and Reading data and fetching the details from contents file

Data I was trying to write shell script which will be return the output in the below format First i was trying to do these using sed. sed -n '/.ksh/p' mainksh.ksh sed -e 's/*\(.*\)/\1/g' mainksh.ksh $RUN_DIR, $SUB_DIR and the variables which will be defined in the profile file. when i am... (0 Replies)
Discussion started by: rameshds
0 Replies

8. Shell Programming and Scripting

Formatting sql in UNIX

hi folks am running the shell script below #!/bin/bash sqlplus 'scott@orcl/tiger'<<ENDSQL >> outputlogfile.csv SET PAGES 0 SET HEAD ON SET ECHO OFF SET FEEDBACK OFF SET LINESIZE 100 SET PAGESIZE 100 SET SERVEROUTPUT ON --# Fire the query on database select * from employee; ... (2 Replies)
Discussion started by: coolboy98699
2 Replies

9. Shell Programming and Scripting

SQL Query in Shell Script output formatting

Hi All, #!/bin/ksh call_sql () { sql=$1 sqlplus -s $sqlparam_sieb <<EOF SET ECHO OFF; SET NEWPAGE NONE; SET SQLBL OFF; SET VERIFY OFF; SET LINESIZE 2000; SET... (2 Replies)
Discussion started by: Kevin Tivoli
2 Replies

10. UNIX for Dummies Questions & Answers

SQL Report from UNIX

I am trying to write a shell script which dynamically reads all the .sql (oracle sql files) from a particular directory and generates a .csv file in the target directory. I have started first with the below sample script to see if it is working but I am not able to get it executed, please help... (7 Replies)
Discussion started by: mora
7 Replies

LEARN ABOUT OPENSOLARIS

getusershell

getusershell(3C)					   Standard C Library Functions 					  getusershell(3C)

NAME

       getusershell, setusershell, endusershell - get legal user shells

SYNOPSIS

       #include <unistd.h>

       char *getusershell(void);

       void setusershell(void);

       void endusershell(void);

DESCRIPTION

       The  getusershell()  function  returns  a  pointer  to  a  legal  user  shell  as defined by the system manager in the file /etc/shells. If
       /etc/shells does not exist, the following locations of the standard system shells are used in its place:

	 /bin/bash		    /bin/csh
	 /bin/jsh		    /bin/ksh
	 /bin/ksh93		    /bin/pfcsh
	 /bin/pfksh		    /bin/pfsh
	 /bin/sh		    /bin/tcsh
	 /bin/zsh		    /sbin/jsh
	 /sbin/pfsh		    /sbin/sh
	 /usr/bin/bash		    /usr/bin/csh
	 /usr/bin/jsh		    /usr/bin/ksh
	 /usr/bin/ksh93 	    /usr/bin/pfcsh
	 /usr/bin/pfksh 	    /usr/bin/pfsh
	 /usr/bin/sh		    /usr/bin/tcsh
	 /usr/bin/zsh		    /usr/sfw/bin/zsh
	 /usr/xpg4/bin/sh

       The getusershell() function opens the file /etc/shells, if it exists, and returns the next entry in the list of shells.

       The setusershell() function rewinds the file or the list.

       The endusershell() function closes the file, frees any memory used by getusershell() and setusershell(), and rewinds the file /etc/shells.

RETURN VALUES

       The getusershell() function returns a null pointer on EOF.

BUGS

       All information is contained in memory that may be freed with a call to endusershell(), so it must be copied if it is to be saved.

NOTES

       Restricted shells should not be listed in /etc/shells.

SunOS 5.11							    1 Nov 2007							  getusershell(3C)
All times are GMT -4. The time now is 07:40 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy