On 03/17/2015 06:50 AM, Medhavi Mahansaria wrote:
Hi,
I am writing a porting a procedure running in oracle to a PL/pgSQL
function.
I need to use commit and rollback in my function.
I have read that usage of commit and rollback is not possible in
PL/pgSQL, however savepoints can be used.
even when i use savepoints and rollback to a savepoint in the exception
block I am getting the following error
ERROR: cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
In oracle:
*CREATE OR REPLACE PROCEDURE abc (STATUS IN NUMBER) AS*
* CODE NUMBER;*
* MSG NVARCHAR2(200);*
*BEGIN*
* DELETE FROM LOG;*
* DELETE FROM TRACKER;*
* BEGIN*
* IF (STATUS < 1)*
* THEN*
* <some query>*
* INSERT INTO TRACKER SELECT 1,SYSDATE FROM DUAL;*
* COMMIT;*
The above is your problem, there cannot be a COMMIT in the function.
See here for more detail;
http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
* END IF;*
* EXCEPTION*
* WHEN OTHERS THEN*
* CODE:=SQLCODE;*
* MSG:= SQLERRM;*
* ROLLBACK;*
* INSERT INTO LOG('CODE AND MESSAGES ARE ' || CODE || ' '
|| MSG);*
* COMMIT;*
* RAISE_APPLICATION_ERROR(-20001, 'EXCEPTION WHEN EXT SYSTEM ID
KEY');*
* END;*
* BEGIN*
* IF (STATUS < 5)*
* THEN*
* <some query>*
**
* INSERT INTO TRACKER SELECT 5,SYSDATE FROM DUAL;*
* COMMIT;*
* END IF;*
* EXCEPTION*
* WHEN OTHERS THEN*
* CODE:=SQLCODE;*
* MSG:= SQLERRM;*
* ROLLBACK;*
* INSERT INTO LOG('CODE AND MESSAGES ARE ' || CODE || ' '
|| MSG);*
* COMMIT;*
* RAISE_APPLICATION_ERROR(-20001, 'EXCEPTION WHEN EXT SYSTEM ID
KEY');*
* END;*
*
............... and so on (I have blocks toll STATUS < 200 and all
follow the same concept)*
*END;*
*/*
How can i acheive the same output/flow in PL/pgSQL?
Can you please share a converted code snippet for my reference.
Thanks & Regards
Medhavi Mahansaria
Cell:- +91 9620053040
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
