Home » SQL & PL/SQL » SQL & PL/SQL » Why doesn't this rollback work?
Why doesn't this rollback work? [message #235742] Mon, 07 May 2007 16:57 Go to next message
Catch33
Messages: 3
Registered: May 2007
Junior Member
Hello all,

I'm a complete newbie to Oracle and am trying to demonstrate a simple rollback, yet it does not work. Code and output is below. Any help would be greatly appreciated.

DECLARE
	row_count INTEGER := 0;
	table_has_more_than_0_rows EXCEPTION;

BEGIN
	/* Create table but don't add any rows to it. Note "EXECUTE IMMEDIATE"
         must be used for DDL statements within PL/SQL code.  */
	EXECUTE IMMEDIATE 'CREATE TABLE my_table (my_int INTEGER)';

	/* Select # of rows (which will be zero) from new table and store in row_count. */
	EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_table' INTO row_count;
	DBMS_OUTPUT.PUT_LINE('Table has this many rows: '||row_count);
	COMMIT;

	DBMS_OUTPUT.PUT_LINE('');
	DBMS_OUTPUT.PUT_LINE('Inserting 3 rows into table...');
	EXECUTE IMMEDIATE 'INSERT INTO my_table VALUES(1)';
	EXECUTE IMMEDIATE 'INSERT INTO my_table VALUES(2)';
	EXECUTE IMMEDIATE 'INSERT INTO my_table VALUES(3)';
	EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_table' INTO row_count;
	DBMS_OUTPUT.PUT_LINE('Table has this many rows: '||row_count);

	/* Throw exception if # of rows is more than 0. */
	IF row_count>0 THEN
		RAISE table_has_more_than_0_rows;	
	END IF;

	EXECUTE IMMEDIATE 'DROP TABLE my_table';

EXCEPTION
	/* Handle exceptions. */
	WHEN table_has_more_than_0_rows THEN
		DBMS_OUTPUT.PUT_LINE('');
		DBMS_OUTPUT.PUT_LINE('Attempting to rollback to 0 rows...');
		ROLLBACK;
		COMMIT;
		DBMS_OUTPUT.PUT_LINE('Table has this many rows: '||row_count);
		EXECUTE IMMEDIATE 'DROP TABLE my_table';

	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Some unknown exception was caught.');
		EXECUTE IMMEDIATE 'DROP TABLE my_table';


END;
.
run;


===============
OUTPUT IS HERE:
===============
Table has this many rows: 0

Inserting 3 rows into table...
Table has this many rows: 3

Attempting to rollback to 0 rows...
Table has this many rows: 3

PL/SQL procedure successfully completed.

[Updated on: Mon, 07 May 2007 17:04]

Report message to a moderator

Re: Why doesn't this rollback work? [message #235743 is a reply to message #235742] Mon, 07 May 2007 17:08 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Do NOT use EXECUTE IMMEDIATE for DML.
IIRC, EXECUTE IMMEDIATE does implicit COMMIT prior to returing to calling session.
Re: Why doesn't this rollback work? [message #235744 is a reply to message #235743] Mon, 07 May 2007 17:30 Go to previous messageGo to next message
Catch33
Messages: 3
Registered: May 2007
Junior Member
Thanks much anacedent,

I did not know that about "EXECUTE IMMEDIATE". Originally, I was not using that for the DML statements, but I continually ran into "PL/SQL: ORA-00942: table or view does not exist" when I just had this:

INSERT INTO my_table VALUES(1);

Changing it to use the "EXECUTE IMMEDIATE" got rid of those errors. I don't know why it can't find the table I just created (unless I use "EXECUTE IMMEDIATE"). Any ideas on that?

Thanks again!
Re: Why doesn't this rollback work? [message #235745 is a reply to message #235742] Mon, 07 May 2007 17:39 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You can NOT do DDL from within PL/SQL (except by using EXECUTE IMMEDIATE).
Again it is poor, bad, & lousy design/implementation to do DLL from within PL/SQL.
You really, really should NOT be creating DB objects "on the fly".
You should have static, source code controlled & QA'ed SQL that creates all DB objects.
Re: Why doesn't this rollback work? [message #235747 is a reply to message #235745] Mon, 07 May 2007 18:19 Go to previous messageGo to next message
Catch33
Messages: 3
Registered: May 2007
Junior Member
Right on - thanks. I was creating the table on the fly because this is just a test script. And I did notice earlier that if I didn't drop the table, the code would run fine the second time through. It just seems that when you create a table in PL/SQL using "EXECUTE IMMEDIATE", the table not really visible unless you continue using "EXECUTE IMMEDIATE". For my test purposes, I'll just create separate scripts to create and drop the table.

(EDITED)
Jeez - I'm such a yo-yo. I forgot to select COUNT(*) into row-count prior to my last output to the screen. That was probably the only problem to begin with. But the points about bad design are well-taken! Thanks for the advice.

[Updated on: Mon, 07 May 2007 18:35]

Report message to a moderator

Re: Why doesn't this rollback work? [message #235748 is a reply to message #235742] Mon, 07 May 2007 18:34 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Oracle works very, very hard to present each user with a READ CONSISTENT view of the data base at the time you start any transaction/session.
If the table did not exist when you initiated your session, then Oracle won't "see" the table for your session.
This the same behvior for DML.
Your session won't see new or updated record which change after your session starts.
Re: Why doesn't this rollback work? [message #235778 is a reply to message #235743] Tue, 08 May 2007 00:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
anacedent wrote on Tue, 08 May 2007 00:08

IIRC, EXECUTE IMMEDIATE does implicit COMMIT prior to returing to calling session.

Nope, not recalled correctly Wink
SQL> create table faq (id number);

Table created.

SQL> insert into faq values (1);

1 row created.

SQL> begin
  2    execute immediate('insert into faq values(2)');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from faq;

        ID
----------
         1
         2

The rest is true though!
Re: Why doesn't this rollback work? [message #235781 is a reply to message #235748] Tue, 08 May 2007 00:32 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
First of all, I don’t understand your question clearly. I'm presuming that you are getting 3 rows even after rollback. Correct me if I’m wrong.

As far as this code concerns it behaving absolutely right. In the Exception section you rollback the transaction, hence all the data would go out. But after that you are just displaying the local variable that you had assigned after the insertion. So obviously your row_count will have 3 and when you print it in the exception you will get 3 only not 0.

Thanks,
Thangam
Re: Why doesn't this rollback work? [message #235791 is a reply to message #235781] Tue, 08 May 2007 00:54 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good eye... but the OP stated few posts above:
Quote:
I forgot to select COUNT(*) into row-count prior to my last output to the screen

Regards
Michel
Previous Topic: ORA-6550 + PLS-00302 (merged)
Next Topic: Exception Handling
Goto Forum:
  


Current Time: Sat Dec 03 12:14:53 CST 2016

Total time taken to generate the page: 0.05702 seconds