HTTP Post from Oracle PL/SQL

I recently needed to execute a HTTP post from an ApEx application to another web page running as some sort of a service. For added security the web page service only accepted POST requests and one of the parameters passed is a secret password (not encrypted but secure enough for an Intranet application).

Anyway the requirement was to post the request using PL/SQL to the web service, and receive the response. I visited the oracle page on UTL_HTTP for a bit of reference and came up with the following code:


set serveroutput on;
exec dbms_output.enable(1000000000);
set escape '\'

DECLARE
  req   UTL_HTTP.REQ;
  resp  UTL_HTTP.RESP;
  value VARCHAR2(1024);  -- URL to post to
  v_url VARCHAR2(200) := 'http://T97040476TA9000/core_dmt/withdraw_job.php';
  -- Post Parameters
  v_param VARCHAR2(500) := 'pwd=password123\&core_id=12223\&type=PK\&reason=Test reason';
  v_param_length NUMBER := length(v_param);
BEGIN
  -- Set up proxy servers if required
  --  UTL_HTTP.SET_PROXY('proxy.my-company.com', 'corp.my-company.com');
  req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'POST');
  --  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
  UTL_HTTP.SET_HEADER (r      =>  req,
                       name   =>  'Content-Type',
                       value  =>  'application/x-www-form-urlencoded');
  UTL_HTTP.SET_HEADER (r      =>   req,
                       name   =>   'Content-Length',
                       value  =>   v_param_length);
  UTL_HTTP.WRITE_TEXT (r      =>   req,
                       data   =>   v_param);  resp := UTL_HTTP.GET_RESPONSE(req);
  LOOP
    UTL_HTTP.READ_LINE(resp, value, TRUE);
    DBMS_OUTPUT.PUT_LINE(value);
  END LOOP;
  UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(resp);
END;
/

The only thing you need to change to make the code work for you are the v_url and the v_param values to reflect the url of your service and the parameters it expects.

Happy Coding!

ShareShare on FacebookTweet about this on TwitterShare on Google+Share on LinkedInEmail this to someone

You may also like...

5 Responses

  1. Likwid says:

    How did you handle this in your PHP? I am trying this but it doesn’t seem to want to work. Could you post some PHP code that handled the response, or are you just handling them as normal PHP post variables?

  2. admin says:

    Hi Likwid,
    You just handle them as normal PHP post variables. So for the example post from Oracle above, your PHP will contain the following:

    < ?php // ... // Get post parameters $pwd = $_POST["pwd"]; $core_id = $_POST["core_id"]; $type = $_POST["type"]; $reason = $_POST["reason"]; ?>

    Cheers,
    Ariel

  3. Sarah says:

    Hello,

    I really hope you get this since its been 6 years from the posted date. What I am wondering is, Can I wrap this request to a button? Meaning only when the user clicks on a button will the http request be sent? I am using pl/sql to create a web application.

    Thank you in advance,
    Sarah

  4. ryelpango says:

    Hi Sarah,
    I’m pretty sure it’s possible to invoke the above code via a button submit. I’m guessing you would need to wrap up the code above in a procedure and specify it as the “action” in a html form.

    Cheers,
    Ariel

  5. David says:

    Often, I read “how call web service with pl/sql”, but never “how extract parameters…”.
    I created a restful web service with pl/sql and embededd gateway apex, and i need to extract parameter written via UTL_HTTP.WRITE_TEXT….
    Can you suggest me ?

Leave a Reply

Your email address will not be published. Required fields are marked *