Oracle ORDS处理程序定义上的blob到clob

嗨!
我试图发送一个post请求与json:

{
        "id": 12344444,
        "email": "ppppoddddddppp@gmail.com",
        "first_name": "",
        "last_name": "",
        "billing": {
            "first_name": "22222",
            "last_name": "",
            "company": "",
            "address_1": "",
            "address_2": "",
            "city": "",
            "postcode": "",
            "country": "",
            "state": "",
            "email": "",
            "phone": ""
        }
}

我正在尝试使用apex_json提取信息，例如: “在”

我阅读了以下指南:https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#parsing-json
它工作，但不在ORDS处理程序定义内 ....

我正在尝试使用以下代码...但它不是插入数据并返回 “201”:

DECLARE
    l_json_payload clob;  
 l_blob_body    blob := :body;
    l_dest_offset  integer := 1;
    l_src_offset   integer := 1;
    l_lang_context integer := dbms_lob.default_lang_ctx;
    l_warning      PLS_INTEGER := DBMS_LOB.warn_inconvertible_char;
BEGIN  
 if dbms_lob.getlength(l_blob_body) = 0 then
   :status_code := 400; --error 
      :errmsg := 'Json is empty'; 
   return;
 end if;


     dbms_lob.createTemporary(lob_loc => l_json_payload ,cache   => false);

    dbms_lob.converttoclob( dest_lob     => l_json_payload
                           ,src_blob     => l_blob_body
                           ,amount       => dbms_lob.lobmaxsize
                           ,dest_offset  => l_dest_offset
                           ,src_offset   => l_src_offset
                           ,blob_csid    => dbms_lob.default_csid
                           ,lang_context => l_lang_context
                           ,warning      => l_warning); 

APEX_JSON.parse(l_json_payload);

                             INSERT INTO ACCOUNTS
              ( 
                wp_id                           ,
                name                            ,
                email                           ,
                f_name                          ,
                l_name                          ,
                wp_role                         ,
                wp_username                     ,
                woo_is_paying_customer          ,
                woo_billing_first_name           
              ) 
              VALUES 
              ( 
                :id,
                :first_name || ' ' || :last_name,
                :email,
                :first_name,
                :last_name,
                :role,
                :username,
                decode(:is_paying_customer,'false', 'N', 'Y'),
                APEX_JSON.get_varchar2(p_path => 'billing.first_name')
              );

  :status_code := 201; --created 

EXCEPTION 

WHEN OTHERS THEN 
  :status_code := 400; --error 
  :errmsg := SQLERRM; 
END;

更新:
经过测试-问题出在这一行:

l_blob_body    blob := :body;

当我输入它时，它不会将任何内容插入数据库

更新2:
经过测试...
我意识到，这是不可能的结合:
: body和其他绑定值，因此应改用APEX_JSON.get_varchar2 (p_path => 'billing.first_name ')

所以问题就解决了

谢谢-我们将在此处发布您的答案


after testing...
I realized that it is not possible to combine:
: body and other bind value, so APEX_JSON.get_varchar2 should be used instead (p_path => 'billing.first_name')