Re: oracle to postgres

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.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com