Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

how to get std output from sqlplus ?

SOLVED
Go to solution

how to get std output from sqlplus ?

echo 'select * from user_tables' |sqlplus scott/tiger
it doesn't print anything on the std output
Any ideas on how can i achive this ?
10 REPLIES
Brian Crabtree
Honored Contributor
Solution

Re: how to get std output from sqlplus ?

You can do this a few different ways.

1. The way that you are doing it should send out something. I tried it for 9i on HP-UX 11.0 and it worked the way that I would expect

2. Something like the following:
echo "select * from user_tables;
exit;" > temp.sql
sqlplus scott/tiger @temp.sql

3. Something like the following:
sqlplus scott/tiger <

Re: how to get std output from sqlplus ?

Thanks for your prompt answer.
I was forgotten about sqlplus scott/tiger @script.sql

Something without creating any script file ( tee is something platform dependent )?
Brian Crabtree
Honored Contributor

Re: how to get std output from sqlplus ?

I'm not sure if it is platform dependant, but I do not think it is. What it does it send the stdout to a file, as well as continuting to send it to stdout. It is good for being able to monitor a process while logging it as well. The "spool" command in sqlplus does the same thing, although that is dependant on accessing sqlplus correctly also.

Brian

Re: how to get std output from sqlplus ?

I think i will use
sqlplus scott/tiger@sid @sql
and i need to fill this sql file with the proper command that i want to execute.
This wil work on any platform that Oracle Client sqlplus is instaled
R. Allan Hicks
Trusted Contributor

Re: how to get std output from sqlplus ?

you may find the -s option handy.

sqlplus -s scott/tiger @foo.sql >myStdOutput.txt


The -s is for silent and surpresses the boring version and other information that you may not care to have in your output file.

"Only he who attempts the absurd is capable of achieving the impossible

Re: how to get std output from sqlplus ?

THANKS YOU GUYS!
Brian Crabtree
Honored Contributor

Re: how to get std output from sqlplus ?

Make sure that if you want it to be automated, that you end the sql file with "exit;" or it will not quit from sqlplus.

Brian
Rod White
Frequent Advisor

Re: how to get std output from sqlplus ?

I guess you're trying to automate something from a shell script.

Use the EOF.

The following example is a script I use to connect to the database, spool a file then run the file. It is executed by cron.

------------------------------
#set environment variables

ORACLE_HOME=/oracle/product/8.1.7; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH

USERID=XXX
PASSWORD=YYY
SID=ZZZ

# logon to oracle

$ORACLE_HOME/bin/sqlplus $USERID/$PASSWORD@$SID << EOF

set head off
set line 200
set pagesize 1000
set arraysize 1

SELECT 'alter tablespace '||tablespace_name||' BEGIN BACKUP;'
FROM dba_tablespaces

spool /tmp/BEGIN_BACKUP.sql

/

spool off

spool /tmp/BEGIN_BACKUP.log

@/tmp/BEGIN_BACKUP.sql

spool off


EOF

#after the oracle stuff then the shell script continues

lp -dPrinter /tmp/BEGIN_BACKUP.log

# and then maybe more oracle stuff
$ORACLE_HOME/bin/sqlplus $USERID/$PASSWORD@$SID << EOF

set head off
set line 200
set pagesize 1000
set arraysize 1

SELECT 'alter tablespace '||tablespace_name||' END BACKUP;'
FROM dba_tablespaces

spool /tmp/END_BACKUP.sql

/

spool off

spool /tmp/END_BACKUP.log

@/tmp/END_BACKUP.sql

spool off

EOF

# etc etc etc
------------------------------

Notice the use of "EOF". This will cause a return to the shell script. Using this format keeps everything in one shell script and visible, not in multiple seperate .sql files. Also the .sql files produced are completely temporary.


Rod
Donny Jekels
Respected Contributor

Re: how to get std output from sqlplus ?

easiest to implemt is the following.


SQLTASK=`select (your complete select statement including ; and all`
DOIT=$(echo ${SQLTASK} | sqlplus uname/passwd@wheteve.com)

echo ${DOIT} | tee -a $OUTFILE
"Vision, is the art of seeing the invisible"
Donny Jekels
Respected Contributor

Re: how to get std output from sqlplus ?

easiest to implemt is the following.


SQLTASK=`select (your complete select statement including ; and all`
DOIT=$(echo ${SQLTASK} | sqlplus uname/passwd@wheteve.com)

echo ${DOIT} | tee -a $OUTFILE
"Vision, is the art of seeing the invisible"