CMD
SQLPLUS /NOLOG
CONN SYS/TEKNISA@XE AS SYSDBA
/
GRANT EXECUTE ON SYS.UTL_HTTP TO TEKNISA;
GRANT EXECUTE ON SYS.DBMS_LOCK TO TEKNISA;
GRANT EXECUTE ON SYS.UTL_TCP TO TEKNISA;
GRANT EXECUTE ON SYS.UTL_SMTP TO TEKNISA;
GRANT EXECUTE ON SYS.UTL_INADDR TO TEKNISA;
GRANT EXECUTE ON SYS.UTL_MAIL TO TEKNISA;
GRANT EXECUTE ANY PROGRAM TO TEKNISA;
GRANT CREATE JOB TO TEKNISA;
--SET SERVEROUTPUT ON;

/

begin
dbms_network_acl_admin.create_acl (
acl => 'http_permissions.xml',
description => 'HTTP Access',
principal => 'TEKNISA',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
/
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',
principal => 'TEKNISA',
is_grant => true,
privilege => 'connect');
end;
/
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',
principal => 'TEKNISA',
is_grant => true,
privilege => 'resolve');
end;
/
BEGIN
dbms_network_acl_admin.assign_acl (
acl => 'http_permissions.xml',
host => '*',
lower_port => 1,
upper_port => 9999
);
END;
/
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'http_permissions.xml',
        principal   => 'TEKNISA',
        is_grant    =>  TRUE,
        privilege   => 'use-client-certificates');
END;
/
BEGIN
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
       acl => 'http_permissions.xml',
       wallet_path => 'file:C:\oraclexe\wallet');
END;
/

begin
    dbms_network_acl_admin.append_host_ace (
      host       => 'integracaoerp.cisbrasil.com.br',
      lower_port => 1,
      upper_port => 9999,
      ace        => xs$ace_type(privilege_list => xs$name_list('https'),
                                principal_name => 'TEKNISA',
                                principal_type => xs_acl.ptype_db));
end;
/
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  host => 'www.us.example.com',
  ace  =>  xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),
                       principal_name => 'TEKNISA',
                       principal_type => xs_acl.ptype_db)); 
/
/*
BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
        acl         => 'us-oracle-com-permissions.xml',
        principal   => 'ST_USERS')
END;
*/
/
Execute Dbms_Network_Acl_Admin.Create_Acl ( Acl => 'utl_http.xml',Description => 'HTTP Access', Principal => 'UNIMED', Is_Grant => True, Privilege => 'connect' );
Execute Dbms_network_acl_admin.add_privilege ( Acl => 'utl_http.xml',Principal => 'UNIMED', Is_grant => TRUE, Privilege => 'connect');
Execute Dbms_network_acl_admin.add_privilege ( Acl => 'utl_http.xml',Principal => 'UNIMED', Is_grant => TRUE, Privilege => 'resolve');
Execute Dbms_Network_Acl_Admin.add_privilege(Acl => 'http_permissions.xml', Principal => 'TEKNISA', is_grant    =>  TRUE, privilege   => 'use-client-certificates');
Execute Dbms_Network_Acl_Admin.Assign_Acl (Acl => 'utl_http.xml',Host => '*', lower_port => 80, upper_port => 80);

-- Execute Dbms_Network_Acl_Admin.Delete_privilege (Acl => 'utl_http.xml', Principal => 'UNIMED');
/
commit;
/
select * from dba_network_acls;
/
SELECT *
FROM dba_network_acl_privileges
where principal='PCA';
/
select utl_http.request('http://integracaoerp.cisbrasil.com.br/backend/service/index.php/exportaAF') from dual
/
--criando wallets par usar HTTPS OBS pwd tem que ser complexo, default usado = teknisa123
orapki wallet create -wallet c:\oraclexe\wallet -pwd teknisa123 -auto_login
--exportar certificado no formato CER e colocar na wallet com o comando:
orapki wallet add -wallet e:\wallet -trusted_cert -cert e:\certificats\<cert file> -pwd <pwd>


--CRIAR TRIGGER QUE CRIA JOB PARA ATUALIZAR OS REGISTROS
create or replace trigger callWebserviceJob
  after insert on USR_APT_OPERFORN
  for each row
  declare
      JOB_N int;
begin
  IF INSERTING THEN
     dbms_job.submit (
      job => JOB_N, 
      what => 'BEGIN TESTAWEBSERVICE(); END;', 
      next_date =>sysdate + interval '10' second
     ); 
  END IF;
end callWebserviceJob;

/
create or replace
procedure testaWebservice
is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'http://rafaeldias.zeedhi.com/workfolder/pebbian-erp-services-apetite/backend/service/index.php/exportaCadastros';
  name varchar2(4000);
  buffer varchar2(4000); 
  content varchar2(4000) := '{"teste":"oi"}';
begin
  req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
  utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
  utl_http.set_header(req, 'content-type', 'application/json'); 
  utl_http.set_header(req, 'Content-Length', length(content));
 
  utl_http.write_text(req, content);
  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  begin
    loop
      utl_http.read_line(res, buffer);
      dbms_output.put_line(buffer);
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;
end testaWebservice;

/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => '/sys/acls/utl_http.xml');
END;