Visit Our UNIX and Linux User Community


shellscript.query Oracle table..populate in a text file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting shellscript.query Oracle table..populate in a text file
# 1  
Old 08-17-2006
shellscript.query Oracle table..populate in a text file

Hi Guys,
I'm new to this forum as well as to UNIX shell scripting.
I'm looking for a shellscript to query an Oracle database table and populate the result set of the query in a text file.
Could you someone help me out with a sample code?

Thanks,
Bhagat
# 2  
Old 08-17-2006
Computer orcle query ans

Hi Bhagat,
U can use the following query to make connection to a oracle database and spool the reults
to a text file:--

sqlplus -s username@databse_string/password << EOF
whenever sqlerror exit sql.sqlcode;
whenever oserror exit FAILURE

set define off
set head off
set feedback off
set echo off
set pagesize 0
set pages 0
set linesize 200
set timing off

spool appropriate path/dummy.txt
UR QUERY GOES HERE
spool off

EOF

If u want to make only manipulations to databse without spooling the results to a any fimle,
dont put it with in SPOOL command.

Hope it works for u......
# 3  
Old 08-17-2006
here is the script to query and put output in a file

Code:
X=`sqlplus -s user/pwd@host<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select * from table where rownum<5;
EXIT;
eof`

echo $X>testing.dat

and check this url where the same question was answered.this
# 4  
Old 08-17-2006
Thanks both of you!!!
That was very helpful!!
Appreciate your guidance

Regards,
Bhagat
# 5  
Old 09-04-2006
shellscript.query Oracle table..populate in a text file

Hi Guys,
I'm looking for a slightly modified script this time...
ie.Query Oracle database table and populate the result set of the query in a text file.The column values should be seperat
I also want the count of records returned by the Oracle query.
Could you someone help me out with a sample code?

Thanks,
Bhagat
# 6  
Old 09-04-2006
Hi Bhagat
There is small change and i guess will be pretty simple.Give path and filename in spool command where it will store the output.In select statement give column names you want to be in the output and here '|' is used as a delimiter you can use any char you want to.
Code:
sqlplus -s user/pwd@host<<eof
set heading off;
set linesize 1000;
spool /dirpath/filename;
select col1||'|'||col2||'|'||col3.......... from table where condition;
spool off;
EXIT;
eof

and counting for no of rows you can chack with a command to spooled file
x=`wc -l /path/filename | awk '{print $1}'`
echo "no of records \c $x"
# 7  
Old 09-04-2006
sqlplus -s user/pwd@host<<eof>urfile
set serveroutput on feedback off linesize 1000 pagesize 0;
select * from table where rownum<5;
EXIT;
eof

Previous Thread | Next Thread
Test Your Knowledge in Computers #813
Difficulty: Medium
The CSS RGBA alpha parameter defines the opacity as a number between 1.0 (fully transparent) and 0.0 (fully opaque).
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Insert one table and update another with shellscript

I have a problem with my shell script. I want to insert data from file to table1(empty) and then, compare table1 with table2 and update some fields. The first part is correct, but the second part does not work. The only way it works is if after the first part I truncate table1 and run the script... (1 Reply)
Discussion started by: nika_mill
1 Replies

2. UNIX for Advanced & Expert Users

How can i populate the record in to table using shell scripting?

LOG_DIR=/app/rpx/jobs/scripts/just/logs sendEmail() { pzCType="$1"; pzTitle="$2"; pzMsg="$3"; pzFrom="$4"; pzTo="$5"; pzFiles="$6"; pzReplyTo="$7" ( echo "From: $pzFrom\nTo: $pzTo\nSubject: $pzTitle${pzReplyTo:+\nReply-To: $pzReplyTo}" ] && echo... (1 Reply)
Discussion started by: ankit.mca.aaidu
1 Replies

3. Shell Programming and Scripting

shellscript to read data from txt file and import to oracle db

Hi all, Help needed urgently. I am currently writing a shellscript to read data/record from a flat file (.txt) file, and import/upload the data to oracle database. The script is working fine, but it takes too long time (for 18000 records, it takes around 90 mins). I guess it takes so long... (1 Reply)
Discussion started by: robot_mas
1 Replies

4. Shell Programming and Scripting

mysql query in shellscript

Hi, I want to run below query on shellscript but having one problm. ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME" "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and Date='$latest Date';" ) here Date column contans different below dates 2011-12-01... (11 Replies)
Discussion started by: aish11
11 Replies

5. Shell Programming and Scripting

mysql query in shellscript

Hi, I want to access mysql query from database , for that i have tried the below code #! /bin/bash TABLE_NAME=database1 USER_NAME=root IP_ADDR=111.20.9.256 somevar=`echo "select altid from alert where altid='2724'"| mysql -h $IP_ADDR -u $USER_NAME $TABLE_NAME ` echo $somevar ... (1 Reply)
Discussion started by: aish11
1 Replies

6. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

7. Shell Programming and Scripting

Shell script to query Oracle table

Hi, unix gurnis I need help for following requirement for writing a shell scritp. log in to oracle database, query one table total records (select count(*) from table1), pass the return value to a file. Thanks in advance (2 Replies)
Discussion started by: ken002
2 Replies

8. Shell Programming and Scripting

Shellscript to sort duplicate files listed in a text file

I have many pdf's scattered across 4 machines. There is 1 location where I have other Pdf's maintained. But the issues it the 4 machines may have duplicate pdf's among themselves, but I want just 1 copy of each so that they can be transfered to that 1 location. What I have thought is: 1) I have... (11 Replies)
Discussion started by: deaddevil
11 Replies

9. Shell Programming and Scripting

shellscript to find a line in between a particular set of lines of a text file

i have a file a.txt and following is only one portion. I want to search <branch value="/dev36/AREA/" include="yes"></branch> present in between <template_file name="Approve External" path="core/approve/bin" and </template_file> where the no of lines containing "<branch value= " is increasing ... (2 Replies)
Discussion started by: millan
2 Replies

10. Shell Programming and Scripting

load a data from text file into a oracle table

Hi all, I have a data like, 0,R001,2,D this wants to be loaded into a oracle database table. Pl let me know how this has to be done. Thanks in advance (2 Replies)
Discussion started by: raji35
2 Replies

Featured Tech Videos