Categories
Oracle PL/SQL

HTTP Post from Oracle PL/SQL


Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

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!

6 replies on “HTTP Post from Oracle PL/SQL”

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?

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:

Cheers,
Ariel

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

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

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 *