Hi,
How can I do http post in oracle 9i??

Thanks

can i receive posted xml file using utll_http package?

Hi Tom,

I'm using Orcle 9.2.0.4. I have couple questions regarding UTL_HTTP.

1. How is the perfromance of using this package in the stored procedure. Are there any overhead in the database by using this package?
2. Are there any pros and cons that you can share?
3. I'm looking a way that you can just call the web service without waiting a response, is that possible?

Here is the brief background of what we are trying to accomplish. We found out that in the Data Access Layer Component (in the App server) is expensive doing connection in the database every minute. With this issue at hand, we are trying to put the process in the database to initiate the process if there are any transactions available for processing. The purpose is to initiate a web service call from db stored procedure without getting any responses back. The web service will start another thread to process the request.

Thanks in advance.

Joe

I am trying to determine if the changes in the way we doing our process will benefit us the most.

Currently, all the process is initiated by the APP Server Components (.net). It schedules every minute to connect to the db and call the stored procedure to grab the dataset for creating the report. With this current setup, we are finding some issues.

To eliminate some of the issues and we thought it will benefit us in using the UTL_HTTP package.

We are trying to have the db initiate the process when there are transactions available. The idea is for the db call a web service (using the UTL_HTTP with ‘POST’ method) which will start a thread to call a (.net) component. The component will process the request, create the report and update the status in status in the db. The other idea (since it’s available in .net), if we can just call the web service without waiting for any response since the last process will eventually go back to the db to update the status.

We connect use the AQ process since the process is not strictly db.

Here is the prototype that I’m using for testing.

DECLARE
TYPE response IS RECORD (Doc XMLTYPE);

env clob;
http_req utl_http.req;
http_resp utl_http.resp;
Resp Response;
fault_node XMLTYPE;
fault_code VARCHAR2(256);
fault_string VARCHAR2(32767);

BEGIN

env := '<SOAP-ENV:Envelope
xmlns:SOAP-ENV="</code> http://schemas.xmlsoap.org/soap/envelope/"

                    xmlns:xsi="

http://www.w3.org/1999/XMLSchema-instance"

                    xmlns:xsd="

http://www.w3.org/1999/XMLSchema" >

            <SOAP-ENV:Body>
                  <TestDatasetWS xmlns="ReportServices">
                    <Input>Test</Input>
                  </TestDatasetWS>
            </SOAP-ENV:Body>
          </SOAP-ENV:Envelope>';

  utl_http.set_proxy('

http://10.88.132.65',

NULL);
  utl_http.set_persistent_conn_support(FALSE);

  http_req := utl_http.begin_request('

http://10.88.132.65/ReportODWS/ReportWS.asmx',

'POST','HTTP/1.1');

  utl_http.set_header(http_req, 'Content-Type', 'text/xml');
  utl_http.set_header(http_req, 'Content-Length', length(env));
  utl_http.set_header(http_req, 'SOAPAction', 'ReportServices/TestDatasetWS');
  utl_http.write_text(http_req, env);

  http_resp := utl_http.get_response(http_req);
  utl_http.read_text(http_resp, env);
  utl_http.end_response(http_resp);
  resp.doc := xmltype.createxml(env);
  resp.doc := resp.doc.extract('/soap:Envelope/soap:Body/child::node()',
                               'xmlns:soap="

http://schemas.xmlsoap.org/soap/envelope/"'

;
  fault_node := resp.doc.EXTRACT('/soap:Fault',
                                 'xmlns:soap="

http://schemas.xmlsoap.org/soap/envelope/'

;

  IF (fault_node IS NOT NULL) THEN
    fault_code := fault_node.EXTRACT('/soap:Fault/faultcode/child::text()',
                                     'xmlns:soap="

http://schemas.xmlsoap.org/soap/envelope/'

.getstringval();
    fault_string := fault_node.EXTRACT('/soap:Fault/faultstring/child::text()',
                                       'xmlns:soap="

http://schemas.xmlsoap.org/soap/envelope/' <code>.getstringval();

RAISE_APPLICATION_ERROR(-20000, fault_code || ' - ' || fault_string);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Err:'||SUBSTR(UTL_HTTP.GET_DETAILED_SQLERRM, 1, 250));
DBMS_OUTPUT.PUT_LINE('Err:'||SQLERRM(SQLCODE));

END;
/

Oracle 9iR2

Can a stored procedure be exposed as a web service?

Currently, we get XML messages from MSMQ, each XML message has a "action" and it calls a corresponding "TAPI" to insert/update/delete the appropriate table.

Instead, can the XML message directly call the stored proc via a web service using SOAP, HTTP GET/POST and all that stuff?

Thanks

Not sure what you mean? Are you referring to 2 phase commit?

How does that come into the picture here? AFAIK this architecture is fairly standard when not using an all-Oracle technology stack using Oracle AQ, et. al. When other messaging solutions are used (like MSMQ, IBM MQ Series, Tibco, etc), with Oracle as the target database, they all "serialize" the XML into the stored proc's input parameters and call the API. The API does what it does and returns a status code/error message back.

How does this relate to my question on "exposing a Oracle stored proc as a web service"?

Thanks

is it possible to post a request with UTL_HTTP to a CGI-script (e.g. PHP-script) which receives the request-body (e.g. body='test=1234') as POST-parameters. I am trying that for a few day now and I have not yet found a possibility that works.
There is no error message in my plsql procedure but the PHP-script receives no POST-parameters although I use the POST-methode and give the correct content-length and so on.

TOM,

We have a plsql server page application.
The requirement is, on mouse click to send the address of customer to BizTalk server. The BizTalk guys suggests me to use http post to send the info. I have no idea how it works. Can you help me with this !

TIA

TOM,

All that i need to know is back success/failure information.
When i click on a button on the web page this post has to happen. I hope this is what you expecting to know.
Oracle version is 9.2

TIA

TOM,

Now i know what you expcted to know from me.
Its method b) i am interested in.
Is it possible for you to give some more info on how it will be achieved.

I cant follow what is submitting page and inserting into table. Excuse my ignorance.

TIA

TOM,

Thanks for the inputs.
The documentation could not help me much, probably because i am new to this stuff. In the examples there is no such thing as 'GET', its only 'GET_..something..'. Also there is no mention of the 'message' that needs to be posted.

Also pls let me know what information i need to get from BizTalk server guy.

Is there is any example code you can provide!!

TIA

ask them for the URL you need to send to them to get a response back.  EG: Say you wanted to "asktom about biztalk" in your code instead of "ask biztalk", it could be as easy as:

ops$tkyte@ORA9IR2> select utl_http.request( '

) from dual;
 
UTL_HTTP.REQUEST('

-------------------------------------------------------------------------------
<html>
    <head>
 
<script src="/i/javascript/core.js" type="text/javascript"></script>
<link rel="stylesheet" href="/i/css/core.css" type="text/css" />
 <script language="JavaScript1.1" type="text/javascript">
        <!--
        function errorMsg()
        {
....... 

Hi Tom,
In answer to the first question you have mentioned the
use of UTL_TCP for POST .... HTTP 1.0
Here can we use HTTP1.1 instead of HTTP 1.0?
Is this upgradable?how to do it?
If yes,
Will it help in getting response in less amount of time compared to the response using HTTP 1.0?

Hi Tom, 
I want my procedure to abort quickly if the URL is not responding, but set_transfer_timeout doesn't work:

SQL> DECLARE
  2     lv_http_req UTL_HTTP.req;
  3     lv_http_res UTL_HTTP.resp;
  4     lv_url varchar2(2000);
  5  BEGIN
  6     lv_url := '

http://192.168.2.101:8080/something.do';

  7     UTL_HTTP.set_transfer_timeout(3);
  8     lv_http_req := UTL_HTTP.BEGIN_REQUEST(lv_url);
  9     lv_http_res := UTL_HTTP.GET_RESPONSE(lv_http_req);
 10     UTL_HTTP.END_RESPONSE(lv_http_res);
 11  END;
 12  /
DECLARE
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-12535: TNS:operation timed out
ORA-06512: at line 8

Elapsed: 00:00:21.12

Can you tell me why it takes 21 seconds even I set timeout as 3 seconds? Thank you. 

I have a stored procedure and I want to run a report using SRW package.
But I am getting some errors and I can not figure it out which is the source of the problem or if I need to make some modifications to the OAS files.

1. Stored Procedure Aems_Test
CREATE OR REPLACE PROCEDURE Aems_Test IS 
v_custom_list srw_paramlist;
v_report_id srw.job_ident;
v_status srw.status_record;

v_host_name varchar2(200) := lower(sf_get_param_val(null, 'WEB_AS_HOST10G')||'/reports/rwservlet/'); 
v_server_name varchar2(200) := lower(sf_get_param_val(2000, 'WEB_RP_SVR_NAME'));
v_report_name varchar2(50) := lower('post_header.rdf');
v_userid varchar2(50) := not show for security reason;
v_authid varchar2(500) := not show for security reason;
v_destype varchar2(25) := 'file';
v_desformat varchar2(25) := 'pdf';
v_ias_temp varchar2(500) := sf_get_param_val(null, 'WEB_RS_URLCK10G');
v_unique_seq varchar2(50) := lower(to_char(sf_get_sys_date, 'DDMONYYYYHH24MISS'));
v_desname varchar2(550) := v_unique_seq||'post_header.rdf';

cursor get_data_to_process is select seq, nombre, direccion, telefono, limite
from aems_prueba
where rep_generated = 'N';
BEGIN
for x in get_data_to_process loop
srw.start_debugging;
v_custom_list := srw_paramlist(srw_parameter('',''));
srw.add_parameter(v_custom_list, 'GATEWAY' , v_host_name);
srw.add_parameter(v_custom_list, 'SERVER' , v_server_name);
srw.add_parameter(v_custom_list, 'REPORT' , v_report_name);
srw.add_parameter(v_custom_list, 'USERID' , v_userid);
srw.add_parameter(v_custom_list, 'AUTHID' , v_authid);
srw.add_parameter(v_custom_list, 'DESTYPE' , v_destype);
srw.add_parameter(v_custom_list, 'DESFORMAT', v_desformat);
srw.add_parameter(v_custom_list, 'DESNAME' , v_desname);

v_report_id := srw.run_report(v_custom_list);
v_status := srw.report_status(v_report_id);
srw.stop_debugging;
end loop;
exception
when others then
declare
v_error varchar2(4000);
begin
v_error := utl_http.get_detailed_sqlerrm;
dbms_output.put_line(v_error);
end;
END;
/

2. Error got it eecuting proc on SQL*Plus
SQL> exec aems_test;
***********************************************************
* WELCOME TO EVENT-BASED-REPORTING API *
* API-Version : 9i *
* (C) Oracle Corporation, 2000 *
*
* Debugging turned ON *************************************
*** Length of Paramlist : 1
OK : Parameter added : GATEWAY=

http://not

show for security reason/reports/rwservlet/
*** Length of Paramlist : 2
OK : Parameter added : SERVER=repcoco
*** Length of Paramlist : 3
OK : Parameter added : REPORT=post_header.rdf
*** Length of Paramlist : 4
OK : Parameter added : USERID=not show for security reason
*** Length of Paramlist : 5
OK : Parameter added : AUTHID=not show for security reason
*** Length of Paramlist : 6
OK : Parameter added : DESTYPE=file
*** Length of Paramlist : 7
OK : Parameter added : DESFORMAT=pdf
*** Length of Paramlist : 8
OK : Parameter added : DESNAME=17nov2006105612post_header.rdf
Starting run_report: building url
*** Building URL (RUN_REPORT)
OK : URL built :

http://not

show for security reason/reports/rwservlet?SERVER=repcoco&REPORT=post%5Fheader%2Erdf&USERID=not show for security reason&AUTHID=not show for security reason&DESTYPE=file&DESFORMAT=pdf&DESNAME=17nov2006105612post%
5Fheader%2Erdf&statusformat=xml
*** Submitting HTTP Request
*** using URL
:

http://not

show for security reason/reports/rwservlet?SERVER=repcoco&REPORT=post%5Fheader%2Erdf&USERID=not show for security reason&AUTHID=not show for security reason&DESTYPE=file&DESFORMAT=pdf&DESNAME=17nov2006105612post
%5Fheader%2Erdf&statusformat=xml
ERROR : HTTP request failed
ORA-12535: TNS:operation timed out

PL/SQL procedure successfully completed.

3. Error got it executing proc on TOAD
***********************************************************
* WELCOME TO EVENT-BASED-REPORTING API *
* API-Version : 9i *
* (C) Oracle Corporation, 2000 *
*
* Debugging turned ON *************************************
*** Length of Paramlist : 1
OK : Parameter added : GATEWAY=

http://not

show for security reason/reports/rwservlet/
*** Length of Paramlist : 2
OK : Parameter added : SERVER=repcoco
*** Length of Paramlist : 3
OK : Parameter added : REPORT=post_header.rdf
*** Length of Paramlist : 4
OK : Parameter added : USERID=evtms/ecoco@coco
*** Length of Paramlist : 5
OK : Parameter added : AUTHID=evtms/ecoco@coco
*** Length of Paramlist : 6
OK : Parameter added : DESTYPE=file
*** Length of Paramlist : 7
OK : Parameter added : DESFORMAT=pdf
*** Length of Paramlist : 8
OK : Parameter added : DESNAME=17nov2006103405post_header.rdf
Starting run_report: building url
*** Building URL (RUN_REPORT)
OK : URL built : 

http://not

show for security reason/reports/rwservlet?SERVER=repcoco&REPORT=post%5Fheader%2Erdf&USERID=not show for security reason&AUTHID=not show for security reason&DESTYPE=file&DESFORMAT=pdf&DESNAME=17nov2006103405post%5Fheader%2Erdf&statusformat=xml
*** Submitting HTTP Request
*** using URL :

http://not

show for security reason/reports/rwservlet?SERVER=repcoco&REPORT=post%5Fheader%2Erdf&USERID=not show for security reason&AUTHID=not show for security reason&DESTYPE=file&DESFORMAT=pdf&DESNAME=17nov2006103405post%5Fheader%2Erdf&statusformat=xml
ERROR : HTTP request failed
ORA-12545: Connect failed because target host or object does not exist


If you look they are similar but, it doesn't give me some clues of what I need to change, add or do to fix the problem.
not show for security reason: this is not the problem, not the host and port or the user password or connect string.

But I need to know what the errors means and how to fix it.

Thanks a lot,
Abdel Miranda
Panama 

Tom,
Is it possible to 'post' an xml message to a web server and get a reply back using UTL_HTTP package? I was looking for the same information in the books but it is a little bit scattered. I want to post an XML message to a web service and get a reply back instantly in the same format and parse(unmarshall) it and then store it in a database. Is it possible?

Tom:

I was trying to test UTL_HTTP using another pl/sql proceedure as a CGI program. I have a simple procedure in my test instance that insert a record into TEST table. It runs fine via URL and mod_plsql IN the TEST instance.

When I tried to call it from my development instance using UTL_HTTP it prompted me for the input parameter values (even though they were in the URl) and gave me the following error. Is it becuse I am not using MOD_CGI or what?


SQLDEV> select utl_http.request(' http://xxx.xxx.xxx:7778/pls/itst/test_add_emp?i_empid=1&i_first_nam
e=John&i_last_name=Jones') from dual;
Enter value for i_first_name: d
Enter value for i_last_name: c
old 1: select utl_http.request(' http://xxx.xxx.xxx:7778/pls/itst/test_add_emp?i_empid=1&i_first_na
new 1: select utl_http.request(' http://rs9.loc.gov:7778/pls/itst/test_add_emp?i_empid=1d=Johnc=Jon

UTL_HTTP.REQUEST(' HTTP://xxx.xxx.xxx:7778/PLS/ITST/TEST_ADD_EMP?I_EMPID=1D=JOHNC
--------------------------------------------------------------------------------
Mon, 5 Mar 2007 21:06:22 GMT

ORA-01722: invalid number
ORA-06512: at "DEV.TEST_ADD_EMP", line 9
ORA-06512: at line 10

DAD name: itst
PROCEDURE : test_add_emp
URL : http://xxx.xxx.xxx:7777/pls/imst/test_add_emp?i_empid=1d=Johnc=Jo
nes
PARAMETERS :
===========
i_empid:
1d=Johnc=Jones

ENVIRONMENT:
============
PLSQL_GATEWAY=WebDb
GATEWAY_IVERSION=2
SERVER_SOFTWARE=Oracle HTTP Server/1.3.22 (Unix) mod_plsql/9.0.2.0.0 mod_oc4
j/3.0 mod_ossl/9.0.2.0.0 mod_fastcgi/2.2.10 mod_perl/1.26 mod_oprocmgr/1.0
GATEWAY_INTERFACE=CGI/1.1

Tom:

it worked nicely.

Does that mean I can do the same thing with an CGI program running on the remote server.

Basically my program caculates the parameter values then pass it using UTL_HTTP to that other CGI and get the HTML (success or failuire) from that CGI back to my database.

2. I got some confusion between GET and POST. According to HTTP, a GET request is to download file to my web server, a POST request to upload data to a remote server.

But you can post html forms using GET request too? Can you tell me your understanding.

Tom:

Can you verify the following:

1. UTL_HTTP works like HTTP client tools WGET and CURL. You can download web pages and post forms and upload files to other web servers. Correct?

2. In order for an oracle database to talk to another unix box with apache we can implement the follwing technique:

2a. For a database to send an HTTP message to external unix box

we need two programs: one PL/SQL program in the database that uses UTL_HTTP and one perl cgi program on the unix box that can accept parameters values passed by PL/SQL. The database then can call that cgi program and pass the values it needed to that CGI program to do something on the server. Then CGI program can send a message back to that PL/SQL program, by calling the URL for the PL/sql stored procedure and passing a parmater values indicating "OK" or "FAIL".


2b. For an external server to send an HTTP message to oracle database.

we need two programs: one CGI program that calls the URL for PL/SQL stored procedure and passes the parameter value to it. The pl/sql procedure then does something and returns an HTML page with results (input element name. value) that the CGI can parse and take action based on.


3. Can Telnet be used to post http forms over port 80 using HTTP?

4. You said that
<The existing UTL_HTTP package in the database does not support POST, only GET>

What is the difference of posting versus calling a URL and passing the values in a URL Query string




THank you,

TOm:

1. on 2(b) I dont understand what are the issues and why I need Embedded Pl/SQL gateway.

Actually reading the defintion on this I do not understand how it is different than mod_plsql

http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php

Is this for creating and administering DADs from database?

We access oracle web apps using the browser. The browser can be a CGI program that runs that URL through mod-plsql. What is the issue here and what is that embedded gateway for.



2. On (3) Do you mean telnet is a bad choice for using it as an http client and http scripting?

3. I am using Oracle 9.2.0.2. Does it support form posting with UTL_HTTP and DBMS_EPG?

Tom:

Can you explain more what you mean by

<2b) no. You would use dbms_epg blah blah blah blah.... you have missed something fundemental here.>

9i does not have that package and why do i need it anyway.

Why I can't use a CGI program (i.e. perl) that uses an http client software call (i.e. curl or wget) to post data to my pl/sql stored procedure using mod_plsql.

How would you do it.

TOm:

This is exactly my point. Any client that talks HTTP can talk to mod-plsql just like a browser.

THE CGI has to use Perl API, WGET, CURL or whatever that can send http messages and receive results. It should do same thing UTL_HTTP (oracle API) does in database.

Thanks

Tom:

1. Can you use XML format over http to send messsages back and forth between oracle server and other http client?
Like if client sends a query and oracle sends back results.

2. Would you do it using HTML or XML and why? I guess in both cases you have to parse the message and look for the field name in HTML or tag name in XML.

1. Does the XML that Stored procedure write and post to other server has to be embeded in HTML tags? I though http clients can only read HTML tags.

2. You did not tell me what you would do different than my way. WHat is your way?

3. Are you saying use:

HTML message for sending requests to MOD_PLSQL/Stored procedure

XML message OUT from oracle to other http client using QUERY_STRING.

Thank you,

Tom,
Can you give some information about how i can post an XML to a webserver[another oracle application]?

We are currently creating XML in Oracle 10g and posting it using Java POST Method. I want to avoid java and want to keep all in Oracle [i understand oracle..that's why]
If you can give a simple example showing what steps need to be taken in posting XML and check response back from webserver, I would really appreciate it.

Thanks,

Hi Tom, I have a very simple question:

Is it possible to simply call a URL within a Stored Procedure? I can't find any function to do that!

I've been using utl_http.request_pieces, but then I need to use "htp.p" within a loop to print the called page --while I just need to redirect the browser to it. If I print the URL in the browser and then click on it, it works... but I don't wan't to click, I want the stored procedure to do that for me.

Thanks!

I've been using successfully owa_util.redirect_url, but sometimes it simply doesn't work and doesn't show any error messages. For example:

-the url

http://www.test.com/owa/myPackage.RedirectProcedure

should redirect to the page

http://www.test.com/otherDAD/otherPackage.procedure

- But, when I call owa_util.redirect_url (with NO htp.p code before or after it), the browser just shows a blank page, with no HTML source code and --that is the problem-- no error messages... The address box still shows the original url:

http://www.test.com/owa/myPackage.RedirectProcedure

Is it possible to get to know what is happening?

Thanks !

ops$tkyte%ORA9IR2> @owainit

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> exec owa_util.redirect_url( 'http://somewhere.else.to.go/' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> exec owa_util.showpage
Location: http://somewhere.else.to.go/
Content-length: 0

PL/SQL procedure successfully completed.

Here's a piece of code, I use, and never had problems with. I think it's in the way the header is processed, and that's what I miss in previous descriptions:

   PROCEDURE validate_user(p_username IN VARCHAR2, p_password IN VARCHAR2)
   IS
   BEGIN
      IF p_username IS NOT NULL AND p_password IS NOT NULL
      THEN
         IF validate_usr(p_username, p_password)
         THEN
            OWA_UTIL.mime_header('text/html', FALSE);
            owa_cookie.send('MyAppCookie', g_sid, NULL);

            IF g_profile IN('ADMIN', 'ROOT')
            THEN
               OWA_UTIL.redirect_url('privatepkg.bpage1', bclose_header => TRUE);
            ELSE
               OWA_UTIL.redirect_url('privatepkg.gdirs', bclose_header => TRUE);
            END IF;
         ELSE
            HTP.p(format.infopage('Invalid login'));
         END IF;
      ELSE
         OWA_UTIL.redirect_url('publicpkg.showLogin', bclose_header => FALSE);
      END IF;
   END validate_user;

hth, Frank

Tom:

IF I have a program wirtten in PERL that basically send a request (URL that uses a ger or post method) to my oracle database and the database puts the information on a plate and sends it back over http. I can format the data in any way I want (i.e text, html, xml, etc).

We have no issues with that. However let us say the request that comes to Oracle is for a "File" that is either stored in the database or the file system.

Can Oracle do this and what do you use in Oracle to serve a file over http to the PERL program?

dear tom
i need your help to create http request to send messege to another computer use port 80
can you please help me i wait your feedback
thank you for your support


ayman
ayman-ghannam@hotmail.com

Tom:

Do you have a utility that monitors if the 9IAS (http server) is up or not?

I was thinking that the pl/sql program would use this package to ping the web server every minute and based on the response it determines if the web server is up or not?

Now web server is on different machine than database.

or do you think this should be really some perl or unix routine since if DB is down the monitoring cant be done?

Tom:

But if i want to write a small tool would I be using utl_http and checking the data in the response to determine if it is up or down?

Dear Tom,
We are using utl_http to consume a web service. The problem is when we execute the web service thru web browser we get the correct response. but when send the request with utl_http then we get ora-29276 transfer timeout error.
I don't say the error is due to utl_http, it can be coding problem either on server or client but I would appreciate if you can confirm the problem is on client and not server hosting the webserver (or vic versa) if client gets the proper response in web browser.
This issue has been running since last 7 days and nobody is ready to take responsibility of the problem. Server side developers says its client problem and vice versa.

Thanks in advance.

Dear,

iam trying to use http request from oracle database to call an url, when calling the url from the MS IE in the database server the request is succeded, but when using utl_http its not
i have this error mesage:
ORA-29268: HTTP client error 407- Proxy Authentication Required ( The ISA Server requires authorization to fulfill the request. Access to the Web Proxy filter is denied. )

Process exited.

even i set the authontication like this:

UTL_HTTP.SET_PROXY (
PROXY => ' http://username:password@192.168.xx.xx:8080'
);

is that correct?

thanks.

Hi Tom,

My db version is 11gR2. What i want is some thing like
Case 1
UTL_HTTP Call to a URL (needs authentication)
SEND User_name and password
SEND a parameter like "table_name: value_of_table_name"
(MY routines which gets executed by URL call will take table_name parameter and generate XML for the table's selected records)
Generated XML is returned as a response to utl_http call.

Case2
UTL_HTTP Call to a URL (needs authentication)
SEND User_name and password
SEND XML data <<is there any Limit ?>>
(MY_routines which are invoked by the URL call take XML input and perform some DML Operation in database)
As a response i get STATUS (OK, or failed)

Please suggest.

Hi Tom,

Thanks for your quick response. I am not sure how Creating webservices or using APEX may will solve my purpose. But i want or willing to achieve is send changed data of one table in a database to other remote database same table. The number of table are about 100 not all table will go in one request. I am thinking to use the following
o Use DBMS_CDC_PUBLISH and subscribe to get changed data.
o Generate XML using DBMS_XMLGEN

A PHP page will do this for me after connecting to one source Database and will output XML. This php page will be called by Remote database pl/sql routine using UTL_http and the XML will be re parsed to insert the data in the remote database table.

Do you think generating PL/SQL webservice will be useful in this scenario. If yes how?

Actually what we are trying to do is to Sync two databases schema to schema mapping with the changed data only on call. Due to some reason cannot use DB link.

Please suggest.
Many thanks in advance.

Vikas Sharma

Hi Tom,

When I do a POST request from javascript something like:

frm.setAttribute("method", "POST");
frm.setAttribute("action", "DAD url");


to PL/SQL embedded gateway, the request doesn't seem to go through because

owa_util.get_cgi_env('REQUEST_METHOD') doesn't get the method.

Does PL/SQL embedded gateway supports POST and PUT?

Thanks