I have the following script which works fine:

The errors I am trying to trap is that if the file already exists.
The only problem I have is that if the script pops an error like "File
Exists"
it will immediately go to the "Exception" section and not execute the rest
of the lines.
How do I make it execute line 2 even if there is an exception in line 1
SCRIPT

BEGIN

BEGIN

EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "POHTemp" (Like "POMas")';

EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "POLTemp" (Like "POLines")';

EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "POSTTemp" (Like "POShipTo")';

EXECUTE IMMEDIATE 'CREATE INDEX "byPONum" ON "POLTemp" ("PONum",
"LineNum")';

EXECUTE IMMEDIATE 'CREATE INDEX "byPONum" ON "POHTemp" ("PONum")';

EXECUTE IMMEDIATE 'CREATE INDEX "byPONum" ON "POSTTemp" ("PONum")';

EXCEPTION

EXECUTE IMMEDIATE 'EMPTY TABLE "POHTemp" ';

EXECUTE IMMEDIATE 'EMPTY TABLE "POLTemp" ';

EXECUTE IMMEDIATE 'EMPTY TABLE "POSTTemp" ';

END;

END

The errors I am trying to trap is that if the file already exists.
The only problem I have is that if the script pops an error like "File
Exists"
it will immediately go to the "Exception" section and not execute the rest
of the lines.
How do I make it execute line 2 even if there is an exception in line 1

"Ben" wrote:

>I have the following script which works fine:

The errors I am trying to trap is that if the file already exists.
The only problem I have is that if the script pops an error like "File
Exists"
it will immediately go to the "Exception" section and not execute the rest
of the lines.
How do I make it execute line 2 even if there is an exception in line 1<

Try this:

SCRIPT

BEGIN

BEGIN
  EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "POHTemp" (Like "POMas")';
 FINALLY
   BEGIN
     EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "POLTemp" (Like "POLines")';
     EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "POSTTemp" (Like "POShipTo")';
     EXECUTE IMMEDIATE 'CREATE INDEX "byPONum" ON "POLTemp" ("PONum",
"LineNum")';
     EXECUTE IMMEDIATE 'CREATE INDEX "byPONum" ON "POHTemp" ("PONum")';
     EXECUTE IMMEDIATE 'CREATE INDEX "byPONum" ON "POSTTemp" ("PONum")';
   EXCEPTION
     EXECUTE IMMEDIATE 'EMPTY TABLE "POHTemp" ';
     EXECUTE IMMEDIATE 'EMPTY TABLE "POLTemp" ';
     EXECUTE IMMEDIATE 'EMPTY TABLE "POSTTemp" ';
   END;
 END;
END;

If you also want to trap the exception when the other statements are executed you will have to nest the BEGIN/FINALLY statements around each Execute statement.

Of course you could always execute:

select count(*) from information.temporarytables where name='POHTemp';

to see if the temporary table is in there, thus avoiding the exception altogether.
Unfortunately there is no Information.TemporaryIndexes like there is for Information.Indexes. I doubt the indexes for temporary tables are added to Information.Indexes, but it's worth a look.

Barry

Barry,

<< I doubt the indexes for temporary tables are added to
Information.Indexes, but it's worth a look. >>

They are not.

Tim Young
Elevate Software
www.elevatesoft.com