I am having a strange problem that has appeared all of a sudden.
When using APEX_WEB_SERVER.MAKE_REST_REQUEST for a custom authentication process, I am sometimes getting the ORA-01841 error. This happens consistently for some users, but not others. The entire REST call fails when this error is encountered.
I am using APEX 22.2 and ORA19c.
I have set up a test script that makes the REST call, and all I do is change the UserID and PW - no other changes.
For certain users, the call is successful, but for others, I can't even log the error because the MAKE_REST_REQUEST fails, with no JSON payload returned.
Is this a known bug/issue? I have searched the web and cannot find any reference to this problem.
The procedure I'm using for testing/debugging is:
create or replace PROCEDURE DMC_P_AUTHENTICATE_USER_FIX (
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_token IN CHAR,
p_application_name IN VARCHAR2,
p_session_id IN INTEGER,
p_tfa_server_name IN VARCHAR2,
p_out_ff_return OUT CLOB
)
AS
l_first_factor_return CLOB;
l_second_factor_return CLOB;
l_first_factor_body CLOB;
l_second_factor_body CLOB;
l_first_factor_authenticated VARCHAR2(25) := 'N';
l_second_factor_authenticated VARCHAR2(25) := 'N';
l_membership_count INTEGER := 0;
l_exists BOOLEAN;
l_is_member VARCHAR2(10) := 'No';
l_session_id INTEGER := p_session_id;
l_customviewroles CLOB := '';
l_email_address VARCHAR(255) := '';
l_cvr_start INTEGER := 0;
login_failed EXCEPTION;
BEGIN
l_first_factor_body := '{"userName":"' || p_username || '", "token":"' || p_password || '", "appName":"apex"}';
l_second_factor_body := '{"userName":"' || p_username || '", "tokenNext":"' || p_token || '"}';
l_customviewroles := 'Not yet retrieved';
--This is debug log code 1 that will be removed
INSERT INTO dmc_tfa_clob (user_name, l_first_factor_body, l_second_factor_body, l_customviewroles)
VALUES (p_username, l_first_factor_body, l_second_factor_body, l_customviewroles);
COMMIT;
--End debug code 1
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
l_first_factor_return := apex_web_service.make_rest_request(
p_url => 'https://' || p_tfa_server_name || '/dmcDataServices/auth',
p_http_method => 'POST',
p_body => l_first_factor_body,
p_transfer_timeout => 25
);
l_customviewroles := '{' || SUBSTR(l_first_factor_return, INSTR(l_first_factor_return, '"customViewRoles"'), LENGTH(l_first_factor_return));
--This is debug log code 2 that will be removed
INSERT INTO dmc_tfa_clob (user_name, l_first_factor_body, l_second_factor_body, l_customviewroles, l_first_factor_return)
VALUES (p_username, l_first_factor_body, l_second_factor_body, l_customviewroles, l_first_factor_return);
COMMIT;
--End debug code 2
-- Assign the value to the OUT parameter
p_out_ff_return := l_first_factor_return;
END DMC_P_AUTHENTICATE_USER_FIX;
To call the procedure from SQL Developer, I am using:
DECLARE
p_userid varchar2(255);
p_pw varchar2(255);
v_sqlerrm VARCHAR2(4000);
p_clob_out CLOB;
v_offset NUMBER := 1;
v_chunk_size NUMBER := 32767;
v_chunk VARCHAR2(32767);
BEGIN
--Uncomment only one line below:
p_userid := 'USER1'; p_pw := 'PW1';
--p_userid := 'USER2; p_pw := 'PW2';
--p_userid := 'USER3'; p_pw := 'PW3;
-- Call the procedure
DMCS_P_AUTHENTICATE_USER_FIX(p_userid, p_pw, '', 'apex', 123456, 'server:port goes here', p_clob_out);
-- Output the CLOB in chunks
IF p_clob_out IS NOT NULL THEN
WHILE v_offset <= DBMS_LOB.getlength(p_clob_out) LOOP
v_chunk := DBMS_LOB.substr(p_clob_out, v_chunk_size, v_offset);
DBMS_OUTPUT.PUT_LINE(v_chunk);
v_offset := v_offset + v_chunk_size;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('CLOB is NULL');
END IF;
-- Get detailed SQL error message
v_sqlerrm := sqlerrm;
-- Print the detailed SQL error message
DBMS_OUTPUT.PUT_LINE('Detailed SQL Error Message: ' || v_sqlerrm);
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := sqlerrm;
DBMS_OUTPUT.PUT_LINE('Detailed SQL Error Message: ' || v_sqlerrm);
RAISE;
END;
When the call is made to DMCS_P_AUTHENTICATE_USER_FIX, the point of failure is the MAKE_REST_REQUEST call in the procedure, and the whole thing stops, returning the following error:
Error report -
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at line 41
ORA-06512: at "APEX_220200.WWV_FLOW_WEB_SERVICES", line 1230
ORA-06512: at "SYS.UTL_HTTP", line 904
ORA-06512: at "SYS.UTL_HTTP", line 1379
ORA-06512: at "APEX_220200.WWV_FLOW_WEB_SERVICES", line 659
ORA-06512: at "APEX_220200.WWV_FLOW_WEB_SERVICES", line 1222
ORA-06512: at "APEX_220200.WWV_FLOW_WEB_SERVICES", line 1419
ORA-06512: at "APEX_220200.WWV_FLOW_WEBSERVICES_API", line 568
ORA-06512: at "APEXPROD.DMC_P_AUTHENTICATE_USER_FIX", line 45
ORA-06512: at line 17
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
Again, this error is intermittent between users (some users successfully authenticate, others cannot), but for each user, the result is consistent (either they're always successful or always get the failure).
Any help getting to the bottom of this would be much appreciated.
Thanks!