Shell Programming and Scripting

HI ALL
i have a requirement like this. i have to write a shell script to run a sql query. DB is oracle. once the query is run, the results of the query has to be published in a data file. can you please advice me how to go about it. i am absolutely new to shell scripts and this is a part of my job.

This question is answered several times a week; please search the forums.

thanks for the reply, i am new to this group and I will look for the posts

This post may get you started.

try this

DATA=`echo "SET HEADING OFF;\n SET FEED OFF;\n select <whatever query you want here > ;\n exit\n"|sqlplus -s /`

echo $DATA > <whatver file you want>

hi

i tried your query. when i execute that i am getting an error like this "SQL plus cant be found" is there a way not to mention the user name and pwd in the script. thanks

Not exactly as you have asked but that doesn't sound like your problem. Here is one method:

Code:

sqlplus -s /nolog <<EOF > youroutputfile
connect un/pw@db            <== login credentials inside here document
<your sql>;
EOF

If sqlplus can't be found then ${ORACLE_HOME}/bin is not in your path. If that is not in your path then you probably didn't prepare your environment properly by invoking /usr/local/bin/oraenv.

Code:

EXPORT ORACLE_SID=yoursid
EXPORT ORAENV_ASK=N
. /usr/local/bin/oraenv

sqlplus -s /nolog <<EOF > youroutputfile
connect ...
...
EOF

You can also use a named pipe to log in to the database as follows:

Code:

mknod /tmp/loginfile.sql p

print "connect un/pw" > /tmp/loginfile.sql &       <== login credentials in a sql script temporarily
sqlplus -s /nolog <<EOF > youroutputfile
@/tmp/loginfile.sql
...
EOF

You must provide login credentials in order to query the database. Your DBA can configure accounts that allow you to not specify any username or password as you have been given examples for. If your DBA defines accounts using OPS$ prefix, then you can log in as follows:

Code:

sqlplus -s / <<EOF >yourloginfile.sql    <== login credentials tied to your os login
...
EOF