OraFAQ Forum: Unix » Turn off output on the terminal for sql script in Unix shell

Home » Infrastructure » Unix » Turn off output on the terminal for sql script in Unix shell

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Turn off output on the terminal for sql script in Unix shell [message #262605]

Mon, 27 August 2007 19:45

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

Report message to a moderator

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

Littlefoot
Messages: 21823
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!

Report message to a moderator

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

aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles

Junior Member

Thanks a million times! It worked !!!!
All the best, --Alice

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 16:21:26 CST 2024