Question

Custom Event Action: Execute Online SQL fails to call API

  • 20 April 2022
  • 4 replies
  • 783 views
H
Rank
Badge +4

Within the Contacts grid on the Customer page, I added a Custom Menu option that allows the user to highlight a Contact, RMB click, and select “Push to Salesforce”. This calls an externally hosted REST API that gets the details, does a bunch of backend validation and such, and then pushes the contact to our CRM. It works great using the code below:
 

DECLARE
        person_id_ VARCHAR2(50) := &PERSON_ID;
        url_params_ plsqlap_record_api.type_record_;

BEGIN

             url_params_ := Plsqlap_Record_API.New_Record('URL_PARAMETERS');

             Plsqlap_Record_API.Set_Value(url_params_ ,'personid', person_id_, caseSensitive_ => TRUE);
             Plsql_Rest_Sender_API.Call_Rest_EndPoint_Empty_Body(rest_service_ =>'SEND_CONTACT_TO_SF',
                                                            url_params_ => url_params_ ,
                                                            http_method_ => 'GET',
                                                            sender_           => 'CONNECT',
                                                            receiver_         => 'INTEGRATION_API'
                                                            );    

END;

It has worked flawlessly for about 8 months now, but I’ve been tasked with automating it so the extra RMB click is no longer necessary. So, I created a custom event that automatically calls the API when certain fields are updated on the contact, and it fires as expected calling the assigned action. 

I tried wiring up the built-in REST API Custom Event Action, but it never worked, and after reading lots of posts where others gave up and found success using the Execute Online SQL action instead, I also went that route. I basically copied the exact same SQL code from my Custom Menu event with one change - I replaced the person_id_ variable value with the appropriate substitution field, but otherwise, it’s exactly the same. The modified code is below.

 

DECLARE
        person_id_ VARCHAR2(50) := '&NEW:PERSON_ID';
        url_params_ plsqlap_record_api.type_record_;

BEGIN

             url_params_ := Plsqlap_Record_API.New_Record('URL_PARAMETERS');

             Plsqlap_Record_API.Set_Value(url_params_ ,'personid', person_id_, caseSensitive_ => TRUE);
             Plsql_Rest_Sender_API.Call_Rest_EndPoint_Empty_Body(rest_service_ =>'SEND_CONTACT_TO_SF',
                                                            url_params_ => url_params_ ,
                                                            http_method_ => 'GET',
                                                            sender_           => 'CONNECT',
                                                            receiver_         => 'INTEGRATION_API'
                                                            );    

END;

I’ve confirmed that the action is being triggered by inserting this into the code and seeing the popup on the screen.
error_sys.appl_general('CustomerInfoContact','Event Action Executing for PersonID: :P1', person_id_); 

But the call never reaches the API. The event is added to the OUT1 queue, and sender attempts to send it, but the Application Message in the queue always reads “Failed: Bad Request(400)”. It’s never even reaching the API endpoint. What am I doing wrong?

4 replies

H
Rank
Badge +4

Just want to add some info for clarity. I can call the API from my local machine and from Postman using the exact same endpoint URL. I can literally copy the url from the routing address, paste it into Postman, click “Send”, and it works perfectly. So the endpoint is functioning as designed, and can be accessed successfully from other events within IFS, just not the Custom Event Action. 

Rusiru Dharmadasa
Rank
Userlevel 7
Badge +19

@Hotdog I suppose custom menu option works in the same environment with same plsql block.

 

Do you see a difference the way that the OUT1 queue application message creates when using two methods (custom menu and custom event)?

 

If possible share us a screen shot showing all details of the application messages in both ways. Make sure to mask confidential information. 

Rusiru
BrandonEllis
Rank
Badge +3

Hi,

 

I’m encountering the exact same issue using plsql_rest_sender_API.Call_Rest_EndPoint

I believe the URL_PARAMETERS are not being parsed correctly.

 

url_ := PLSQLAP_Record_API.New_Record('URL_PARAMETERS');
    Plsqlap_Record_API.Set_Value(url_ , 'parameter1', endpoint_);

 

Did you ever get to the bottom of this?

 

Call_Rest_EndPoint2 works passing the url as a document:

url_ := PLSQLAP_DOCUMENT_API.New_Document('url_params');
    PLSQLAP_DOCUMENT_API.Add_Attribute(url_,'parameter1',endpoint_);

 

however the app_msg_id and keyref_ on the callback is always null.

 

Thanks,

Brandon

ZRLLUCASM
Rank
Badge +3

Hi Sidekick,

I’m use this comunicaiton with parameters.

 

In this case i did create a custom logic unit to storage data before, but you can call the method NEW_ to storage in your final table.

 

 

declare

  v_req    utl_http.req;

  v_res    utl_http.resp;

  v_buffer varchar2(4000);

  v_body   varchar2(4000) := '{"field":"value"}'; -- Your JSON

 

  -- p0 -> __lsResult

  p0_ VARCHAR2(32000) := NULL;

 

  p1_ VARCHAR2(32000) := NULL;

 

  p2_ VARCHAR2(32000) := NULL;

 

  -- p3 -> __lsAttr

  p3_ VARCHAR2(32000) := 'CF$_DATA' || chr(31) || 'Example' || chr(30);

 

  p4_ VARCHAR2(32000) := 'DO';

begin

  utl_http.set_proxy('http://{proxyYourDatabaseHosted}');

  v_body := '{"name":"Example"}';

 

  v_req := utl_http.begin_request('http://www.url.com/',

                                  'POST');

 

  utl_http.set_header(v_req, 'content-type', 'application/json');

  utl_http.set_header(v_req,

                      'X-AUTH-TOKEN',

                      '{YourToken}');

  utl_http.set_header(v_req, 'Content-Length', length(v_body));

 

  -- Invoke REST API.

  utl_http.write_text(v_req, v_body);

 

  -- Get response.

  v_res := utl_http.get_response(v_req);

 

 begin

    loop

      utl_http.read_line(v_res, v_buffer);

 

      IFSSIM.DATA_CLP.NEW__(p0_, p1_, p2_, p3_, p4_);

      -- Do something with buffer.

      dbms_output.put_line(v_buffer);      

 

   commit;

    end loop;

    utl_http.end_response(v_res);

  exception

    when utl_http.end_of_body then

      utl_http.end_response(v_res);

  end;

end;

 

 

 

BR | Lucas Moura

Reply


Terms & ConditionsCookie settings