Skip to Main Content
  • Questions
  • blob to clob on ORDS Handler Definition

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 06, 2021 - 10:37 pm UTC

Last updated: January 11, 2021 - 2:11 am UTC

Version: 20.2

Viewed 1000+ times

You Asked

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

and Connor said...

Thanks - we'll post this with your answer included here


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')


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here