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
# 1  
Old 05-28-2013
Java Shell script to run sql query having a long listing of parameters
Hi,
I have a query regarding execution of a sql query having long listing of parameters ..I need to execute this query inside a shell script.

The scenario is like....

Suppose I have a file abc.txt that has the card numbers..it could be in thousands..
then I need to fire a query like

select name from table_name where cardnum in ('$x')
where x is the data from the file abc.txt...I am able to execute the query if the data is less ..but if the file abc.txt has thousands of lines..then in that case how to execute it...because when I execute the shell script..it gave me error that there is a long list of parameters...

Please help me to form the shell script to execute it for a long list of parameters....Thanks
# 2  
Old 05-28-2013
The way to avoid too many parameters is not to fiddle and fidget until it accepts too many parameters, as too many parameters will be too many parameters no matter how you cut it. The way to avoid too many parameters is to not use too many parameters.

Perhaps you can avoid reading the file into a shell variable at all:

Code:
( printf "%s" "select name from table_name where cardnum in ('"
        cat parameters.txt
        printf "%s\n" "')" ) | databasecommand

# 3  
Old 05-28-2013
How about using SQL Loader to load the values from abc.txt into a database table say temp_table, and then use this table in the query you mentioned:

select name from table_name where cardnum in (seletc card_no from temp_table)
Last edited by juzz4fun; 05-28-2013 at 04:58 PM..
# 4  
Old 05-28-2013
Quote:
Originally Posted by Corona688
The way to avoid too many parameters is not to fiddle and fidget until it accepts too many parameters, as too many parameters will be too many parameters no matter how you cut it. The way to avoid too many parameters is to not use too many parameters.

Perhaps you can avoid reading the file into a shell variable at all:

Code:
( printf "%s" "select name from table_name where cardnum in ('"
        cat parameters.txt
        printf "%s\n" "')" ) | databasecommand

Hi Corona,
Thanks for the suggestion..but still it is not working...
the input text file is like below:
Code:
1234
7654
9654
5412
7653
.
.
.
9873
6513

I need to run sql query inside a script and then store the result in a file..I am doing it like below:

The command below will make the file abc_csv.txt like below...
'1234','7654','9654'......And it will feed as input to the query. Smilie

Code:
cat abc.txt|tr '\n' ','|sed "s/^/'/"|sed "s/,$/'/"|sed "s/,/','/g">abc_csv.txt
pqr=`cat abc_csv.txt`

sqlplus -s user/password@dbschema<<EOF
alter session set db_file_multiblock_read_count=128;
alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss';
set newpage none feed off
set recsep off
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET LINESIZE 999
SET PAGESIZE 0

set feedback off trimspool on linesize 300
spool sql_out;
SELECT name from table_name where cardnum in ($pqr)
/
spool off;
exit;
EOF

I am able to execute this shell script if there is small data in abc.txt file...but if the file is large ..it is not working
Last edited by Scott; 05-28-2013 at 07:09 PM.. Reason: Code tags
# 5  
Old 05-28-2013
Quote:
Originally Posted by vsachan
abc.txt has thousands of lines
Since your "in list" could be more than 1,000 items, You could use a 'external table' and just join the external table to the target table(s) so that you only retrieve matching card numbers, example:
Code:
-- Create oracle external table linked to a flat file
create table user.table ( 
  card_num                 varchar2( 16 ) )
  organization external (
  type                oracle_loader
  default directory   TMP
  access parameters (
    records delimited by newline
    badfile      'card_numbers.bad'
    discardfile  'card_numbers.dis'
    logfile      'card_numbers.log'
    missing field values are null
    ( card_num ) )
    location('card_numbers.txt') )

# 6  
Old 05-28-2013
Quote:
Originally Posted by spacebar
Since your "in list" could be more than 1,000 items, You could use a 'external table' and just join the external table to the target table(s) so that you only retrieve matching card numbers, example:
Code:
-- Create oracle external table linked to a flat file
create table user.table ( 
  card_num                 varchar2( 16 ) )
  organization external (
  type                oracle_loader
  default directory   TMP
  access parameters (
    records delimited by newline
    badfile      'card_numbers.bad'
    discardfile  'card_numbers.dis'
    logfile      'card_numbers.log'
    missing field values are null
    ( card_num ) )
    location('card_numbers.txt') )

Thanks for your suggestion Spacebar.... Smilie
but I can not touch the database...So we need to only use shell script.
# 7  
Old 05-28-2013
Not sure how feasible is this, break the input file into smaller ones (each file contains as many records as permissible for the IN SQL operator). For each small-input-file, execute your original sql query.
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