Re: Exception Handling in Stored Proc's

Thanks Thomas, Yeah I didn't even notice the typo. I'll try out your suggestion tomorrow. Unfortunately, the company doesn't allow us access to newsgroups, however, they do allow us everyother kind of access to the WEB. Kind of stupid. Actually, here's another question, how do you create "dynamic" temp tables? For example, via a passed parameter. I "hoping" to use this stored proc for various reports and I'm worried about timing and contention. If user A fires off a report A and user B fires off the same report even another one then I'm basically cooked. So what I was planning to do was parse together a temp table name based on the incoming parameter. This stored proc is hell, there's 5 temp tables and approx. 40 table joins. Thanks again. Shew Thomas J. Girsch wrote in message <3641bef8.0@news.one.net>... >I'll assume that you've already noticed the obvious, being that the table >you're dropping isn't the same as the table you're trying to create (at >least, not according to your syntax). Assuming you've fixed that, here's >what I can see: > >From what I can see, you're trying to create a temp table inside a stored >procedure. Problem is, sometimes the temp table already exists. So what >you've tried to do is trap the "table already exists" error and drop the >table when that happens, as in: > >{ >ON EXCEPTION IN (-310) >DROP TABLE dah;>END EXCEPTION WITH RESUME; > >... > >SELECT stuff > INTO TEMP dah;><NEXT_STMT> >} > >The problem here is that when you do the SELECT INTO TEMP and you get >the -310 error, you run the exception, which drops the dah table. The >procedure then resumes, but it doesn't re-try the SELECT INTO TEMP; rather, >it will resume at <NEXT_STMT>. This is problematic. > >I usually attack that problem the other way. Right before doing the SELECT >INTO TEMP 'dah', I drop table 'dah', ignoring the potential -206 error >(table does not exist). Example follows: > >ON EXCEPTION IN (-206) >-- Don't do anything, just ignore the error. >END EXCEPTION WITH RESUME; > >DROP TABLE dah; >SELECT stuff > INTO TEMP dah;> >This should work for you... > >- Tom Girsch > Database Systems Manager > Arch Communications Group, Inc. > >Shew Leong wrote in message <71qnrb$cmq$1@news.interlog.com>... >>Hi Octav, >> >>I tried what you suggested and it worked the 1st time but on the 2nd try. >>Informix errored out with table is not in the database. I guess what it did >>was try to drop the table even it was error -310. >> >>I went through the manual again and tried: >> >>BEGIN >> ON EXCEPTION IN (-310) SET err_num >> IF err_num = -310 THEN >> DROP TABLE dah;>> END IF >> >> SELECT ....>> >>END >> >>What happened was it didn't recogize err_num so I DEFINEd it as INTEGER. >But >>I got the same message - table is not in the database. >> >>Question now is, is there a way to assign the error number returned from >>Informix ? Is there a system variable that the error number is stored in? >> >>Shew >> >>Octav Chiriac wrote in message <71p0m3$blp$1@news.xmission.com>... >>> >>>Let's hope I understand your problem. >>>Try this: >>> >>> >>>BEGIN >>> ON EXCEPTION IN (-310) >>> END EXCEPTION WITH RESUME; >>> DROP TABLE dah;>>>END; >>> >>>SELECT x, y, z >>> INTO TEMP dah WITH NO LOG;>>> >>> >>>Hope it helps, >>>Octav >>> >>>Shew Leong wrote: >>>>Hi, >>>> >>>>Can someone explain how ON EXCEPTION works in stored proc's. If you have >>>>sample code, it would be much appreciated. >>>> >>>>I'm trying to trap table already exist - error number -310. If the table >>>>exist >>>>then I want to drop it. >>>> >>>>Here's the code I've tried (syntax is off the top of my head): >>>> >>>>BEGIN >>>> ON EXCEPTION IN (-310) >>>> DROP TABLE tempMan >>>> END EXCEPTION WITH RESUME>>>> >>>> BEGIN >>>> SELECT x, y, z >>>> INTO TEMP dah >>>> WITH NO LOG >>>> END >>>>END >>>>END>>>> >>>>Thanks >>>> >>>> >>> >>>-- >>>Octav Chiriac Phone: (373) 2 21 20 96 >>>NetInfo S.R.L. Fax: (373) 2 21 36 59 >>>Chisinau (373) 2 24 00 83 >>>Moldova, Republic of mailto:com@netinfo-moldova.com >> >> > >