Home » SQL & PL/SQL » Client Tools » Suppress Orcale script message dynamically (Oracle 10 g)
Suppress Orcale script message dynamically [message #541275] Mon, 30 January 2012 04:35 Go to next message
arup_kc
Messages: 9
Registered: January 2012
Location: India
Junior Member
Hi,

I wiuld like to suppress oracle messages conditionally. I am running oracle view creation scripts from command line, After successful view creation, it is showing me "view created" message. To suppress this, I have created following code:
set serveroutput on format wrapped;
SET TERMOUT OFF
@@test1.sql
SET TERMOUT ON
DECLARE I INTEGER:= 0;
BEGIN
SELECT COUNT(1) INTO I FROM ALL_VIEWS WHERE VIEW_NAME =UPPER('test831') AND OWNER ='MDBADMIN';
IF (I = 1) THEN
DBMS_OUTPUT.put_line('View test831 created successfully');
ELSE
DBMS_OUTPUT.put_line('View test831 NOT created');
END IF;
END;
but still it is giving me "PL/SQL procedure successfully created" message along with "'View test831 created successfully".
I need to suppress "PL/SQL procedure successfully created".
Any help is much appreciated.
Re: Suppress Orcale script message dynamically [message #541278 is a reply to message #541275] Mon, 30 January 2012 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59162
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it is showing me "view created" message. To suppress this,


Just use "set feedback off".

Quote:
I need to suppress "PL/SQL procedure successfully created".


Same thing.

The whole PL/SQL block is non-sense: counting all views to know if one exist and then displaying a message that you ask Oracle to remove!

Regards
Michel
Re: Suppress Orcale script message dynamically [message #541298 is a reply to message #541278] Mon, 30 January 2012 05:44 Go to previous messageGo to next message
arup_kc
Messages: 9
Registered: January 2012
Location: India
Junior Member
Hi Micchel,
Thanks for your reply and it is the exact thing I needed.

However, probably you have not got the sense of that PL/SQL block
That part is included to check whether that view has been created successfully(might be there are better ways to do so) as per our coding standard.
Thanks you once more.
Reagrds,
Arup
Re: Suppress Orcale script message dynamically [message #541301 is a reply to message #541298] Mon, 30 January 2012 05:52 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
arup_kc wrote on Mon, 30 January 2012 11:44

However, probably you have not got the sense of that PL/SQL block

Pretty sure he has got the sense of it.

arup_kc wrote on Mon, 30 January 2012 11:44

That part is included to check whether that view has been created successfully(might be there are better ways to do so)

See if you got any errors. If not it created sucessfully. Querying the data dictionary is a waste of time.

arup_kc wrote on Mon, 30 January 2012 11:44

as per our coding standard.

Your coding standards state that you need to write code like that for every object created? Really?
Re: Suppress Orcale script message dynamically [message #541311 is a reply to message #541301] Mon, 30 January 2012 06:17 Go to previous messageGo to next message
arup_kc
Messages: 9
Registered: January 2012
Location: India
Junior Member
Hi Cookiemaster,
Thanks for your answer.
arup_kc wrote on Mon, 30 January 2012 11:44

That part is included to check whether that view has been created successfully(might be there are better ways to do so)

See if you got any errors. If not it created sucessfully. Querying the data dictionary is a waste of time.
If there is any error in the view creation code, then I am getting error.Otherwise how could I know that there is an error at view creation level? If the compiler is throwing any return code to the calling script?
arup_kc wrote on Mon, 30 January 2012 11:44

as per our coding standard.

Your coding standards state that you need to write code like that for every object created? Really?
[/quote]
I will be obliged if you can tell me any better way.
Regards,
Arup
Re: Suppress Orcale script message dynamically [message #541316 is a reply to message #541311] Mon, 30 January 2012 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59162
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If there is any error in the view creation code, then I am getting error.Otherwise how could I know that there is an error at view creation level?

By not suppressiong the output SQL*Plus gives you.
If you "set termout off" then you can't see the error (on screen).
If you "set feedback on" then you don't see the result when it succeeds as well as it fails.
If you "set feedback off" then you see the result only if it fails:
SQL> set feedback on
SQL> create or replace view v as select * from t;

View created.

SQL> create or replace view v as select * from x;
create or replace view v as select * from x
                                          *
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here


SQL> set feedback off
SQL> create or replace view v as select * from t;
SQL> create or replace view v as select * from x;
create or replace view v as select * from x
                                          *
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here

Regards
Michel
Re: Suppress Orcale script message dynamically [message #541320 is a reply to message #541316] Mon, 30 January 2012 06:56 Go to previous messageGo to next message
arup_kc
Messages: 9
Registered: January 2012
Location: India
Junior Member
Hi MIchel,
Then what I need to do?
Regards,
Arup
Re: Suppress Orcale script message dynamically [message #541323 is a reply to message #541320] Mon, 30 January 2012 07:06 Go to previous message
Michel Cadot
Messages: 59162
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want.
I posted some of the things you can do.
Explain in details what you want with an example in the same way I showed.

Regards
Michel
Previous Topic: REF CURSOR to print output from given input
Next Topic: spooling data
Goto Forum:
  


Current Time: Mon Sep 22 09:16:00 CDT 2014

Total time taken to generate the page: 0.13036 seconds