Dear Experts,
I am successful in invoke HTTP WS request through SOAP_API.sql but I am stuck in the HTTPS request.
The error I am getting is
ORA-20000: ns2:Server - Cannot find form with id 0.
ORA-06512: at "DW_OWNER.SOAP_API", line 121
ORA-06512: at "DW_OWNER.SOAP_API", line 240
ORA-06512: at "DW_OWNER.PKG_MY_WEBSERVICE", line 43
ORA-06512: at line 11
I wonder if anyone here could help me in it? Many thanks!
Details
I am trying to invoke Web Services( HTTPS request) through PLSQL package UTL_HTTP. The version of Oracle database is 11g R2.
At this stage, we have granted the domain of the HTTPS WS request to the ACL of the running schema.
We have got the chained PEM certificate added into the Oracle Wallet and ran UTL_HTTP.SET_WALLET.
Also the below SQL runs successfully.
--------
select
utl_http.request('https://keysurveydev.ourdomain/Member/api/v81/form/result/FormResultManagementService?wsdl',null,'directory of the oracle wallet',null) from dual
;
------
So I assume at this stage, the connection is ok. The HTTPS request runs successfully through Soap UI.
Then I have the invoke procedure written this way(changed from original SOAP_API.sql), but then get the error in the check_fault procedure.
FUNCTION invoke(p_request IN OUT NOCOPY t_request,
p_url IN VARCHAR2,
p_action IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN
VARCHAR2 AS
-- ---------------------------------------------------------------------
l_envelope VARCHAR2(32767);
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_response t_response;
l_line_data VARCHAR2(32767);
l_header_name VARCHAR2(32767);
l_header_value VARCHAR2(32767);
BEGIN
generate_envelope(p_request, l_envelope);
show_envelope(l_envelope, 'Request');
l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.1');
IF (p_username IS NOT NULL) THEN
UTL_HTTP.SET_AUTHENTICATION(l_http_request, p_username, p_password); -- Use HTTP Basic Authen. Scheme
END IF;
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml');
UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope));
UTL_HTTP.set_header(l_http_request, 'SOAPAction', p_action);
UTL_HTTP.write_text(l_http_request, l_envelope);
l_http_response := UTL_HTTP.get_response(l_http_request);
IF (l_http_response.status_code = UTL_HTTP.HTTP_UNAUTHORIZED) THEN
DBMS_OUTPUT.PUT_LINE('HTTP response status code: HTTP_UNAUTHORIZED');
UTL_HTTP.END_RESPONSE(l_http_response);
RETURN ('HTTP_UNAUTHORIZED end response');
END IF;
FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(l_http_response) LOOP
UTL_HTTP.GET_HEADER(l_http_response, i, l_header_name, l_header_value);
DBMS_OUTPUT.PUT_LINE(l_header_name || ': ' || l_header_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || l_http_response.status_code);
DBMS_OUTPUT.PUT_LINE('HTTP response reason phrase: ' || l_http_response.reason_phrase);
UTL_HTTP.read_text(l_http_response, l_envelope);
UTL_HTTP.end_response(l_http_response);
show_envelope(l_envelope, 'Response');
l_response.doc := XMLTYPE.createxml(l_envelope);
l_response.envelope_tag := p_request.envelope_tag;
l_response.doc := l_response.doc.extract('/'||l_response.envelope_tag||':Envelope/'||l_response.envelope_tag||':Body/child::node()',
'xmlns:'||l_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/"');
check_fault(l_response);
RETURN 'Normal return';
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(l_http_response);
DBMS_OUTPUT.PUT_LINE('utl_http.end_of_body');
RETURN ('end_of_body');
END;
---------------------------------
I get the error in the red line in the check_fault procedure.
PROCEDURE check_fault(p_response IN OUT NOCOPY t_response) AS
-- ---------------------------------------------------------------------
l_fault_node XMLTYPE;
l_fault_code VARCHAR2(256);
l_fault_string VARCHAR2(32767);
BEGIN
l_fault_node := p_response.doc.extract('/'||p_response.envelope_tag||':Fault',
'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/');
IF (l_fault_node IS NOT NULL) THEN
l_fault_code := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultcode/child::text()',
'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
l_fault_string := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultstring/child::text()',
'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
RAISE_APPLICATION_ERROR(-20000, l_fault_code || ' - ' || l_fault_string);
END IF;
END;
The error is
ORA-20000: ns2:Server - Cannot find form with id 0.
Is there anyone who encountered this before?
Many thanks!
Amy
----------------------------------------------------------------
Notes
I downloaded the soap_api.sql from http://www.google.com.au/url?url=http://www.oracle-base.com/dba/miscellaneous/soap_api.sql&rct=j&frm=1&q=&esrc=s&sa=U&ei=sc-fU56aFIjMkQX7mICoDw&ved=0CBQQFjAA&sig2=jxqML7lgxWg0Oa82PJUQnw&usg=AFQjCNHi_IyY1Ir_LUWhO7K01GXQEMKtIQ
I also get this example working successfully. http://www.oracle-base.com/articles/9i/consuming-web-services-9i.php
Message was edited by: Amy Qin