DECLARE
		l_body     CLOB;
		l_response CLOB;

        l_wallet_path varchar2(1000) := 'file:'||CCM_INT_API_PKG.GET_DB_DIRECTORY_PATH('DATA_PUMP_DIR');
        l_api_sub_get_url varchar2(500) := CCM_INT_API_PKG.G_CACM_API_BASE_URL||'subscriber/';

        l_preamble varchar2(10);
        l_subscriber_id varchar2(20);

        l_api_login_url varchar2(500) := 'https://cacm-api-stage.ascend/auth/v2/login';
        l_client_id      VARCHAR2(32767) := 'ex*********vc';
        l_client_secret  VARCHAR2(32767) := '20*********J';

        l_access_token VARCHAR2(4000);
        l_refresh_token VARCHAR2(4000);
        l_status VARCHAR2(100);
        l_transactionId VARCHAR2(100);
	BEGIN

		/******** Get Access Token ***********/
        /****** Mandatory Headers ***********/
		apex_web_service.clear_request_headers;
		apex_web_service.g_request_headers(1).name  := 'accept';
		apex_web_service.g_request_headers(1).value := '*/*';
        apex_web_service.g_request_headers(2).name  := 'Content-Type';
		apex_web_service.g_request_headers(2).value := 'application/json';
	 
		DBMS_OUTPUT.put_line('API URL: '||l_api_login_url);
        
        l_response := apex_web_service.make_rest_request (
		   p_url         => l_api_login_url,
		   p_http_method => 'POST',
           --p_credential_static_id => 'CIS_CACM_REST_API_CRED',
           p_username      => l_client_id,
           p_password  => l_client_secret,
		   p_wallet_path => l_wallet_path);

        SELECT jt.access_token, jt.refresh_token, jt.status, jt.transactionId
        INTO l_access_token, l_refresh_token, l_status, l_transactionId
        FROM JSON_TABLE(
            l_response,
            '$'
            COLUMNS (
                access_token   VARCHAR2(4000) PATH '$.access_token',
                refresh_token  VARCHAR2(4000) PATH '$.refresh_token',
                status         VARCHAR2(100)  PATH '$.status',
                transactionId  VARCHAR2(100)  PATH '$.transactionId'
            )
        ) jt;

        DBMS_OUTPUT.put_line('Response Body: ');
        DBMS_OUTPUT.PUT_LINE('Access Token: ' || l_access_token);
        DBMS_OUTPUT.PUT_LINE('Refresh Token: ' || l_refresh_token);
        DBMS_OUTPUT.PUT_LINE('Status: ' || l_status);
        DBMS_OUTPUT.PUT_LINE('Transaction ID: ' || l_transactionId);
		
        DBMS_OUTPUT.PUT_LINE('     Full Response: ' || l_response);

		/******* Call Subscriber GET API ************/
		/****** Mandatory Headers ***********/
		apex_web_service.clear_request_headers;
		apex_web_service.g_request_headers(1).name  := 'accept';
		apex_web_service.g_request_headers(1).value := '*/*';
		apex_web_service.g_request_headers(2).name  := 'Authorization';
		apex_web_service.g_request_headers(2).value := 'Bearer '||l_access_token;

		l_preamble := 'BAK1';
		l_subscriber_id := '1234567';

		l_response := apex_web_service.make_rest_request (
			   p_url         => l_api_sub_get_url||l_preamble||'/'||l_subscriber_id,
			   p_http_method => 'GET',
			   p_wallet_path => l_wallet_path);
	   
		/********** Response in JSON Format: *********************/
		dbms_output.put_line('Subscriber GET Response: '||l_response);
	   
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('Error : '||SQLERRM);
			RAISE;
	END;
	
Output
---------------------------
API URL: https://cacm-api-stage.ascend/auth/v2/login
Response Body: 
Access Token: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6InVzcmJfc3ZjIiwic3ViIjoibXl2YWxpZGF0aW9ua2V5IiwiaWF0IjoxNzM4NTg2MjM4LCJleHAiOjE3Mzg1ODgwMzh9.4w2d0HrOh_KVkXLrkUbuIYmKXVvwK27slDKxUmsdHeY
Refresh Token: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6InVzcmJfc3ZjIiwiaWF0IjoxNzM4NTg2MjM4LCJleHAiOjE3Mzg1OTcwMzh9.zs6E1OagskiEsb_EDofi4gAn_VhUjMvdw1WfpMlnr4k
Status: success
Transaction ID: 9adf20c0-e22b-11ef-a5d9-db67e10924bf
     Full Response: {"access_token":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6InVzcmJfc3ZjIiwic3ViIjoibXl2YWxpZGF0aW9ua2V5IiwiaWF0IjoxNzM4NTg2MjM4LCJleHAiOjE3Mzg1ODgwMzh9.4w2d0HrOh_KVkXLrkUbuIYmKXVvwK27slDKxUmsdHeY","refresh_token":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6InVzcmJfc3ZjIiwiaWF0IjoxNzM4NTg2MjM4LCJleHAiOjE3Mzg1OTcwMzh9.zs6E1OagskiEsb_EDofi4gAn_VhUjMvdw1WfpMlnr4k","status":"success","token_type":"Bearer","expires_in":3600,"transactionId":"9adf20c0-e22b-11ef-a5d9-db67e10924bf","error":null}
Subscriber GET Response: {"data":{"preamble":"BAK1","preambleName":"CBR-ANCHORAGE (INTEGRATED)","subscriberName":"LAKEVILLE POLICE DEPT","abbreviatedName":"LAKEVILLE","companyId":1,"branchNumber":"1234","branchName":"1234","subLegalDesig":"","businessCategory":"111","kobDesc":"SECURITY","dataDisplayLevel":"01","crKobCode":"01","lastActivityDate":"2024-11-20","subcodeDletDate":"2024-11-20","subAddedDate":"2024-11-20","updateUserid":"CACMUSR1","companyName":"BANKAMERICA CORP","subTypeDesc":"REGULAR ADMIN SUBSCRIBER","subStatusDesc":"DELETE (5 YEAR RETENTION)","companyDivName":"","serviceAreaDesc":"INTEGRATED AFFILIATES-HSE RPTG","subscriberType":"A","kob":"01","companyDivisionId":0,"overrideInquiryDisplayLevel":"","status":"D","statusEffectiveDate":"2002-03-22","futureStatus":"A","futureStatusDate":"2002-03-22","jointSubscriberInd":"2","jointSubscriberPreamble":"BAK1","jointSubscriberName":"LAKEVILLE POLICE DEPT","primaryServiceArea":"012","secondaryServiceArea":"C01","legalDesignator":"","CEMProfileID":"","startDate":"","businessCatDesc":"","lastUpdatedDate":"2024/11/20","lastUpdatedTime":"13:46:17"},"status":"success","transactionId":"9b8301e0-e22b-11ef-a5d9-db67e10924bf","error":null}

Statement processed.

1.82 seconds