Blz, é isso aí... Só para mostrar que as instruções em si estão 100% corretas,
segue o teste que fiz numa VM com 12cR2 (a Developer´s Day que vc baixa em
technet.oracle.com), que já vem com APEX instalado num PDB como é necessário, e
no exemplo estou conectado como SYS nesse PDB....
NEM PRECISO DIZER, eu Não tenho nem Antivírus Nem firewall nem nada assim
bloqueando meu acesso à internet... O exemplo :
==> o APEX tá instalado :
SQL> SELECT username,
account_status,
TO_CHAR(lock_date, 'DD-MON-YYYY') AS lock_date,
TO_CHAR(expiry_date, 'DD-MON-YYYY') AS expiry_date,
default_tablespace,
temporary_tablespace
FROM dba_users
WHERE username LIKE UPPER('%APEX%')
ORDER BY username; 2 3 4 5 6 7 8 9
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE
-------------------------------- -------------------- --------------------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
APEX_050100
OPEN
SYSAUX TEMP
APEX_INSTANCE_ADMIN_USER
EXPIRED 02-MAR-2017
USERS TEMP
USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE
-------------------------------- -------------------- --------------------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
APEX_LISTENER
OPEN
USERS TEMP
APEX_PUBLIC_USER
OPEN
==> vou criar um ACL, obviamente já usando a sintaxe 12c :
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_NETWORK_ACL_ADMIN.append_host_ace (
3 host => 'oracle-base.com',
4 lower_port => 80,
5 upper_port => 80,
6 ace => xs$ace_type(privilege_list => xs$name_list('http'),
7 principal_name => 'APEX_050100',
8 principal_type => xs_acl.ptype_db));
9* END;
SQL> /
PL/SQL procedure successfully completed.
==> crio a função que consome o web service de demonstração lá do ORACLE_BASE ,
que nem ele mostrou :
SQL> CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN NUMBER,
p_int_2 IN NUMBER)
RETURN NUMBER
AS
l_envelope CLOB;
l_xml XMLTYPE;
l_result VARCHAR2(32767);
BEGIN
-- Build a SOAP document appropriate for the web service.
l_envelope := '<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/";
xmlns:tns="http://www.ignyte.com/whatsshowing";
xmlns:xs="http://www.w3.org/2001/XMLSchema";>
<soap:Body>
<ws_add xmlns="http://oracle-base.com/webservices/";
soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/";>
<int1 xsi:type="xsd:integer">' || p_int_1 || '</int1>
<int2 xsi:type="xsd:integer">' || p_int_2 || '</int2>
</ws_add>
</soap:Body>
</soap:Envelope>';
-- Get the XML response from the web service.
l_xml := APEX_WEB_SERVICE.make_request(
p_url => 'http://oracle-base.com/webservices/server.php',
p_action => 'http://oracle-base.com/webservices/server.php/ws_add',
p_envelope => l_envelope
);
-- Display the whole SOAP document returned.
DBMS_OUTPUT.put_line('l_xml=' || l_xml.getClobVal());
-- Pull out the specific value of interest.
l_result := APEX_WEB_SERVICE.parse_xml(
p_xml => l_xml,
p_xpath => '//return/text()',
p_ns => 'xmlns:ns1="http://oracle-base.com/webservices/";'
);
DBMS_OUTPUT.put_line('l_result=' || l_result);
RETURN TO_NUMBER(l_result);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44
Function created.
===> Antes que vc pergunte, como estou copiando e colando no sqlplus executando
via terminal, ele desposiciona os números de linha, ignore isso.... Vou
executar a função :
SQL> SELECT add_numbers(1, 5) FROM dual;
ADD_NUMBERS(1,5)
----------------
6
==> okdoc, funfa certinho.... Vou meter um SERVEROUTPUT, que nem ele sugere na
página, para debug/demonstração :
SQL> SET SERVEROUTPUT ON
SELECT add_numbers(1, 5) FROM dual;
2
SQL>
SQL> l
1* SELECT add_numbers(1, 5) FROM
SQL> a dual;
1* SELECT add_numbers(1, 5) FROM dual
SQL> /
ADD_NUMBERS(1,5)
----------------
6
l_xml=<?xml version="1.0" encoding="ISO-8859-1"?><SOAP-ENV:Envelope
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/";
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/";
xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/";><SOAP-ENV:Body><ns1:ws_addResponse
xmlns:ns1="http://oracle-base.com/webservices/";><return
xsi:type="xsd:string">6</return></ns1:ws_addResponse></SOAP-ENV:Body></SOAP-ENV:Envelope>
l_result=6
SQL>
Blz ???
[]s
Chiappa
