Visit Our UNIX and Linux User Community


Formatting Oracle sqlplus output


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Formatting Oracle sqlplus output
# 1  
Old 03-11-2010
Formatting Oracle sqlplus output

a job extracts orcle data into unix as flat file.
a single record breaks into two record in unix flat file. This is the case only for 6 records out of 60 lack records.

(its not single record in two line. but its single record into record. ie., \n come into picture)

can you tell me what would be the problem? how can i make them single record.?

Last edited by radoulov; 03-11-2010 at 11:17 AM.. Reason: Title fixed.
# 2  
Old 03-11-2010
Quote:
Originally Posted by Gopal_Engg
[...]
(its not single record in two line. but its single record into record. ie., \n come into picture)
I don't understand, what's the difference?

Quote:
can you tell me what would be the problem? how can i make them single record.?
Please post the code you're executing and an example of the output.
# 3  
Old 03-11-2010
Quote:
Originally Posted by Gopal_Engg
...
(... but its single record into record. ie., \n come into picture)
Huh ? And what does "record into record" mean ?
If there are newlines ("\n" characters) in a column value then you can use the REPLACE function to replace each newline to a single blank space.

Quote:
...
can you tell me what would be the problem?...
Nope. Difficult to tell you unless you give more details.

tyler_durden
# 4  
Old 03-11-2010
Problem w replace() is that it has to be nested for more than one instance of a bad character. This can get to be cumbersome if there's more than say three chars on the list. Might be best to pipe output into either tr, sed or something that can swap or reduce the chars for you as a range or a char class. Likewise on a file previously spooled, but much more difficult since you'd need to parse the file first.
# 5  
Old 03-11-2010
Please state what Operating System and version you have.
Please state what shell you use (e.g. sh, ksh, bash).
Please post what version of Oracle you have.
Please post the script.
Please post what you typed to execute the script.
Please post sample input data (blotting anything confidential).
Please post what you expected to happen.
Please post what actually happened (including any error messages).
# 6  
Old 03-11-2010
The answer: set linesize 999 (* or some other large size as required).
IF you don't want headings - set pages 0 stops that and page feeds.

sqlplus wraps at linesize, the default is 80.
Code:
set linesize 999
set pages 0
set trimspool on
spool someoutputfile.txt
select * from sometable;
spool off



---------- Post updated at 18:02 ---------- Previous update was at 17:57 ----------

PS: lack, lac, or lakh is a number designation that is maybe not known by a lot of the people here -- ones you really want helping you.
# 7  
Old 03-11-2010
Could be the answer, but without a sense of the DDL or data involved I guess it really depends on the OP's actual problem. Could still be GIGO...

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing Oracle function as file input to sqlplus

Apologies if this is the incorrect forum.There is an issue in the function call I am facing while calling the same from a unix shell scripts. Basically, I want the ref cursor to return values to a variable in sqlpus. The function call is currently saved in a ".txt" file in a unix location. I want... (7 Replies)
Discussion started by: amvip
7 Replies

2. UNIX and Linux Applications

Please help: Oracle gqsql or sqlplus output format like mysql

On psql select titolo,lingua from titolo where titolo ~* 'brivid'; titolo | lingua ------- + ------ Brivido | 1 On Sqlplus/gqsql SQL> select titolo,genere,anno,lingua from titolo where titolo like '%rivid%'; TITOLO... (6 Replies)
Discussion started by: Linusolaradm1
6 Replies

3. Shell Programming and Scripting

Connect to Oracle using sqlplus

I have logged into oracle using SQLPLUS. When I type any kind of query, there is only 1 answer - '2'. What is wrong with it? (1 Reply)
Discussion started by: Subhasis
1 Replies

4. Shell Programming and Scripting

Problems with storing oracle sqlplus query output shell script

Hello everyone, I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected. I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't... (3 Replies)
Discussion started by: rockf1bull
3 Replies

5. Shell Programming and Scripting

redirecting oracle sqlplus select query into file

So, I would like to run differen select queries on multiple databases.. I made a script wich I thought to be called something like.. ./script.sh sql_file_name out.log or to enter select statement in a command line.. (aix) and I did created some shell script wich is not working.. it... (6 Replies)
Discussion started by: bongo
6 Replies

6. Shell Programming and Scripting

Connecting to Oracle DB using sqlplus

Hi, I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following: 1. Connect to oracle database hosted on a different server 2. fire a query on the oracle db 3. store the output in a variable 4. use this variable for further logic... (26 Replies)
Discussion started by: shrutihardas
26 Replies

7. UNIX for Dummies Questions & Answers

Connecting to Oracle DB using sqlplus

Hi, I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following: 1. Connect to oracle database hosted on a different server 2. fire a query on the oracle db 3. store the output in a variable 4. use this variable for further logic... (1 Reply)
Discussion started by: shrutihardas
1 Replies

8. Shell Programming and Scripting

Running Oracle SqlPlus with Java in Kshells

Hello, This may not be the right place to ask for help for this problem but i might be because i'm using Java & SqlPlus in alot of Kshell scripts. Just to give a high level picture, i'm basically using Java to control each SqlPlus execute command, by reading a flat file that contains the run... (2 Replies)
Discussion started by: bobk544
2 Replies

9. UNIX and Linux Applications

How to access Oracle table using sqlplus

Hi, I want to use sqlplus from server1 sqlplus usr1/pass1@dns1 and I want to connect to an Oracle database from a server2. Unfortunately the database was created on the server1 and on server2. So when I use the command just like that...it connects to the database from the server2. ... (2 Replies)
Discussion started by: AngelMady
2 Replies

10. UNIX for Advanced & Expert Users

Problem while calling Oracle 10g SQLPLUS files

Hi all, Iam facing a lot of problem while calling Oracle 10g SQLPLUS files from shell. What is the standard procedures to be taken care. Any help would be useful for me. Thanks in advance, Ganapati. (2 Replies)
Discussion started by: ganapati
2 Replies

Featured Tech Videos