topic Re: how to get std output from sqlplus ? in Operating System - HP-UX

how to get std output from sqlplus ?

Popeanga Marian_1 — Thu, 03 Apr 2003 07:07:42 GMT

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 ?

Re: how to get std output from sqlplus ?

Brian Crabtree — Thu, 03 Apr 2003 07:20:09 GMT

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 ?

Popeanga Marian_1 — Thu, 03 Apr 2003 07:42:00 GMT

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 )?

Re: how to get std output from sqlplus ?

Brian Crabtree — Thu, 03 Apr 2003 08:32:32 GMT

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 ?

Popeanga Marian_1 — Thu, 03 Apr 2003 09:01:25 GMT

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

Re: how to get std output from sqlplus ?

R. Allan Hicks — Fri, 04 Apr 2003 13:36:28 GMT

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.

Re: how to get std output from sqlplus ?

Popeanga Marian_1 — Sat, 05 Apr 2003 07:51:57 GMT

THANKS YOU GUYS!

Re: how to get std output from sqlplus ?

Brian Crabtree — Sat, 05 Apr 2003 08:41:14 GMT

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

Re: how to get std output from sqlplus ?

Rod White — Tue, 15 Apr 2003 07:47:16 GMT

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

Re: how to get std output from sqlplus ?

Donny Jekels — Wed, 16 Apr 2003 15:41:57 GMT

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

Re: how to get std output from sqlplus ?

Donny Jekels — Wed, 16 Apr 2003 15:42:10 GMT

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