Home » SQL & PL/SQL » SQL & PL/SQL » Need help with Procedure Error Handling (Oracle 10.0.2)
Need help with Procedure Error Handling [message #436582] Thu, 24 December 2009 05:22 Go to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Hi everyone

I have a PL/SQL procedure which drops a table and creates a new one. The test case is below.


CREATE OR REPLACE PROCEDURE proc1 as

v_tab_drop varchar2(2000);
v_tab_create varchar2(2000);

BEGIN

v_tab_drop := 'drop table table1';
execute immediate v_tab_drop;

v_tab_create := 'create table table1 as select * from table2';
execute immediate v_tab_create;

END;



The problem is that, if table1 does not exist already, the execute immediate v_tab_drop fails and the procedure do not execute further. Is there any way to handle this exception so that procedure continues to execute?

Thanks.

Hammad
Re: Need help with Procedure Error Handling [message #436583 is a reply to message #436582] Thu, 24 December 2009 05:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Don't drop and re-create tables on the fly. It will bring you nothing but endless headaches.

What is it that you are actually trying to achieve?
Re: Need help with Procedure Error Handling [message #436584 is a reply to message #436582] Thu, 24 December 2009 05:32 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
It is very rare that creating and dropping tables on the fly is a good idea. What exactly is it that you are trying to do?

[Edit: Too slow Smile]

[Updated on: Thu, 24 December 2009 05:33]

Report message to a moderator

Re: Need help with Procedure Error Handling [message #436585 is a reply to message #436582] Thu, 24 December 2009 05:33 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi,

This must be posted before. Find out.
Use different BEGIN EXCEPTION blocks for drop and create resp.

Regds
Soni
Re: Need help with Procedure Error Handling [message #436587 is a reply to message #436582] Thu, 24 December 2009 05:47 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Well my boss requires this so no choice Sad

We have a flat table which is made on daily basis. The old one is renamed and then dropped in the original procedure. But I don't want to depend on the fact that the table must exist before the procedure is executed. I think I have to include some RAISE EXCEPTION to handle this. But I'm not sure what type of EXCEPTION should be handled.

Any help would be appreciated.

Thanks.
Re: Need help with Procedure Error Handling [message #436588 is a reply to message #436582] Thu, 24 December 2009 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why not just truncate the table and then insert into it?
Dropping the table does not appear to be a requirement here.
Re: Need help with Procedure Error Handling [message #436595 is a reply to message #436588] Thu, 24 December 2009 06:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The end-result of a truncated table will be exactly the same end-result as if you rename, drop, then re-create the table.

Just without all the endless trouble involved.
Re: Need help with Procedure Error Handling [message #436607 is a reply to message #436587] Thu, 24 December 2009 07:37 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
hammad83 wrote on Thu, 24 December 2009 06:47
Well my boss requires this so no choice Sad


Sounds like another genius boss. Why don't you start telling your boss how to do his job and see what his reaction is.
Re: Need help with Procedure Error Handling [message #436626 is a reply to message #436587] Thu, 24 December 2009 10:30 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
It's also your job to provide the technical sound advice to your boss, whom may not be technically savy. If you provide the pros and cons to your boss about the situation...then I'm sure he will appreciate that better.
Re: Need help with Procedure Error Handling [message #436628 is a reply to message #436626] Thu, 24 December 2009 11:12 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Well guys Thanks for the advice but if someone can post the solution to my question i.e. how do I handle the exception if table does not exist, that would be really helpful and would so help me learn something Smile
Re: Need help with Procedure Error Handling [message #436630 is a reply to message #436628] Thu, 24 December 2009 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
We tend to not give loaded weapons to those not qualified to handle them.

If you insist on such fool hearty behaviour, then SEARCH for EXCEPTION HANDLER & proceed to Read The Fine Manual.
Re: Need help with Procedure Error Handling [message #436633 is a reply to message #436630] Thu, 24 December 2009 16:16 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I wouldn't commonly do this but have made use of it in DDL scripts to keep junk errors of of my DDL logs. Example...
exec drop_tab_if_exists('EMP');
create table EMP (....);

exec drop_tab_if_exists('DEPT');
create table DEPT (....);


SQL> set serveroutput on
SQL> CREATE OR REPLACE PROCEDURE proc1 as
  2     v_tab_drop varchar2(2000);
  3     v_tab_create varchar2(2000);
  4     --ORA-00942: table or view does not exist
  5     no_such_table   EXCEPTION;
  6     PRAGMA EXCEPTION_INIT (no_such_table,  -00942);
  7  BEGIN
  8    begin
  9      v_tab_drop := 'drop table table1';
 10      execute immediate v_tab_drop;
 11    exception
 12      --when no_such_table then null;
 13      when no_such_table then dbms_output.put_line('INFO: table1 wasnt present');
 14    end;
 15    --v_tab_create := 'create table table1 as select * from table2';
 16    v_tab_create := 'create table table1 as select * from dual';
 17    execute immediate v_tab_create;
 18  END;
 19  /

Procedure created.

SQL>
SQL> exec proc1;
INFO: table1 wasnt present

PL/SQL procedure successfully completed.

SQL> exec proc1;

PL/SQL procedure successfully completed.

SQL>



Re: Need help with Procedure Error Handling [message #436832 is a reply to message #436582] Mon, 28 December 2009 13:17 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Show your boss this thread.

No disrepect intended to your boss, but ORAFaq is composed of experts in the Oracle arena. If your boss has found a great reason for doing drop/create on the fly, we would all like to hear about it because we want to know what it is.

Alternatively you boss could explain to the squints here that the best technical solution is not always the best solution for the business. This may be a situation of "this is how it works now and changing it would be more exspensive than living with it".

That said, let me re-iterate the sentiments already given in this thread:

Quote:
Droping and Creating tables on the fly is always bad. It leads to big headaches and troubles. Most of us here have seen at least one system that did this and the story was always disaster. Problems can manifest at any time, but most especially they show up either in late stages of development (think performance issues), or a year down the line (again performance issues come to mind for me). Needless to say these are the most expensive stages for problems to show up in. Developers think they are so slick with a drop/create on the fly solution to their problem but they do this without any real understanding of what drop/create really means. Most developers do not thing of concurrency issues, scalability issues, locking strategies, and the query optimization process in Oracle when they write code.

So, again please show this thread to your boss and ask him to clue us to why his use of drop/create is so great because if he has somehow found the holey grail of drop/create on the fly, all of us want to use it too.

Kevin
Previous Topic: best practices writing a query
Next Topic: Result based on conditional where clause
Goto Forum:
  


Current Time: Sun Dec 11 02:19:28 CST 2016

Total time taken to generate the page: 0.17243 seconds