apex_web_service.make_rest_request POST有错误的json或返回ORA-06502数字或值错误

Question

我无法通过使用apex_web_service.make_rest_request()使用PLSQL使用e-verify/dhs获得成功的POST/身份验证/登录。我先在Apex中尝试，但得到了数值/值错误，然后切换到PLSQL进行调试。它使用JSON。顶点版本是4.2。我可以得到一个get to work，但这不使用header或parms。

null

因此，我当然希望这段代码能够正常工作，但相反，我如何查看来自Oracle/Apex的请求，以便确认json是什么样子的（对于上面的#1）？谢了！

这里是代码。

      l_parm_names  apex_application_global.vc_arr2;  
      l_parm_values apex_application_global.vc_arr2;   
      l_resp_clob        clob;
      l_resp_length      integer;
      l_body_varchar2    varchar2(4000);
      l_body_clob        clob;
    begin  
      l_parm_names(1) := 'username';  
      l_parm_values(1) := 'user1234';  
      l_parm_names(2) := 'password';  
      l_parm_values(2) := 'pass1234';
      l_body_varchar2  := '{"username":"user1234","password":"pass1234"}';
      l_body_clob      := to_clob(l_body_varchar2);
      apex_web_service.g_request_headers.delete();
      apex_web_service.g_request_headers(1).name := 'Content-Type';  
      apex_web_service.g_request_headers(1).value := 'application/json';
      l_resp_clob := apex_web_service.make_rest_request(
         p_url => 'https://stage-everify*******login',  
         p_http_method => 'POST',
--         p_parm_name => apex_util.string_to_table('username:password'),
--         p_parm_value => apex_util.string_to_table('user1234:pass1234')
--         p_parm_name => l_parm_names,
--         p_parm_value => l_parm_values
         p_body => l_body_clob
         );   
       INSERT INTO ev_clob (body, resp, dte, note)
        VALUES (l_body_clob, l_resp_clob, SYSDATE, 'Stack Script 1');  commit;
    end;

Answer 1

您需要以这种方式生成json

SET SERVEROUTPUT ON
DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN

  OPEN l_cursor FOR
    SELECT e.empno AS "employee_number",
           e.ename AS "employee_name",
           e.deptno AS "department_number"
    FROM   emp e
    WHERE  rownum <= 2;

  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object;
  APEX_JSON.write('employees', l_cursor);
  APEX_JSON.close_object;

  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
END;
/

您可以在这里找到更多信息https://docs.oracle.com/cd/e59726_01/doc.50/e39149/apex_json.htm#AEAPI29737

BEGIN
  apex_json.open_object; -- {
  apex_json.open_object('obj'); -- "obj": {
  apex_json.write('obj-attr', 'value'); -- "obj-attr": "value"
  apex_json.close_all; -- }}
END;