OraFAQ Forum: Client Tools » sqlplus, how to send error messages to stdout when set feedback is off and spooling to file

Home » Other » Client Tools » sqlplus, how to send error messages to stdout when set feedback is off and spooling to file

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

sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257669]

Thu, 09 August 2007 00:11

tberghuis
Messages: 6
Registered: August 2007

Junior Member

My sql script basically dumps a table to a csv file.

whenever sqlerror exit 1
set termout off
set pagesize 0
set heading off
set feedback off
spool a_file.csv
select col1||','||col2 from table_name;

quit

My problem is that if the DB I log into doesn't contain table 'table_name', the error is not displayed on screen and is written into a_file.csv.

Any suggestions would be greatly appreciated.

Regards
Tom

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257673 is a reply to message #257669]

Thu, 09 August 2007 00:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

How is it that you are trying to access a table that does NOT exist?

Doctor, it hurts when I poke myself in the eye.
How can I stop the pain?
Answer: Don't poke yourself in the eye!

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257679 is a reply to message #257669]

Thu, 09 August 2007 00:22

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

You "set termout off", that is you said don't display anything.
You spool the result, that is you said put all what happen into file.
So behaviour is expected one.

Regards
Michel

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257691 is a reply to message #257673]

Thu, 09 August 2007 00:42

tberghuis
Messages: 6
Registered: August 2007

Junior Member

The user logs in with the wrong userid that does not contain the table 'table_name'

Thanks

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257693 is a reply to message #257669]

Thu, 09 August 2007 00:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>The user logs in with the wrong userid that does not contain the table 'table_name'
Maybe you should not pull the trigger until you know it will succeed.

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257695 is a reply to message #257679]

Thu, 09 August 2007 00:49

tberghuis
Messages: 6
Registered: August 2007

Junior Member

I know that the behavior is the expected behavior. I am asking how would I be able to not display what is being spooled, but if an error was to occur it would be displayed.

If only sqlplus used stderr it would be easy.

Tom

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257696 is a reply to message #257693]

Thu, 09 August 2007 00:50

tberghuis
Messages: 6
Registered: August 2007

Junior Member

How can I check if table_name exists without anything printed to screen except when there is an error?

Tom

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257700 is a reply to message #257696]

Thu, 09 August 2007 00:58

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You could write a PL/SQL procedure (instead of a simple SQL you use now).

In there, test for table existence. If it exists, spool result into the file - but now you'll have to use UTL_FILE to do that. If it doesn't exist, display warning message (probably not via DBMS_OUTPUT, but raise an exception).

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257704 is a reply to message #257700]

Thu, 09 August 2007 01:03

tberghuis
Messages: 6
Registered: August 2007

Junior Member

I knew it would resort to having to write PL/SQL.
Thanks for the help guys.

Tom

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257708 is a reply to message #257700]

Thu, 09 August 2007 01:13

tberghuis
Messages: 6
Registered: August 2007

Junior Member

I just did a google on UTL_FILE and have found...

The Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is on the computer where the client is running.

DOH!

Report message to a moderator

Re: sqlplus, how to send error messages to stdout when set feedback is off and spooling to file [message #257762 is a reply to message #257695]

Thu, 09 August 2007 02:27

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Try this

whenever sqlerror exit 1
Def flag=1
Col f noprint new_value flag
select '*** table does not exist', 0 f 
from dual 
where not exists (select 1 from user_tables where table_name='TABLE_NAME');
set termout off
set pagesize 0
set heading off
set feedback off
col c noprint
select 1/&flag c from dual;
spool a_file.csv
select col1||','||col2 from table_name; 
quit

Regards
Michel

Report message to a moderator

Previous Topic:	SQL +
Next Topic:	SQLPLUS Report with multiple queries

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 05:26:45 CDT 2024