Home » SQL & PL/SQL » Client Tools » sqlplus, how to send error messages to stdout when set feedback is off and spooling to file
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 Go to next message
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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!
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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.
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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).
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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!
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 Go to previous message
Michel Cadot
Messages: 64103
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
Previous Topic: SQL +
Next Topic: SQLPLUS Report with multiple queries
Goto Forum:
  


Current Time: Sun Dec 04 18:40:34 CST 2016

Total time taken to generate the page: 0.11581 seconds