Home » Infrastructure » Unix » Turn off output on the terminal for sql script in Unix shell
Turn off output on the terminal for sql script in Unix shell [message #262605] Mon, 27 August 2007 19:45 Go to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

I am creating a Unix shell script with below command:

"sqlplus -s stage/stagep@itxp1 @fixture_file.sql;"
where the fixture_file.sql creates the actual flat file from a query.
What can I do to turn off the output from the screen?
I tried everything even importing terminal vt100 but still displays the output which is supposed to spool to a specific location as defined by fixture_file.sql
Any suggestions?
Thanks so much, --Alice
Re: Turn off output on the terminal for sql script in Unix shell [message #262663 is a reply to message #262605] Tue, 28 August 2007 00:56 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try to include SET TERMOUT OFF into your SQL script? Something like this:
REM test.sql

set termout off
spool test.txt
select table_name from user_tables;
spool off
exit;
This is how it runs:
M:\>sqlplus -s scott/tiger@ora10 @test

M:\>
See? No output to the screen!
Re: Turn off output on the terminal for sql script in Unix shell [message #262987 is a reply to message #262663] Tue, 28 August 2007 13:28 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Thanks a million times! It worked !!!!
All the best, --Alice
Re: Turn off output on the terminal for sql script in Unix shell [message #616678 is a reply to message #262987] Thu, 19 June 2014 10:46 Go to previous messageGo to next message
akeller728
Messages: 2
Registered: June 2014
Location: MT
Junior Member
I have a similar, but slightly different question on this topic. The Oracle administrator has created a login.sql with "set showmode on". I want to do the following in UNIX;
unix_var=`sqlplus -s user/pass << EOF
set term off echo off feed off time off showmode off
SELECT sysdate
FROM dual;`
echo unix_var

I'm expecting to get just the date, but I get:
new: showmode BOTH
end of LOGIN.SQL
05-JUN-14

Other than creating my own login.sql which won't happen in the production environment, is there a way to not have showmode display?

Tony
Re: Turn off output on the terminal for sql script in Unix shell [message #616679 is a reply to message #616678] Thu, 19 June 2014 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59275
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You cannot bypass what is in the login.sql script, so no you can't if you can't create your own login.sql in your sqlpath.

Note that the DBA should not impose a login.sql, he has the glogin.sql to put general settings which should not contain anything but variable settings or column format or things like that if your enterprise has a general policy on them.
You should be able to create as you want a login.sql for your scripts/sessions.

In short, glogin.sql is for settings valid for all using an Oracle home, login.sql is for settings valid for all scripts sharing some (SQL) paths (generally a set of scripts or batches).

Re: Turn off output on the terminal for sql script in Unix shell [message #616774 is a reply to message #616679] Fri, 20 June 2014 09:32 Go to previous messageGo to next message
akeller728
Messages: 2
Registered: June 2014
Location: MT
Junior Member
Ah... Someone showed me a way to do it:

sqlplus -s -R 3 user/pass

Won't allow access to host, but I don't need that.
Re: Turn off output on the terminal for sql script in Unix shell [message #616801 is a reply to message #616774] Fri, 20 June 2014 13:08 Go to previous message
Michel Cadot
Messages: 59275
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
but I don't need that.


Are you sure?
Isn't your command "qlplus -s stage/stagep@itxp1 @fixture_file.sql;"?
It means you have to access to "fixture_file.sql", doesn't it?

Previous Topic: How to remove carriage return character from a file in oracle
Next Topic: CHR(10) does not work from shell script
Goto Forum:
  


Current Time: Tue Sep 30 08:25:25 CDT 2014

Total time taken to generate the page: 0.11458 seconds