Thread: Re: oracle to postgres
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; Not sure about that state code. I could not find it here: http://www.postgresql.org/docs/9.3/interactive/errcodes-appendix.html For plpgsql error handling, see here: http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Adrian Klaver adrian.klaver@aklaver.com
On 1/29/2015 1:02 PM, Adrian Klaver wrote: > Not sure about that state code. I could not find it here: > > http://www.postgresql.org/docs/9.3/interactive/errcodes-appendix.html I believe SQLCODE = -942 is the same as ORA-00942, which is 'table or view does not exist'. I /think/ (but won't vouch) that this is roughly equivalent to SQLSTATE '42P01' -- john r pierce 37N 122W somewhere on the middle of the left coast
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
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
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE CONTAINER';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
Jim nailed it. In PostgreSQL, this is just
DROP TABLE IF EXISTS CONTAINER;
One line. No dynamic SQL, exception block, or even a block at all. It also issues a notice when the table does not exist, so if you're watching the messages, you'll know what happened.
On top of that, you don't have the weird edge cases to think about that even make using an EXCEPTION block for conditional DROP a good idea in Oracle. The reason you use an EXCEPTION block in Oracle is because 1) there's no atomic way of both checking for existence and performing the drop, and 2) because DDL always emits an implicit commit, checking for existence and then DROPping creates a race condition if you could have multiple sessions performing the DROP. (Of course, I've always questioned the wisdom of setting your system up where you could have multiple sessions doing that at the same time.) In PostgreSQL, both these problems are solved. In PG, DDL does not automatically emit a commit. DDL happens inside a transaction (assuming a transaction is initialized). This means you can perform multiple DDL operations in a single transaction and even roll them back. And for a single command that does both, you have the IF EXISTS option.
I love PostgreSQL. It makes the simple things simple, and the hard things possible and easier.