Oracle APEX call Dialogflow V2 in PL/SQL - Authentication with OAuth2 or Google Service Account

Question

Migrating from Dialogflow V1 means that embedding the API key inside of client-side javascript no longer works. Instead, authentication is required with a Google Service Account (or OAuth works too? Still unclear on this).

Since the chatbot is on Blogger, there is no easy way to add server side code which would use the Google Cloud SDKs to authenticate.

So, the requests need to be routed through our Oracle APEX server, which needs to authenticate itself with Dialogflow. However, I am having difficulties with the following procedures:

https://docs.oracle.com/database/apex-5.1/AEAPI/OAUTH_AUTHENTICATE-Function.htm

https://docs.oracle.com/database/apex-18.1/AEAPI/MAKE_REST_REQUEST-Function.htm

oauth_authenticate fails with the error

s_internal_error: true
apex_error_code: APEX.REGION.UNHANDLED_ERROR
ora_sqlcode: -20001
ora_sqlerrm: ORA-20001: Authentication failed. ORA-06512

Here is the overall code:

create or replace procedure "TEST_DIALOGFLOW"
is
begin
 begin DECLARE
  l_response_clob         CLOB;
  l_rest_url              VARCHAR2(1000);
  l_token_url             VARCHAR2(1000);
  l_count_posted          PLS_INTEGER;
BEGIN
  l_rest_url  := 'https://dialogflow.googleapis.com/v2/projects/<project>/agent/sessions/0:detectIntent';
  l_token_url := 'https://accounts.google.com/o/oauth2/token';

  apex_web_service.oauth_authenticate(
    p_client_id            => <client id>,
    p_client_secret        => <client secret>,
    p_token_url            => l_token_url,
    p_wallet_path          => <wallet path>,
    p_wallet_pwd           => <wallet password>
  );

  l_response_clob := apex_web_service.make_rest_request(
    p_url                  => l_rest_url,
    p_http_method          => 'POST',
    p_scheme               => 'OAUTH_CLIENT_CRED',
    p_body                 => '{"queryInput":{"text":{"text":"test","languageCode":"en"}}}',
    p_wallet_path          => <wallet path>,
    p_wallet_pwd           => <wallet password>
  );

  htp.p('HTTP Status Code: '||apex_web_service.g_status_code);
  IF apex_web_service.g_status_code = 200 THEN
    apex_json.parse(p_source => l_response_clob, p_strict => true);
  END IF;
END;
end;
end;

I suspect that the token URL is incorrect. Or that there is a simpler way to handle this.

I also have a suspicion about the OAuth setup inside of GCloud. Do these have to be filled out?

Also, I am unclear on a Google Service Account vs. OAuth. Right now, the setup is for an OAuth key I made for Dialogflow. However, the recommended is with a service account, but I am unsure how to use the above procedures to login with those credentials.

Try going into the Shared Components > Web Credentials and create a credential of type OAuth2 Client Credentials Flow. Then skip the call to OAUTH_AUTHENTICATE and just call MAKE_REST_REQUEST using the p_credential_static_id and p_token_url parameters. — Dan McGhan

Rafmg8 Rafmg8 · Accepted Answer · 2020-03-19T13:28:47

You will need to use OAuth if you want to access the user data for the application, such as user watch time on YouTube.

By reading your code and comment, if I am not mistaken, you are implementing the chatbot on Google Blogger, right? If this is the case, you can code PHP to simplify the procedure. You can embed <iframe> and link to your own server, your server side code can be done in your server. I hope this helps.

Oracle APEX call Dialogflow V2 in PL/SQL - Authentication with OAuth2 or Google Service Account

1 Answers