Home » SQL & PL/SQL » SQL & PL/SQL » DROP SORT ROLLBACK inquiries  () 1 Vote
DROP SORT ROLLBACK inquiries [message #396805] Wed, 08 April 2009 01:48 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi Experts,

I know that data in a database are like a basket of balls, its irrelevant to know 'which is the first ball in the basket?'. But for our front-end, it displays everything as diplayed in the table (Select * from TABLE1). So whenever I do an insert I have to really/physically arrange it. An example I inserted TEST1 and TEST2 in TABLE1..

Initially...


Select * from TABLE1;

CODE  DESC

AA    AAA
BB    BBB
CC    CCC
WW    WWW
ZZ    ZZZ

Insert into TABLE1 (code,desc) VALUES (T1,'TEST1');
Insert into TABLE1 (code,desc) VALUES (T2,'TEST2');

Select * from TABLE1;

CODE  DESC

AA    AAA
BB    BBB
CC    CCC
WW    WWW
ZZ    ZZZ
T1    TEST1
T2    TEST2

-- I need something like this after an insert WITHOUT the ORDER BY statement --

Select * from TABLE1;

CODE  DESC

AA    AAA
BB    BBB
CC    CCC
T1    TEST1
T2    TEST2
WW    WWW
ZZ    ZZZ

-- Somewhere In my script --

Begin
.
.
.
Insert into TABLE1_TEMP Select * from TABLE1;

 Begin
  EXECUTE IMMEDIATE 'Drop Table TABLE1';
  Exception when OTHERS then NULL;
 End;

EXECUTE IMMEDIATE 'CREATE TABLE TABLE1....';

INSERT INTO TABLE1 Select * from TABLE1_TEMPl [B]ORDER BY DESC[/B];
DELETE TABLE1_TEMP;

EXCEPTION WHEN OTHERS THEN
 ROLLBACK;
.
.
.
 utl_file.put(ErrorFile,'* ROLLBACK - Message: '||SQLERRM||' '||SQLCODE);
 utl_file.put(GoodFile,'* ROLLBACK - Message: '||SQLERRM||' '||SQLCODE);
.
.

END;
-- END --

Select * from TABLE1;

CODE  DESC

AA    AAA
BB    BBB
CC    CCC
T1    TEST1
T2    TEST2
WW    WWW
ZZ    ZZZ



Any other way to do this? Confused

Another thing, a ROLLBACK undo changes in that transaction. If something happened DURING RUNTIME after my Drop TABLE statement.. Will the ROLLBACK statement 'bring back' my dropped table?

Please help. Thanks for the time.

Regards,
Wilbert

[Updated on: Wed, 08 April 2009 01:50]

Report message to a moderator

Re: DROP SORT ROLLBACK inquiries [message #396812 is a reply to message #396805] Wed, 08 April 2009 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need something like this after an insert WITHOUT the ORDER BY statement

No way.

Quote:
Will the ROLLBACK statement 'bring back' my dropped table?

No. DDL are auto-committed.

Regards
Michel

[Updated on: Wed, 08 April 2009 02:05]

Report message to a moderator

Re: DROP SORT ROLLBACK inquiries [message #396825 is a reply to message #396812] Wed, 08 April 2009 02:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is an option you can configure in the database called FLASHBACK - this will let you recover dropped tables.
Similarly, in 10g, there is the RecycleBin that lets you do the same thing.

sadly both of these need setting up in advance.

If they're not set up then a point in time Db recovery is your only real option for getting the table back.
Re: DROP SORT ROLLBACK inquiries [message #396836 is a reply to message #396805] Wed, 08 April 2009 02:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check Index Organized Tables.
I never used them myself, but from what I understand these are capable of returning an ordered resultset without using order by in your query
Re: DROP SORT ROLLBACK inquiries [message #396848 is a reply to message #396836] Wed, 08 April 2009 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Wed, 08 April 2009 09:34
Check Index Organized Tables.
I never used them myself, but from what I understand these are capable of returning an ordered resultset without using order by in your query

No.
Sometimes it will, sometimes not.

Regards
Michel

Re: DROP SORT ROLLBACK inquiries [message #396862 is a reply to message #396805] Wed, 08 April 2009 03:28 Go to previous message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Thanks for the replies and answering my inquiries. I believe our flashback drop capability is turned on. Will do more readings on this.

Again, thanks thanks thanks...

Regards,
Wilbert
Previous Topic: Pipelined function
Next Topic: SQL Non Equi Join
Goto Forum:
  


Current Time: Wed Dec 07 16:38:00 CST 2016

Total time taken to generate the page: 0.08760 seconds