I would like to know if there is a way to list all groups and their users at once.
I wrote this code here and I want to know if I can change something in the URL to get all users and groups?
SET SERVEROUTPUT ON;
DECLARE
L_HTTP_REQUEST UTL_HTTP.REQ;
L_HTTP_RESPONSE UTL_HTTP.RESP;
L_TEXT VARCHAR2(32767);
RESP clob:='';
L_JSON_OBJ JSON_OBJECT_T;
L_JSON_IN JSON_ARRAY_T;
v_userid varchar2(100);
v_username varchar2(100);
v_email varchar2(100);
v_statuts varchar2(100);
BEGIN
--SET WALLET PATH
UTL_HTTP.SET_WALLET('file:c:/okta_wallet_base/',NULL);
--L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST('https://www.okta.com/','GET','HTTP/1.1');
-- L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST('https://<account_name>.okta.com/api/v1/users/','GET','HTTP/1.1');
L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST('https://<account_name>.okta.com/api/v1/apps/0oa5mg1q0x5rVLsBd5d7/users','GET','HTTP/1.1');
--L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST('https://https://<account_name>.okta.com/oauth2/default ','GET','HTTP/1.1'); test
-- SET HEADERS
UTL_HTTP.SET_HEADER(l_http_request, 'content-type', 'application/json');
UTL_HTTP.SET_HEADER(l_http_request, 'authorization', 'SSWS <api key>');
UTL_HTTP.SET_HEADER(l_http_request, 'Accept', 'application/json');
--UTL_HTTP.SET_HEADER(l_http_request, 'authorization', 'bearer <i3PcSBl6_4fKbd9okEMSlQiEJ5SWE94V6VyKiINr from sk_test_%>');
-- GET RESPONSE AND STORE IN A VARIABLE
L_HTTP_RESPONSE := UTL_HTTP.GET_RESPONSE(L_HTTP_REQUEST);
DBMS_OUTPUT.PUT_LINE('HTTP response status code =>' || L_HTTP_RESPONSE.status_code);
DBMS_OUTPUT.PUT_LINE('HTTP response reason phrase => ' || L_HTTP_RESPONSE.reason_phrase);
-- LOOP THROUGH THE RESPONSE.
BEGIN
LOOP
UTL_HTTP.READ_TEXT(L_HTTP_RESPONSE, L_TEXT, 32766);--
RESP:=RESP||L_TEXT;
END LOOP;
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(L_HTTP_RESPONSE);
END;
--DBMS_OUTPUT.PUT_LINE(RESP);
L_JSON_IN := json_array_T(RESP);
FOR indx IN 0 .. L_JSON_IN.get_size - 1 LOOP
L_JSON_OBJ := treat (L_JSON_IN.get(indx) as JSON_OBJECT_T);
v_userid:= L_JSON_OBJ.get_object('profile').get_string('lastName') ;
v_username:= L_JSON_OBJ.get_object('profile').get_string('login');
v_email:= L_JSON_OBJ.get_object('profile').get_string('email');
v_statuts:=L_JSON_OBJ.get_string('status');
--DBMS_OUTPUT.PUT_LINE(v_userid || ' '|| v_username || ' '|| v_email ||' '||v_statuts);
-- insert data to table
--INSERT INTO OKTA_USERS (USERID,USERNAME,EMAIL,STATUTS) values (v_userid , v_username, v_email ,v_statuts);
END LOOP;
EXCEPTION
when others then begin
UTL_HTTP.END_RESPONSE(L_HTTP_RESPONSE);
DBMS_OUTPUT.PUT_LINE('ERROR →'|| SQLERRM);
END;
END;
/