Hi!
I'm trying to send a post request with 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": ""
}
}
I'm trying to use apex_json to extract information like: ״company״ that is in ״billing״
I read the following guide:
https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#parsing-json and it works but not inside ORDS Handler Definition....
I'm trying to use the following code ... but it's not insert the data and return "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;
updating:
After testing - the problem is in this line:
l_blob_body blob := :body;
When I enter it, it does not insert anything into a database
update 2:
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')
So the problem was solved