Hi ,
I have a txt file with contents like:
1234
2345
3456
7891
I need to write a script which takes input file as txt file..run a sql query for that number and place the output of query in another file..
select * from bus_event where acct_nbr='1234'( from input txt file)
the query should run for all numbers in txt file and place the result in another txt file line by line..
Please anyone help me 
printf "select
*
from
bus_event
where
acct_nbr='%s';\n" $(<infile)
Shell independent option:
nawk -v q="'" '{ print "select * from bus_event where acct_nbr = " q $1 q ";"}' infile
I need to send the output of query executed for each number present in input file to a txt file (output file)
printf > outputfile "select
*
from
bus_event
where
acct_nbr='%s';\n" $(<infile)
I have executed the above code but it throwed an error:
db_user=`echo $DB_USER_NAME`
db_pwd=`echo $DB_PASSWORD`
db_sid=`echo $TWO_TASK`
`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
set pagesize 0 feedback off verify off heading off echo off
printf > out.txt 'select * from bus_event where bus_event_seq_nbr='%s';\n' $(<./seqnbr.txt)
exit;
EOF`
Error throwed is -
SP2-0734:: not found
Yes,
you've got an error because you need something different.
# these seem unnecessary ...
db_user=$DB_USER_NAME
db_pwd=$DB_PASSWORD
db_sid=$TWO_TASK
{
printf 'set pages 0 feed off ver off head off echo off\n'
printf "select
*
from
bus_event
where
bus_event_seq_nbr='%s';\n
" $(<./seqnbr.txt)
} |
sqlplus -s "$db_user"/"$db_pwd"@"$db_sid"
Or even:
{
printf 'set pages 0 feed off ver off head off echo off\n'
printf 'select
*
from
bus_event
where
bus_event_seq_nbr in ( %s );\n' $(
printf "'%s'\n" $(<./seqnbr.txt) |
paste -sd, -
)
} |
sqlplus -s "$db_user"/"$db_pwd"@"$db_sid"
I ran the above code but no output file is created.
The output file should contain query result.
But I didnt find any such file created.
But you see it in your terminal, don't you?
You need to redirect the output:
{
printf 'set pages 0 feed off ver off head off echo off\n'
printf 'select
*
from
bus_event
where
bus_event_seq_nbr in ( %s );\n' $(
printf "'%s'\n" $(<./seqnbr.txt) |
paste -sd, -
)
} |
sqlplus -s "$db_user"/"$db_pwd"@"$db_sid" > output
No I didnt get anything displayed.
OK,
what do you see when you execute this code?
{
printf 'set pages 0 feed off ver off head off echo off\n'
printf 'select
*
from
bus_event
where
bus_event_seq_nbr in ( %s );\n' $(
printf "'%s'\n" $(<./seqnbr.txt) |
paste -sd, -
)
}
I didnt get anything displayed on the terminal..when I redirected the output...output file is created with the content of SQL usage but not the output of select command.
code i have used is :
{
printf 'set pages 0 feed off ver off head off echo off\n'
printf 'select
*
from
bus_event
where
bus_event_seq_nbr in ( %s );\n' $(
printf "'%s'\n" $(<./seqnbr.txt) |
paste -sd, -
)
} |
sqlplus -s "$db_user"/"$db_pwd"@"$db_sid" > ./output.txt
OK,
run this command and post the output:
set -xv
{
printf 'set pages 0 feed off ver off head off echo off\n'
printf 'select
*
from
bus_event
where
bus_event_seq_nbr in ( %s );\n' $(
printf "'%s'\n" $(<./seqnbr.txt) |
paste -sd, -
)
}
set +xv
It seems correct, isn't it? And what happens when you execute this command in sqlplus?
select
*
from
bus_event
where
bus_event_seq_nbr in ( '3969495','3969503','3969511','3969491','3969499' );
Could also please post the content of the output file (the error message)?
[quote=radoulov;302565161]
It seems correct, isn't it? And what happens when you execute this command in sqlplus?
I have redirected the output to a file called output.txt
but the content of output did not contain the expected result.
set -xv
{
printf 'set pages 0 feed off ver off head off echo off\n'
printf 'select
*
from
bus_event
where
bus_event_seq_nbr in ( %s );\n' $(
printf "'%s'\n" $(<./seqnbr.txt) |
paste -sd, -
)
}
set +xv
sqlplus -s "$db_user"/"$db_pwd"@"$db_sid" > ./output.txt
[quote]
The output file content is like :
mingle% more output.txt
SQL*Plus: Release 10.2.0.4.0 - Production
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Usage 1: sqlplus -H | -V
-H Displays the SQL*Plus version and the
usage help.
-V Displays the SQL*Plus version.
Usage 2: sqlplus [ [<option>] [<logon>] [<start>] ]
<option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]
-C <version> Sets the compatibility of affected commands to the
version specified by <version>. The version has
the form "x.y[.z]". For example, -C 10.2.0
-L Attempts to log on just once, instead of
reprompting on error.
-M "<options>" Sets automatic HTML markup of output. The options
have the form:
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
-R <level> Sets restricted mode to disable SQL*Plus commands
that interact with the file system. The level can
be 1, 2 or 3. The most restrictive is -R 3 which
disables all user commands interacting with the
file system.
-S Sets silent mode which suppresses the display of
the SQL*Plus banner, prompts, and echoing of
commands.
<logon> is: (<username>[/<password>][@<connect_identifier>] | /)
[AS SYSDBA | AS SYSOPER] | /NOLOG
Specifies the database account username, password and connect
identifier for the database connection. Without a connect
identifier, SQL*Plus connects to the default database.
The AS SYSDBA and AS SYSOPER options are database administration
privileges.
The /NOLOG option starts SQL*Plus without connecting to a
database.
<start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]
Runs the specified SQL*Plus script from a web server (URL) or the
local file system (filename.ext) with specified parameters that
will be assigned to substitution variables in the script.
When SQL*Plus starts, and after CONNECT commands, the site profile
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run. The files may
contain SQL*Plus commands.
Refer to the SQL*Plus User's Guide and Reference for more information.
This is not correct, it should be:
set -xv
{
printf 'set pages 0 feed off ver off head off echo off\n'
printf 'select
*
from
bus_event
where
bus_event_seq_nbr in ( %s );\n' $(
printf "'%s'\n" $(<./seqnbr.txt) |
paste -sd, -
)
} |
sqlplus -s "$db_user"/"$db_pwd"@"$db_sid" > ./output.txt
set +xv
Note that there is a pipe after the closing brace.
You need to assign values to these variables:
db_user
db_pwd
db_sid
Thankyou verymuch it worked..
But can it be modified to not to display anything on terminal when ran..
becoz am getting lot of content displayed over terminal when i run the script.
mingle% new.ksh
{
printf 'set pages 0 feed off ver off head off echo off linesize 2000\n'
printf 'select
*
from
bus_event
where
bus_event_seq_nbr in ( %s );\n' $(
printf "'%s'\n" $(<./seqnbr.txt) |
paste -sd, -
)
} |
sqlplus -s "$db_user"/"$db_pwd"@"$db_sid" > ./output.txt
+ sqlplus -s atlvivd/vivdev1@ltd119a
+ printf set pages 0 feed off ver off head off echo off linesize 2000\n
+ 1> ./output.txt
+ paste -sd, -
+ 0< ./seqnbr.txt
+ printf '%s'\n 3969495 3969503 3969511 3969491 3969499
+ printf select
*
from
bus_event
where
bus_event_seq_nbr in ( %s );\n '3969495','3969503','3969511','3969491','3969499'
set +xv