Using UTL_HTTP is Transaction Control possible

758149

Hi All,

I am new to this UTL_HTTP too. I am trying to access a Webservice from my PL/SQL and the webservice intern calls a procedure from a different system.
Can this transaction controlled from my PL/SQL Block?

I mean, If I issue a Commit in my PL/SQL code, the COMMIT should happen in the 2nd system too.
Same way the rollback.

Is it possible? If so, How it works? any example you got.

Thanks a lot for your help guys!!

Best Regards,
Sivakumar M

Billy Verreynne

How do you start a transaction via a web service? What does the SOAP envelope contain?

How do you commit that transaction via a web service? What does the SOAP envelope contain?

How do you rollback that transaction via a web service? What does the SOAP envelope contain?

HTTP is a stateless protocol. How do you commit that transaction via a web service? Transactions require state. How is state addressed?

758149

Dear Billy,

it goes like this....

PL SQL Procedure

[

L_CUR_POS_V := '100';
UTL_HTTP.SET_DETAILED_EXCP_SUPPORT (TRUE);
L_CUR_POS_V := '101';
L_HTTP_REQ := UTL_HTTP.BEGIN_REQUEST (L_HTTP_URL_V, 'POST', 'HTTP/1.1');
L_CUR_POS_V := '102';
--UTL_HTTP.SET_AUTHENTICATION (L_HTTP_REQ, L_HTTP_USERNAME_V, L_HTTP_PASSWORD_V);
L_CUR_POS_V := '103';
UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT (L_HTTP_REQ, FALSE);
L_CUR_POS_V := '104';
UTL_HTTP.SET_HEADER (L_HTTP_REQ, 'Content-Type', 'text/xml');
L_CUR_POS_V := '105';
UTL_HTTP.SET_HEADER (L_HTTP_REQ, 'charset', 'UTF-8');
L_CUR_POS_V := '106';
UTL_HTTP.SET_HEADER (L_HTTP_REQ, 'Content-Length', LENGTH (IP_INPUTDATA_V));
L_CUR_POS_V := '107';
--WRITES SOME TEXT DATA IN THE HTTP REQUEST BODY
UTL_HTTP.WRITE_TEXT (L_HTTP_REQ, IP_INPUTDATA_V);
L_CUR_POS_V := '108';
-- GET HTTP RESPONSE
L_HTTP_RESP := UTL_HTTP.GET_RESPONSE (L_HTTP_REQ);
L_CUR_POS_V := '109';
-- GET THE RESPONSE TEXT VALUE
UTL_HTTP.READ_TEXT (L_HTTP_RESP, L_OUTPUTDATA_V);
L_CUR_POS_V := '110';

IF (L_HTTP_RESP.STATUS_CODE <> 200)
THEN
L_CUR_POS_V := '111';
--APP_LOGGER.ERROR ('HTTP REQUEST CALL FAILED;STATUS CODE~' || L_HTTP_RESP.STATUS_CODE);
RAISE L_RAISE_E;
END IF;

L_CUR_POS_V := '112';
UTL_HTTP.END_RESPONSE (L_HTTP_RESP);
RETURN L_OUTPUTDATA_V;

IF Success --> I want to Commit.

EXCEPTION
-- I want o Roll back here

]

Thanks...

John Stegeman

As Billy said, you need to tell us how you commit or rollback the transaction via the web service - how do you expect us to know how your web services have implemented commit/rollback (or even if they have at all)

Oh, and PLEASE tell me that isn't really how you comment your code! Those comments add no value, just like this one adds no value:

-- add 1 to A
a := a + 1;

Billy Verreynne

Whether a web service commits or rollback, is a function of the web service. Either your SOAP call to the web service is accepted and returns a valid response. Or not.

So the question should be: "+do I commit or rollback after that web service call?+". Not "+how do I get the web service to commit/rollback with my commit/rollback?+".

The basic structure of your PL/SQL procedure should be something along the following lines:

procedure MyCallToWebService( .. )
  ..
begin
  --// implicit start of your database transaction (use a save point if needed)
  prepare SOAP envelope

  make HTTP post call to web server

  --// look for technical errors in the communication with web service
  if HTTP response code is not 200 (HTTP_OK) then a raise a web server error

  parse the HTTP response as XML

  --// look business errors (web service saying it cannot accept or process
  --// the values you supplied)
  if xml contains an error status then raise web service error

  --// XML response from web service says web service accepted, processed
  --// and committed its transaction
  record successful processing
  commit

exception when OTHERS then  --// can decide to separately deal with exceptions 
  record failed processing (via an autonomous transaction)
  rollback
  raise
end

This is not, and cannot be, a 2 phase transaction - due to the stateless nature of the architecture used. There is thus a risk.

Your code can send a successful SOAP call to the web service. It commits its transaction and responds with a success status.

Before your code can process the successful response and do your commit, the server/network/database crashes. You never mark your transaction as committed. This transaction is rolled back when the database starts. So according to your data, the transaction never happened.

Is this is an issue or risk, then additional artificial transaction status end points need to be included in your code, in order to provide data on transactions that are in-doubt.