What Info is Available from my APEX Social Sign-In Provider?

Sorry for the brevity, but I hope this helps someone. If you have configured APEX Social Sign-In with Google, Facebook, Okta, Azure--pick your poison--you may have struggled with figuring out what user information is available to you. If you know the name of the value you are looking for, it is pretty easy to get, but figuring out just what is being returned is not documented anywhere (at least not that I can find). So, here is what you can do...

  • Create an application item A_SOCIAL_INFO
  • Edit your Social Sign-In authentication scheme and add the following pl/sql

procedure post_authentication is

l_index varchar2(32767);
l_kind number;
l_value varchar2(32767);
begin

l_index := apex_json.g_values.first;

:A_SOCIAL_INFO := '-- Start --
';

for i in 1..(apex_json.g_values.count) loop
l_kind := apex_json.g_values(l_index).kind ;
if l_kind = 4 then
l_value := apex_json.g_values(l_index).number_value;
elsif l_kind = 5 then
l_value := apex_json.g_values(l_index).varchar2_value;
else
l_value := null;
end if;
:A_SOCIAL_INFO := :A_SOCIAL_INFO || l_index || ': ' || l_kind || ':' || l_value || '

';
l_index := apex_json.g_values.next(l_index);
end loop;

:A_SOCIAL_INFO := :A_SOCIAL_INFO || '-- End --
';

exception
when others then
:A_SOCIAL_INFO := :A_SOCIAL_INFO || ' err: ' ||sqlerrm;

end;

  • Add post_authentication as your Post-Authentication Procedure Name
  • Create a region on a page with region source &A_SOCIAL_INFO.

Run the application with a new session. Review the results on your page.

I have noticed that some IdP's will not send information unless you explicitly request it as an additional attribute. In your AuthN scheme, add name,address to the Additional User Attributes.

You can then use apex_json.get_varchar2 to get any values based upon what you see as names in the result.

I realize this is brief and lacks any pictures. If you have questions, feel free to leave a comment.

p.s. I'll try to get specific posts up soon for Azure, Google, Facebook, Okta, etc.