oracle调用API

一、开启ACL

1、创建ACL  黄色标记的换成对应的用户名即可

begin

 dbms_network_acl_admin.create_acl (

 acl => 'HTTPUSER_UTL_INADDR.xml',--自定义授权配置文件

 description => 'utl_inaddr',

  principal => 'HTTPUSER',   --此为将来要进行操作的用户

 is_grant => TRUE,

  privilege => 'resolve'

  );

  commit;

  end;

2  、增加权限

begin

dbms_network_acl_admin.add_privilege (

acl => 'HTTPUSER_UTL_INADDR.xml',

principal => 'HTTPUSER',

is_grant => TRUE,

privilege => 'connect'

);

 commit;

end;

3 、授予用户权限

begin

dbms_network_acl_admin.assign_acl(

acl => 'HTTPUSER_UTL_INADDR.xml',

host => '*'--允许访问的ip地址

);

commit;

end;

二、创建函数

create or replace function jgh22(r_content in  string)

return varchar2

 IS

  req  UTL_HTTP.REQ;

  resp UTL_HTTP.RESP;

  message  clob;

  strtemp varchar2(32767);

  r_content2 varchar2(32767);

  r_content3 varchar2(32767);

  r_url varchar2(2000) := 'http://192.168.31.86:8504/api'; #换成对应的API

begin

    begin

      r_content2:='{'||'"line":"'||r_content||'"}';

      r_content2:=replace(r_content2,chr(10),' ') ; #替换\r \n \t

      r_content2:=replace(r_content2,chr(13),' ') ;

      r_content2:=replace(r_content2,chr(9),' ') ;

      req  := UTL_HTTP.BEGIN_REQUEST(r_url,'POST','HTTP/1.1');

       utl_http.set_persistent_conn_support(req,TRUE);

        utl_http.set_header(req, 'Content-Type', 'application/json;charset=utf-8');

         strtemp:=CONVERT(r_content2, 'UTF8','ZHS16GBK'); #字符串类型转换，需根据数据库类型适配

       ---设置字符集

      UTL_HTTP.set_body_charset(req,'utf-8');

       utl_http.set_header(req, 'Content-Length',lengthb(strtemp));

       utl_http.write_raw(req,utl_raw.cast_to_raw(strtemp));

       resp := UTL_HTTP.GET_RESPONSE(req);

       if resp.status_code = 200 then

       utl_http.read_text(resp, message, length(message));  #接收数据

       utl_http.end_response(resp);

       else--错误处理

       message := '网络访问错误！';

       utl_http.end_response(resp);

       end if;

       r_content2 :=message;

     -- utl_http.end_request(req);

    EXCEPTION

      WHEN utl_http.end_of_body THEN

        utl_http.end_response(resp);

        WHEN OTHERS THEN

          dbms_output.put_line(sqlerrm);

          dbms_output.put_line(dbms_utility.format_error_backtrace);

     -- utl_http.end_response(resp);

      utl_http.end_request(req);

    end;

    r_content2:=replace(r_content2,'[[','[')  ;

    r_content2:=replace(r_content2,']]',']')  ;

    r_content2:=replace(r_content2,'],[',']@[')  ;

    return r_content2;

END ;

三、函数调用

select ID,jgh22(DOCTEXT) AS br_level from API_SQL  #表名