Solved

whenever sqlerror exit sql.sqlcode rollback except

Posted on 2012-04-03
14
2,277 Views
Last Modified: 2012-04-30
I am need to run the development scripts very frequently in my development database.

I want to rollback and exit the script if any error/s except below 2.

1. ORA-02289: sequence does not exist.
2. ORA-00942: table or view does not exist.

Could you let me know how to achieve it?

It is Oracle database version 11.1.0.7
0
Comment
Question by:YBSolutions
  • 7
  • 3
  • 3
  • +1
14 Comments
 

Author Comment

by:YBSolutions
ID: 37803411
Just FYI,

I have tested below is working fine. It will roll back and exit.

whenever sqlerror exit sql.sqlcode rollback

But I want to ignore below errors.

1. ORA-02289: sequence does not exist.
2. ORA-00942: table or view does not exist.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 37803655
You will either have to put all the 'drop' statements before the 'WHENEVER' statement on envelop each drop statement like this:
WHENEVER SQLERROR CONTINUE;

DROP TABLE myTable;

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK

Open in new window

:p
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37804120
>>But I want to ignore below errors.

If the above post does not solve the issue:  What is generating those errors?  What are you wanting to 'ignore'?

If it is DDL, that does an implicit commit and previous DML cannot be rolled back.

for example:


update some_table set col1='Y';

--this will succeed
create table bob( col1 char(1));

--this will fail
create table bob( col1 char(1));

--This does nothing
rollback;
--The update has been committed since the first DDL succeeded.
0
 
LVL 23

Expert Comment

by:David
ID: 37804224
It would be great to see the actual code.  Adding to slightwv @37804120, the errors suggest you are combining object creation with data manipulation.  For example, you might see those errors if you are querying another schema's objects for which you don't have object privileges.  Is that a possible factor here?
0
 

Author Comment

by:YBSolutions
ID: 37804615
Guys,

My script has ddl and dml both.
I know the fact told by 'slightwv' that ddl has implicit commit.

I am okay with this, How ever I want to roll back all dml (including previous to ddl).
Can you comment by keeping this in mind ?
                          OR

Can you suggest some other way/script to get it done?
0
 

Author Comment

by:YBSolutions
ID: 37804978
Hi,

Waiting for your comment/s.
0
 
LVL 23

Expert Comment

by:David
ID: 37805876
Grins, sorry, sleep time in the U.S.  I'm pleased that you follow slight's reasoning, but that begs the obvious -- you cannot roll back prior to a commit (ddl implicit).

Therefore, allow me to welcome me to your solution:  database recovery to a prior point in time / system change number:

http://www.oracle-base.com/articles/10g/Flashback10g.php
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9011.htm
http://orafaq.com/node/1847
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37806418
OK, I think I understand now.  I missed your comment when I first read the question:   except below 2

I don't think you can to this in straight sqlplus.  You can create a stored procedure say, do_ddl and trap those two specific errors.

create or replace procedure do_ddl(inSQL in varchar2)
is
	err_num number;
	err_msg varchar2(255);
	table_not_found EXCEPTION;
	sequence_not_found EXCEPTION;
	PRAGMA EXCEPTION_INIT(table_not_found, -942);
	PRAGMA EXCEPTION_INIT(sequence_not_found, -2289);
begin

	execute immediate inSQL;

	exception
		when table_not_found  then
			null;
		when sequence_not_found  then
			null;

end;
/

show errors

--generate a 942 and continue
exec do_ddl('drop table jnfgugure');
--generate a 2289 and continue
exec do_ddl('drop sequence jnfgugure');
-- generate a 904 and exit
exec do_ddl('select ralph from dual');

Open in new window

0
 
LVL 23

Expert Comment

by:David
ID: 37806485
elegant as always :)
0
 

Author Comment

by:YBSolutions
ID: 37821506
I wrote below script, any comment?
Note: You can test is by connecting scott/tiger.

set errorlogging on
-- dml start from here ----
select * from dual ;

insert into salgrade values (9, 999, 9999) ;

insert into salgrade values (10, 100, 1000) ;

insert into salgrade values (11, 1111, 11111) ;

-- select test from abc ;

drop table abc ;

---dml finish here ----

declare
i int ;

begin
select count(*) into i from sperrorlog where MESSAGE not like '%ORA-00942%' ;
select count(*) into i from sperrorlog where MESSAGE not like '%ORA-02289%' ;

if i!=0 then

rollback ;

else
commit ;

end if ;
end ;
/
0
 

Author Comment

by:YBSolutions
ID: 37821604
Now, I need to show the output at the end
if script was successful and committed  
                             OR
It encountered errors (other than ORA-00942, ORA-02289) and rolled back.

Could you help me to find out how to use
'DBMS_OUTPUT.PUT_LINE' in given condition.
Please feel free to suggest any other method if you have.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37823200
Does it work for you?  In my quick tests, the inserts are still committed.  I believe even attempted DDL does a commit.

Do a quick test to confirm:

drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values ('a');
drop table abc ;

rollback;

select * from tab1;

Open in new window



That said, if it had worked, you will need to combine both selects.  Otherwise only the second count will be in the variable.
select count(*) into i from sperrorlog where MESSAGE not like '%ORA-00942%' and MESSAGE not like '%ORA-02289%' ;
0
 

Author Comment

by:YBSolutions
ID: 37865872
Testing.
0
 

Author Closing Comment

by:YBSolutions
ID: 37910396
I wrote below query to solve it.


truncate table sperrorlog ;
set errorlogging on
----------

------ SCRIPT STARTS FROM HERE -----

...Script goes here...

----- SCRIPT ENDS HERE ------

Paste below lines after the script.

-------
declare
i int ;
begin
select count(*) into i from sperrorlog where MESSAGE not like '%ORA-00942%' and MESSAGE not like '%ORA-02289%' ;
if i!=0 then
rollback ;
else
commit ;
end if ;
end ;
/

SET LINESIZE 190
col username format a20
col timestamp format a15
col message format a100

select username, message, TO_CHAR(timestamp, 'dd-mon-yyyy hh24:mi:ss') from sperrorlog where MESSAGE not like '%ORA-00942%' and MESSAGE not like '%ORA-02289%' ;
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Single ERP VS muttiple Application or Systems 6 64
oracle rollup query 3 51
Extract the first word (before the , ) 2 39
Bash Script to Analyze Oracle Schemas 11 84
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now