Removing unnecessary eol ($) character from Oracle sql query output


 
Thread Tools Search this Thread
# 1  
Removing unnecessary eol ($) character from Oracle sql query output

Hi All,

I am fetching oracle query result in shell variable. As columns numbers are more the output wraps in unix terminal .i.e one complete record in db gets store in multiple lines. with each line ends with $ character. I want to remove these unnecessary $ character but to keep required $ character at the end of record.

I have set following set option in oracle query
Code:
  set feedback off
	set linesize 1800
	set colsep '|'
	set pagesize 0
	set trimout on
	COL ORIGINTYPE FORMAT A15
	COL ORGNAME FORMAT A10
	COL USERNAME FORMAT A12
	COL LOCATION FORMAT A20
	COL OBJECTTYPE FORMAT A45 
	COL TIMEOFACTION FORMAT A20
	COL CATEGORYCODE FORMAT A16
	COL OBJECTNAME FORMAT A500
	COL RESULT FORMAT A20
	COL ACTION FORMAT A500
        COL ADDITION FORMAT A500

Shell - sh
OS- HP-UX
Oracle - Oracle 9i

Any help is appreciated.
# 2  
Can you show some examples

Hard to understand without seeing sample output.
# 3  
Quote:
Originally Posted by joeyg
Hard to understand without seeing sample output.
suppose db record is like
Code:
aa|bb|cc|dd|ee|ff|gg|hh|ii|jj|kk

but my output has $ in between where I have given large format space for column.
Code:
aa|bb|cc|$
dd|ee|ff|gg|$
hh|ii|jj|kk|$
$

I hope this will help to understand.

Last edited by Harshal22; 11-06-2012 at 04:19 PM..
# 4  
sed 's//$' filename
# 5  
I have to keep last $ as it is. It is expected eol character.
# 6  
can you try it please

sed 's//$/\n' filename
# 7  
Quote:
Originally Posted by ANSHUMAN1983
can you try it please

sed 's//$/\n' filename
I will let you know tomorrow
 

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sql query output count

Hi Team, below sql rerturn 20 records, the result set i am going to assign to one variable and it showing count is 1. and i don't use count() in sql query... based on count, i need to fail the script. No_of_step=`echo ${g_count} | wc -l` function gf_count() { g_count=`sqlplus -s... (8 Replies)
Discussion started by: bmk123
8 Replies

2. Shell Programming and Scripting

How to Assign the Output of an SQL Query to a Variable?

Hi iam new to shell scripting how to declare variables as redshift query and I have to compare two counts by using if condition . ex:count=select count(*) from prd; select count(*) from prd; select count(*) from tag; can any one help me . Please use CODE tags when displaying... (1 Reply)
Discussion started by: sam526
1 Replies

3. Programming

Oracle simple SQL query result in: ORA-08103: object no longer exists

Dear community, please help with a query on Oracle. I'm using SQLPlus (but with SQLDeveloper is the same) to accamplish a sinple query like: select count(*) from ARCHIT_D_TB where (TYP_ID=22 OR TYP_ID=23) and SUB_TM like '%SEP%' and CONS=1234This is a very simple query that works perfect until... (5 Replies)
Discussion started by: Lord Spectre
5 Replies

4. Solaris

SQL QUERY to Table Output

Hi I am trying to run sql query from solaris in csh script and send the output to email. Below is my sql query select p.spid,se.program seprogram, se.machine, se.username, sq.sql_text,sq.retrows from v$process p inner join v$session se on p.addr = se.paddr inner join ( select... (2 Replies)
Discussion started by: tharmendran
2 Replies

5. Shell Programming and Scripting

How to run a SQL select query in Oracle database through shell script?

I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this? Is there a way to have a persistent connection to oracle database... (9 Replies)
Discussion started by: vel4ever
9 Replies

6. Shell Programming and Scripting

perl- oracle sql query

Hi, I am new to perl.How to query oracle database with perl??? Thanks (1 Reply)
Discussion started by: tdev457
1 Replies

7. Shell Programming and Scripting

Read value from user and use it in Oracle SQL query

Guys can anyone just tell me whether i can pass a value(from UNIX SCRIPT) as an ARGUMENT in Oracle Query? e.g. echo "enter value" read value insert into tablename where col=$value /*something like this*/ (1 Reply)
Discussion started by: subodh.thakar
1 Replies

8. Shell Programming and Scripting

Removing unnecessary Delimiters from a file

Hi all, I have a pipe delimited file - (sample data) 1|1|K|Doe|1234567890|123456789|EXP|99|99|John|Y|Dallas|Texas|Kyle|999 2|1|A|2|01/01/9999|Appl|01/01/9999|vendor|Select|||||| 3|1|A|2|01/01/9999|Check|01/01/9999|ksmith|Suggest|||||| 4|1|T|Complaint|Mary|01/01/9999|01/01/9999|||||||... (2 Replies)
Discussion started by: sumeet
2 Replies

9. Shell Programming and Scripting

redirecting sql query output to a file

Hi, I am executing sql files in my unix shell script. Now i want to find whether its a success or a failure record and redirect the success or failure to the respective files. meaning. success records to success.log file failure record to failure.log file. As of now i am doing like... (1 Reply)
Discussion started by: sailaja_80
1 Replies

10. Shell Programming and Scripting

Oracle SQL Query & connect?

Hi I'm looking to query a table on a database and then iterate over the results in a loop. I believe this is the last part of my script that I need (after finding out threads for passing variables to other scripts and calling functions in other scripts). I've searched the forums but the best... (8 Replies)
Discussion started by: Dird
8 Replies

Featured Tech Videos