Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Shell script to run sql query having a long listing of parameters

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script to run sql query having a long listing of parameters
# 8  
Old 05-28-2013
You already have your converted data in a file! There's no barrier to just using that other file instead of the original data.

Since your statement is much huger and more complicated than what you showed, I'd use here documents instead of printf.

Code:
( cat <<EOF
header
EOF

cat abc_csv.txt

cat <<EOF
footer
EOF ) | sqlplus -s user/password@dbschema

# 9  
Old 05-28-2013
Quote:
Originally Posted by Corona688
You already have your converted data in a file! There's no barrier to just using that other file instead of the original data.

Since your statement is much huger and more complicated than what you showed, I'd use here documents instead of printf.

Code:
( cat <<EOF
header
EOF

cat abc_csv.txt

cat <<EOF
footer
EOF ) | sqlplus -s user/password@dbschema

Hi Corona...Thanks for the info...but what is this header and footer...could you please modify your script with the sql query..I mean where to put ... so that I can understand?
# 10  
Old 05-28-2013
Header is all the stuff that comes before $pqr.

Footer is all the stuff that comes after $pqr.

This code avoids needing $pqr at all so should avoid the too many arguments error.
# 11  
Old 05-29-2013
Quote:
Originally Posted by vsachan
Thanks for your suggestion Spacebar.... Smilie
but I can not touch the database...So we need to only use shell script.
Ok, You can just read your file in the shell script dynamically creating your sql statement, each "in clause" can have a max of 1,000 elements, example:
Code:
select co1, col2, col3
 from  schema.table
where  card_number in( 1, 2, 3, ..., 1000 )
  or   card_number in( 1001, 1002, 1003, ..., 2000 )
  or   card_number in( 2001, 2002, 2003, ..., 3000 )
  or   card_number in( 3001, 2002, 3003, ..., 4000 )
  or   card_number in( 4001, 4002, 4003, ..., 5000 )

# 12  
Old 05-29-2013
Quote:
Originally Posted by Corona688
Header is all the stuff that comes before $pqr.

Footer is all the stuff that comes after $pqr.

This code avoids needing $pqr at all so should avoid the too many arguments error.

Thanks Corona...what to write inside the query...But I am not able to form the query..If possible, could you please mention the coding steps as I do not have too much idea of shell scripting?
# 13  
Old 06-03-2013
Just try the code and leave off the | sqlplus ... and you will see what you need to put in it.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Run sql query in shell script and output data save as delimited text

I want to run sql query in shell script and output data save as delimited text (delimited text would be comma) Code: SPOOL_FILE=/pgedw/dan.txt SQL=/pgedw/dan.sql sqlplus -s username/password@myhost:port/servicename <<EOF set head on set COLSEP , set linesize 32767 SET TRIMSPOOL ON SET... (8 Replies)
Discussion started by: Jaganjag
8 Replies

2. UNIX for Dummies Questions & Answers

Script to run sql query.

Please read How To Ask Questions The Smart Way (1 Reply)
Discussion started by: balu_279013
1 Replies

3. 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

4. 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

5. Shell Programming and Scripting

Need help to run sql query from a script..which takes input from a file

I need to run sql script from shell script which takes the input from a file and contents of file will be like : 12345 34567 78657 and query will be like : select seq_nbr from bus_event where event_nbr='12345'; select seq_nbr from bus_event where event_nbr='34567'; select seq_nbr... (1 Reply)
Discussion started by: rkrish
1 Replies

6. UNIX for Dummies Questions & Answers

shell script for long listing of groupnames

Hello, When listing the file systems (using ls -ltr) , if the group names are longer the group name is getting truncated. Can someone help with the script which would display the truncated group name? I appreciate if someone could help in this regard. (3 Replies)
Discussion started by: mike12
3 Replies

7. Shell Programming and Scripting

specified path name is too long passing parameters to awk via shell script

Hello, I have this shell script that runs awk code by passing in parameters however now it doesn't work anymore with the parameters and I don't know why. It removes duplicates from an input file based on a part of the last field and a key column. It removes the record with the older datetime... (0 Replies)
Discussion started by: script_op2a
0 Replies

8. Shell Programming and Scripting

run sql query via perl script

Hello, If I run this command on the server it works. # dbc "update config set radio_enabled = 0;" how can I execute the same command in perl. I have defined the dbc path. Can any one please correct the last line. #!/usr/bin/perl #database path $dbc='/opt/bin/psql -Userver... (0 Replies)
Discussion started by: sureshcisco
0 Replies

9. Shell Programming and Scripting

Dynamic SQL query based on shell script parameters

Hi, I need a script that will run a dynamic Oracle SQL. Dynamic meaning the SQL statement depends on the parameter. For instance, something like this: #!/bin/ksh -x # Set environment . /home/mine/set_vars sqlplus $LOGINID <<! >> /home/mine/log.txt select count(1) from $1 where... (2 Replies)
Discussion started by: laiko
2 Replies

10. Shell Programming and Scripting

Shell Script Passing Parameters For Directory Listing

i have this basic piece of code that i am trying to debug to accept input parameter to be able to display a directory listing of files. cd /u02/app/eatv/dev/out CURDIR=`pwd` echo directory listing of $CURDIR echo if ; then ls -latr else ls -latr $1 fi basically if the script... (9 Replies)
Discussion started by: wtolentino
9 Replies
Login or Register to Ask a Question