Exception Handling: Oracle equivalent to VB's Resume Next?

[mike777]

Hello.
I have the following code:

[COLOR=blue]procedure someproc as
     vcSQL varchar2(1000);
     begin
          vcSQL:='drop table sometable';
          execute immediate vcSQL;
          vcSQL:='create global temporary table gttbl_table1 (col1 varchar2(10))';
          vcSQL:=vcSQL||' on commit preserve rows';
          execute immediate vcSQL;
          exception
               when others then
                    RESUME NEXT??;
     end someproc;[/color]

So the idea is when it encounters an error (any error), it just skips the line that caused the error and continues executing the code in the procedure.
Of course, the has limited use, but is applicable in this situation as-proposed.
Thank you for your help.
-Mike Kemp

 

[carp]

Mike -
If I understand your requirements correctly, I think this would do it for you:

procedure someproc as
     vcSQL varchar2(1000);
     begin
     BEGIN
          vcSQL:='drop table sometable';
          execute immediate vcSQL;
     EXCEPTION
        WHEN OTHERS THEN
          NULL;
     END;
     BEGIN
         vcSQL:='create global temporary table gttbl_table1 (col1 varchar2(10))';
          vcSQL:=vcSQL||' on commit preserve rows';
          execute immediate vcSQL;
     EXCEPTION
        WHEN OTHERS THEN
          NULL;
     END;
     end someproc;

 

[jaggiebunnet]

Of course you are not going to create a global temp table on the fly using dynamic sql, you are just using that as an example aren't you because that would just be mad!

Remember Oracle NOT IN (sybase, sqlserver) !!

 

[mike777]

Carp,
Thanks for the suggestion. Yes, that does work.
My coworker came up with what looks like the most graceful solution yet. See what you think:

procedure someproc
     begin
          some kind of command here;
          exception when others then null;
          some more commands here;
     end someproc;

I didn't test it, but he says that it works, and it looks like exactly what you have proposed, only compressed.
Thanks again.
-Mike Kemp
P.S. JaggieBunnet: Hmmmm...interesting. I MIGHT just be mad...

 

[sem]

I don't se any compression. Moreover named block is obviously larger that anonymous one. To invoke this "nested" procedure you need one more line. Thus I'd suggest you to use Carp's advice without further "enhancements"

Regards, Dima

http://seminihin.narod.ru

 

[carp]

The problem I see is that you only get one EXCEPTION section per block and it has to come AFTER all of your other logic. So
BEGIN
command
EXCEPTION
more commands
END
will not work. Your "more commands" part is actually part of the exception handler and will only execute if the exception is raised. But it WILL compile!

Elbert, CO
1155 MDT