Hi,
I have a requirement where i have been given a stored procedure (Transform) and i have been told that i need to commit the procedure once it finishes successfully.
Is there any way in Stored proc stage or any other way i can achieve this?
How to commit after procedure run
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
How to commit after procedure run
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
At end of the job.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
At the end of the job when the connections are closed any open transaction will be committed automatically.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Instead of calling stored procedure i am running PL/SQL block in Oracle connector stage and comming at the end.
Below is the code,how can i abort a job in case RetVal <>0?
Below is the code,how can i abort a job in case RetVal <>0?
Code: Select all
DECLARE
RetVal NUMBER;
P_PAYLOAD str_payloads.payload%type;
P_PAYLOADHASH VARCHAR2(200);
P_TIMESTAMP VARCHAR2(32767);
P_PAYLOADSIZE NUMBER;
P_ERRORMSG VARCHAR2(32767);
BEGIN
P_PAYLOAD := EMPTY_BLOB();
P_PAYLOADHASH := NULL;
P_TIMESTAMP := TO_CHAR(SYSTIMESTAMP,'yyyy/mm/dd hh24:mi:ss');
P_PAYLOADSIZE := NULL;
P_ERRORMSG := NULL;
RetVal := AITBROKER.STR_TRANSMISSION.USF_LOG_PAYLOAD_V2 ( '#PYLDUUID#', P_PAYLOAD, P_PAYLOADHASH, P_TIMESTAMP, P_PAYLOADSIZE, P_ERRORMSG );
IF(RetVal <> 0) THEN
DBMS_OUTPUT.PUT_LINE('P_ERRORMSG: ' || P_ERRORMSG);
ROLLBACK;
ELSE
DBMS_OUTPUT.PUT_LINE('Success!');
COMMIT;
END IF;
END;
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/