Forum Stats

  • 3,814,954 Users
  • 2,258,934 Discussions
  • 7,892,895 Comments

Discussions

Using UTL_HTTP is Transaction Control possible

758149
758149 Member Posts: 7
edited Mar 28, 2013 7:15AM in SQL & PL/SQL
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

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,800 Red Diamond
    edited Mar 28, 2013 3:17AM
    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
    758149 Member Posts: 7
    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
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Mar 28, 2013 6:02AM
    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
    Billy Verreynne Software Engineer Member Posts: 28,800 Red Diamond
    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.
This discussion has been closed.