Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Script/SQL configuration

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script/SQL configuration
# 1  
Old 01-11-2012
Java Script/SQL configuration
i have a field in a record which is varchar2(180) and i have stored 180 characters in it
when i query record for the value from sqldeveloper, it is coming properly
when i connect from UNIX and try to query the field, i am getting in 3 lines instead of displaying in 1 line
SHELL SCRIPT :
Code:
sqlplus -silent $USER/$passwd@$DB << EOF
                set heading off
                set feedback off
                set serveroutput on;
        whenever sqlerror exit -1
        whenever oserror exit -2
        SPOOL tmpfile;
 select descr60 from tmp where id='1234'
        SPOOL OFF;
EOF


From UNIX
$ echo tmpfile
Code:
12345678901234567890123456789012345678901234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890123456789012345678901234567890
1234567890123456789

FROM SQL DEVELOPER
Code:
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789


i want the value to be displayed in the same line when i query from UNIX
any suggestions on how to solve this?
Last edited by Franklin52; 01-11-2012 at 03:22 AM.. Reason: Please use code tags for code and data samples, thank you
# 2  
Old 01-11-2012
Script/SQL configuration
Hi Vkca ,

output in tmpfile may be appearing due to the page size issue , put this command in script :
set pagesize 200
and since the number of characters in line are more than unix screen size so might be appearing in second line .

Best is to download the file and see text in textpad to confirm the output . Smilie
# 3  
Old 01-11-2012
type the below command and see what is the columns value

Code:
 
stty -a

as per the ouput you given, it should be 80.

you can change the termial settings also..

just see the man page of stty
# 4  
Old 01-11-2012
Java Script/SQL configuration
Thank u all for the reply.

i found that it is not the problem with the screen configuration, but it is the problem with the column width that the sql will write into the buffer

i used 'set lin 300' in the sql. Check the following SQL
Code:
sqlplus -silent $USER/$passwd@$DB << EOF
                set heading off
                set feedback off
                set serveroutput on;
        whenever sqlerror exit -1
        whenever oserror exit -2
set lin 300;
        SPOOL tmpfile;
 select descr60 from tmp where id='1234'
        SPOOL OFF;
EOF

set lin <n> will set the column width to <n> characters width.


Moderator's Comments:
Mod Comment How to use code tags
Last edited by Franklin52; 01-11-2012 at 05:00 AM.. Reason: Correcting code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

2. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

3. Shell Programming and Scripting

pass null value to sql script from korn shell script

There are 4 parameters that I have to pass from korn shell to sql script. 1) I have to check if $1 , $2 , $3 and $4 are null values or not . How can I do that ? 2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script... (11 Replies)
Discussion started by: megha2525
11 Replies

4. Shell Programming and Scripting

configuration for ftp script in main script

Hi, I am new to shell scripting,and i was planning to write a script that will FTP files to destination folder. I was planning that All configuration should be done through a properties files. and finally the output should be Files are transferred I have developed a properties file named... (4 Replies)
Discussion started by: rahul125
4 Replies

5. Shell Programming and Scripting

How to grep sql error in shell script and exit the script?

I need help in the following script. I want to grep the sql errors insert into the error table and exit the shell script if there is any error, otherwise keep running the scripts. Here is my script #!/bin/csh -f source .orapass set user = $USER set pass = $PASS cd /opt/data/scripts echo... (2 Replies)
Discussion started by: allinshell99
2 Replies

6. Shell Programming and Scripting

Help with hp configuration script (ksh)

Hi I am writting a configuration script to make changes to the /etc/rc.config.d/nddconf file. I have pulled this section out to test it so I have the editing of the file already written. I have two problems with the function. 1. The script is ignoring the results of the if loop. 2. The sed... (12 Replies)
Discussion started by: korg
12 Replies

7. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

8. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

9. UNIX for Advanced & Expert Users

Script to get system configuration

Hi, We have >1000 UNIX boxes in our environment with various UNIX flavors like Solaris, HP-UX and Redhat Linux ES 3/4/5. We need to collect their system configuration like - No. of CPUs and their frequencies - RAM Size - No. of HDDs installed and their usage - Exact OS Version and its... (3 Replies)
Discussion started by: prvnrk
3 Replies

10. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies
Login or Register to Ask a Question