APEX

when i try to create rest data source where rest data source type is simple http or oracle rest data source

in discovery step genertes error is ORA-20987: Error at line 1, col 1: Unexpected character "<"

when try to execute get operation. my restful api source in sql commands generates json object without problem

i authentiacte with oauth2 with client credintial and i apply that in creating data source

my api source

declare
--arb_name housing_view.arb_name%type ;
meal_record daily_trans%rowtype ;
l_cursor SYS_REFCURSOR ;
l_date date ;
j_meal_kind varchar2(50) ;
j_meal_type varchar2(25) ;
j_meal_exception varchar2(50) ;
j_time varchar2(40) ;
j_payroll varchar2(10) ;
count_rec number ;
vempno varchar2(5);
vemptype varchar2(1) ;
vtdate varchar2(10);
TYPE emp_detail_type IS RECORD
(
arb_name housing_view.arb_name%type,
hour number(3,0),
min number(3,0),
meal_kind varchar2(1),
meal_type varchar2(3),
except_meal varchar2(1)
);
emp_detail_rec emp_detail_type ;

begin

l\_date := to\_date(:tdate,'yyyy-MM-dd')  ;  
    -- Open a cursor to fetch the transactions for the given employee  

vempno := :empno;
vemptype := :emptype ;
vtdate := :tdate ;
OPEN l_cursor FOR
SELECT arb_name,hour,min,meal_kind,meal_type,except_meal
FROM daily_trans,housing_view where
g_payroll = nvl(vempno,'09005') and type = nvl(vemptype,'G') and to_char(meal_date,'yyyy-MM-dd') = nvl(vtdate,'2025-04-27') and housing_view.payno = g_payroll and daily_trans.type = housing_view.emp_type ;

-- Loop through the cursor and build the JSON response

   count\_rec := 0  ;   

LOOP
FETCH l_cursor INTO emp_detail_rec;
EXIT WHEN l_cursor%NOTFOUND;
count_rec := count_rec+1 ;
if emp_detail_rec.meal_kind = 'L' THEN
j_meal_kind :='غذاء';
END IF ;
if emp_detail_rec.meal_kind = 'T' THEN
j_meal_kind := 'وجبة إضافية';
END IF ;
if emp_detail_rec.meal_kind = 'B' THEN
j_meal_kind := 'إفطار';
END IF ;
if emp_detail_rec.meal_kind = 'D' THEN
j_meal_kind := 'عشاء';
END IF ;
if emp_detail_rec.meal_type = 'HOT' then
j_meal_type := 'ساخنة';
end if ;
if emp_detail_rec.meal_type = 'DRY' then
j_meal_type := 'جافة';
end if ;
if emp_detail_rec.except_meal <> 'Y' then
j_meal_exception := 'عادية' ;
end if ;
if emp_detail_rec.except_meal = 'Y' then
j_meal_exception := 'إستثنائية' ;
end if ;
j_time := ''||emp_detail_rec.hour||':'||emp_detail_rec.min ;
if count_rec = 1 then
APEX_JSON.initialize_output;
APEX_WEB_SERVICE.g_status_code :=200 ;
--SET CONTENT-TYpe header to application/json; charset = UTF-8
owa_util.mime_header('application/json;charset= UTF-8',FALSE) ;
APEX_JSON.open_object;
APEX_JSON.open_array('data');
end if ;
APEX_JSON.open_object;
j_payroll := :emptype||:empno ;
APEX_JSON.WRITE('EMPID', j_payroll);
APEX_JSON.WRITE('Name', emp_detail_rec.arb_name );
APEX_JSON.WRITE('Meal_Date',TO_CHAR(l_date,'DD/MM/YYYY')) ;
APEX_JSON.WRITE('Meal', j_meal_kind ) ;
APEX_JSON.WRITE('Meal_Type', j_meal_type ) ;
APEX_JSON.WRITE('Issued_Time', j_time ) ;
APEX_JSON.WRITE('Meal_Status', j_meal_exception ) ;
APEX_JSON.close_object;

END LOOP ;
if count_rec > 0 then

     APEX\_JSON.close\_all;  
       
      
         
     else   
        APEX\_WEB\_SERVICE.g\_status\_code :=204 ;  
        APEX\_JSON.initialize\_output;  
       owa\_util.mime\_header('application/json;charset= UTF-8',FALSE) ;  
        apex\_json.open\_object;        -- {  
       apex\_json.write('status', 204); --   "a":1   
       apex\_json.write('message', 'Employee has not meals in this date');  
       apex\_json.write('empno',:empno);  
       apex\_json.write('emptype', '\*\*\*\*' );  
       apex\_json.write('tdate','\*\*\*' );  
       apex\_json.close\_object;  

     end if ;  
 

     -- Close the cursor  
    CLOSE l\_cursor;

exception
when NO_DATA_FOUND THEN
apex_json.close_all;
APEX_WEB_SERVICE.g_status_code :=204 ;
APEX_JSON.initialize_output;
owa_util.mime_header('application/json;charset= UTF-8',FALSE) ;
apex_json.open_object; -- {
apex_json.write('status', 204); -- "a":1
apex_json.write('message', 'Employee has not meals in this date');
apex_json.write('empno','****');
apex_json.write('emptype', '*' );
apex_json.write('tdate','****' );
apex_json.close_object;
when others then

IF SQLCODE = -1031 THEN   
       APEX\_WEB\_SERVICE.g\_status\_code :=403 ;  
       APEX\_JSON.initialize\_output;  
       APEX\_JSON.open\_object;  
       APEX\_JSON.write('status', 403);  
       APEX\_JSON.write('message', 'You are not authorithed ');  
       APEX\_JSON.close\_object;  
     else   
         APEX\_JSON.initialize\_output;  
         APEX\_WEB\_SERVICE.g\_status\_code   := 400;   
         APEX\_JSON.open\_object;  
         APEX\_JSON.write('status', 400);  
         APEX\_JSON.write('message',sqlerrm );  
         APEX\_JSON.close\_object;  
     end if ;  

end ;