how to get std output from sqlplus ?

[Popeanga Marian_1]

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 ?

[Brian Crabtree · Accepted solution]

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 <

[Popeanga Marian_1]

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]

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

[Popeanga Marian_1]

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]

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.

[Popeanga Marian_1]

THANKS YOU GUYS!

[Brian Crabtree]

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]

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]

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