Connecting to ORACLE through SHELL

Shell Programming and Scripting
1

Hi,

I am trying to connect to oracle database (9.2.0.7.0) through Sun solaris.
But I am unable to do so.

The content of my script is as follows:

Shell Sript:

RESULT=`$ORACLE_HOME/bin/sqlplus schema/'pwd'@db <<  EOF
set echo on;
set feedback on;
@/home/count/qry.sql`
echo $RESULT

qry.sql

spool /home/count/infoc.txt
set serveroutput on 10000
set serveroutput on FORMAT WRAP
set heading off
set pause off
set linesize 200
select count(*) from ECT

infoc.txt

SQL> set serveroutput on 10000
SP2-0158: unknown SET option "10000"
SQL> set serveroutput on FORMAT WRAP
SQL> set heading off
SQL> set pause off
SQL> set linesize 200
SQL>
SQL> select count(*) from ECT
  2

but i Jus want to store the value of select query which is 1150 in the infoc.txt file.

Please help me to solve problem.

2

You havn't closed the here doc label (EOF)

3

Where to close??

i closed it in fourth line no...!

4 
RESULT=`$ORACLE_HOME/bin/sqlplus schema/'pwd'@db <<  EOF
set echo on;
set feedback on;
@/home/count/qry.sql
EOF`
echo $RESULT
5

still same outputs......i mean same errors....no changes with that too.... :frowning:

6

Are you successfully able to connect?

$ORACLE_HOME/bin/sqlplus schema/'pwd'@db << EOF 2>oracle.log
select count(*) from ECT;
EOF

Please check if its giving o/p on the screen and also check oracle.log ( if created any).

7

What errors do you encounter ?
Paste those over here.

tyler_durden

8

have you actually debugged so far as to try running the following on the command-line on your own machine?

$ORACLE_HOME/bin/sqlplus schema/'pwd'@db

I think you'd find that pwd would be a literal string and not what you'd expect it to be...

Generally, you'd want to do something like the following:

sqlplus -s ${sql_connstr}${my_data} <<-SQL_SCRIPT|egrep -v "^$|rows selected" >|${out_file} 
  ..
SQL_SCRIPT

Now, if you're concerned about your flurry of set commands effecting what's being output to your file, you could play around and manage these to squelch any echoes, etc, or you could also just extend the egrep -v command above to suppress most of the noise.

9

General comment. No time to post specifics.

By using a unix shell "here" document Oracle is in interactive mode and will give you SQL> prompts etc. which obfuscate your output.

If you invoke sqlplus and ask it to run a SQL program from a file it will all become less complicated. Within the program use "set" statements to turn off (not on) feedback etc. and then use Oracle "spool" commands to output your enquiry to file. There are many examples on this site.

sqlplus /NOLOG program_name.sql
10

to methyl's point, I believe: yes...an external SQL file is useful in terms of providing a single point of SQL behavior. However, and possibly to the OP's use of the here doc, it's often more portable to embed the SQL into the script via the here doc.

Overall, I guess you might say, pick a paradigm and go with it, rather than cobble together approaches. Using a here doc to then call a SQL file may not simplify things.

11

First, try to manually connect to your database first. If it works, try the code below.

If you want to store the value in a file. Please see the following code:

$ORACLE_HOME/bin/sqlplus -s schema/'pwd'@db <<  EOF > /home/count/infoc.txt
set echo on;
set feedback on;
@/home/count/qry.sql;
EOF

If you want the output the result to the $RESULT parameter:

RESULT=`$ORACLE_HOME/bin/sqlplus -s schema/'pwd'@db <<  EOF
set echo on;
set feedback on;
@/home/count/qry.sql;
EOF`
echo $RESULT

Then remove the spool line from your SQL file