From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Ramesh T <rameshparnanditech(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: oracle to postgres |
Date: | 2015-02-03 03:14:47 |
Message-ID: | 54D03D27.5070305@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2/2/15 9:13 PM, Jim Nasby wrote:
> On 1/29/15 3:02 PM, Adrian Klaver wrote:
>> On 01/29/2015 03:16 AM, Ramesh T wrote:
>>> hello,
>>> can any one help me to convert oracle to postgres script..?
>>> following code ..
>>> BEGIN
>>> EXECUTE IMMEDIATE 'DROP TABLE CONTAINER';
>>> EXCEPTION
>>> WHEN OTHERS THEN
>>> IF SQLCODE != -942 THEN
>>> RAISE;
>>> END IF;
>>> END;
>>> advance thanks,
>>
>> For hints and tips see:
>>
>> http://www.postgresql.org/docs/9.3/interactive/plpgsql-porting.html
>>
>> In this case I believe the following is what you want:
>>
>> BEGIN
>> DROP TABLE CONTAINER;
>> EXCEPTION
>> WHEN OTHERS THEN
>> IF RETURNED_SQLSTATE != -942 THEN
>> RAISE;
>> END IF;
>> END;
>
> It would be even better to instead do...
>
> EXCEPTION
> WHEN blah THEN
> NULL; -- Ignore this error
>
> See other comments about the correct error code to use. Also, note that
> this is a risky pattern; it will ignore that error no matter what object
> generated it. I much prefer to also sanity-check the actual error
> message to make sure the error is on the object we expect it to be.
Or even better yet... just use DROP IF EXISTS...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2015-02-03 03:27:46 | Re: Versioning Schema SQL ideas needed |
Previous Message | Jim Nasby | 2015-02-03 03:13:16 | Re: oracle to postgres |