How to create a UNIX script to login to SQLPLUS and run SELECT from SQLPLUS?

How to create a UNIX script to login to SQLPLUS and run SELECT from SQLPLUS?
I have SQLPLUS script (in the myscript.sql file,for example) that runs a SELECT query and places an output into a flat file using SPOOL.
To run the script I need to login to SQLPLUS from my UNIX ksh prompt and then execute that script as @myscript.sql
I need to create a shell script file that would login to SQLPLUS and run the SQLPLUS script from there.
I tried to use this code below where everything starting from the line 3 is from the tested myscript.sql script.  (The script itself is simplified here as much as possible).
But after I execute the shell script it just logins into SQLPLUS, displays the SQL prompt and does not do anything else.
What am I missing?
Thanks a lot in advance!
#!/bin/ksh
sqlplus myusername/mypassword

set echo off
set feedback off
set linesize 166
set pagesize 0
set sqlprompt ''
set colsep ""
set term off
set verify off
set trimspool on

spool /myfoldername/fname1.txt
SELECT sysdate from dual;

spool off;
exit;


pavelmedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

woolmilkporcCommented:
Hi,
use a "here" document -

#!/bin/ksh
sqlplus myusername/mypassword <<EOF
set echo off
set feedback off
set linesize 166
set pagesize 0
set sqlprompt ''
set colsep ""
set term off
set verify off
set trimspool on
spool /myfoldername/fname1.txt
SELECT sysdate from dual;
spool off;
quit
EOF
exit
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
a "here" script is the correct answer and should get all the points.

I would suggest one small change for security reasons.  Move the username and password into the script.  This way it hides them from a 'ps -ef' command.

sqlplus <<EOF
connect myusername/mypassword
set echo off
set feedback off
set linesize 166
0
pavelmedAuthor Commented:
I made the changes suggested by woolmilkporc.
It did execute the sqlplus script but he result in the fname1.txt file was this:
SELECT sysdate from dual;
24-MAR-10
spool off;

I should have not displayed the SELECT statement and the "spool off".  The original script that I ran from sqlplus displayed only "24-MAR-10", and that is what I am looking for.
What should be changed to remove SELECT and spool off from the output?

Thank you
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

pavelmedAuthor Commented:
I also tried suggestion by sliqhtwv to move password and username, however it did not login to SQLPLUS.
The script is below:

#!/bin/ksh
sqlplus <<EOF
connect myusername/mypassword

set echo off
set feedback off
set linesize 166
set pagesize 0
set sqlprompt ''
set colsep ""
set term off
set verify off
set trimspool on

spool /home/pmedniko/fname1.txt
SELECT sysdate from dual;

spool off;
quit
EOF
exit
----------------------------------
The error displayed is below:
Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
Enter user-name: ERROR:
ORA-01017: invalid username/password; logon denied
0
slightwv (䄆 Netminder) Commented:
>>should have not displayed the SELECT

try running sqlplus in silent mode:

sqlplus -s myusername/mypassword <<EOF
...

>>Enter user-name: SP2-0306: Invalid option.

sorry.  forgot the /nolog flag.

sqlplus -s /nolog <<EOF
conn myusername/mypassword
...

0
pavelmedAuthor Commented:
I tried this:
sqlplus -s /nolog <<EOF
connect myusername/mypassword

The script did connect to SQLPLUS and produced a right output file, but it also displayed the SELECT output (24-MAR-10) on the UNIX command line (see below).  How can I avoid displaying that?

[/home/mydir]>./test_name2.sh
24-MAR-10
0
slightwv (䄆 Netminder) Commented:
redirect sqlplus output to /dev/null:

sqlplus -s /nolog > /dev/null 2>&1<<EOF
...

0
pavelmedAuthor Commented:
I did redirect sqlplus to dev/null, and now the output is not displayed on the command line.
But potentially that output can be huge, thousands and thousands of records, and I wonder about that redirection that it may consume some space or memory.  The point was to redirect the output to a flat file only.  Can it be done?
Could you please comment?
0
slightwv (䄆 Netminder) Commented:
I'm confused.

from http:#28466256: on the UNIX command line (see below).  How can I avoid displaying that?

from http:#28467004 and now the output is not displayed on the command line

These seem to contradict each other.

Anyway, the spool command should capture the output of the SQL command.
0
pavelmedAuthor Commented:
The output does go to the flat file defined by SPOOL - that is right.
But, before I added the redirection "> /dev/null 2>&1" to the line "sqlplus -s /nolog > /dev/null 2>&1<<EOF"
it also displayed the output to the shell command line.
My concern is that after I added the redirection to /dev/null, while the output is not displayed on the command line anymore, it is still being redirected to that "virtual" file, and, because it can be huge, might consume some memory.
Is it possible to suppress the display of the output on the command line instead of just redirecting it to /dev/null?

0
slightwv (䄆 Netminder) Commented:
>> because it can be huge, might consume some memory.
No worries.  /dev/null really doesn't exist.  That is what it's purpose is.  It's a null pointer that doesn't use any resources but behaves like a file.

http://en.wikipedia.org/wiki//dev/null

>> instead of just redirecting it to /dev/null?
Not that I'm aware of
0
pavelmedAuthor Commented:
Great!  Thanks a lot!
The very last question:
My script ends with this - as was suggested:
---------------------
spool off;
quit
EOF
exit
---------------------
I am not sure if ";" is needed after "spool off" - it works both ways, but I am not sure which one is more correct.  (SELECT did not work without ";")
Can you please comment?
0
slightwv (䄆 Netminder) Commented:
sqlplus is pretty forgiving.  I'm not even sure if there is a 'standard' for sqlplus.

a ';' terminates a SQL command.  the 'set' and 'spool' commands are SQL*Plus commands.

I know...  it's confusing.

I think it's just user preference.  If you go by all the sqlplus scripts floating around out there, I can't recall any using the ';' for sqlplus commands.  I don't use them but I've been around Oracle for many years and I went with what I was taught by my mentor.

The old-school folks will probably snicker at you if they seem them and the purists will say it wastes a key-stroke.

but if you like it and are comfortable with it, go for it.
0
pavelmedAuthor Commented:
Again, thanks a lot.
I am splitting the points because woolmilkporc did provide the first answer and slightwv helped a lot to implement it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.