Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare

  messageClob clob;
  log_id number;

  procedure getMyReq(p_messageClob clob, p_logId number) is
    logHistoryId number;
  begin

    if p_messageClob is not null then
      dbms_output. put_line(p_messageClob);

      for message In (select * from xmltable(
                        xmlnamespaces(default 'http://xxx/xx/x'),
                        'myReq' passing xmltype(p_messageClob)
                        columns
                          signature varchar2(50) path 'signature',
                          status varchar2(10) path 'status',
                          mydate date path 'mydate')) 
      loop
        dbms_output.put_line(message.signature || ', ' || message.status|| ', ' || message.mydate );
       
      end loop;
    end if;
--  exception
--    ...some code
  end getMyReq;
 
begin
  log_id := 12; --for testing
  messageClob := to_clob(
  '
      <myReq xmlns="http://xxx/xx/x" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://xxx/xx/x file:/C:/Users/User1/Documents/test.xsd">
         <signature>HelloWorld</signature>
         <status>End</status>
         <mydate>2005-03-29T13:24:36.000</mydate>
      </myReq >
  ');
  getMyReq(p_messageClob => messageClob,
                  p_logId       => log_id);
end;
/


dbms_output:

      <myReq xmlns="http://xxx/xx/x" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://xxx/xx/x file:/C:/Users/User1/Documents/test.xsd">
         <signature>HelloWorld</signature>
         <status>End</status>
         <mydate>2005-03-29T13:24:36.000</mydate>
      </myReq >
  

ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 13
ORA-06512: at line 13
ORA-06512: at line 40

select to_date('2005-03-29T13:24:36.000', 'YYYY-MM-DD') from dual;
ORA-01830: date format picture ends before converting entire input string

select to_timestamp('2005-03-29T13:24:36.000', 'YYYY-MM-DD"T"HH24:MI:SS.FF3') from dual;


TO_TIMESTAMP('2005-03-29T13:24:36.000','YYYY-MM-DD"T"HH24:MI:SS.FF3')


29-MAR-05 13.24.36.000000000


declare

  messageClob clob;
  log_id number;

  procedure getMyReq(p_messageClob clob, p_logId number) is
    logHistoryId number;
  begin

    if p_messageClob is not null then
      dbms_output. put_line(p_messageClob);

      for message In (select * from xmltable(
                        xmlnamespaces(default 'http://xxx/xx/x'),
                        'myReq' passing xmltype(p_messageClob)
                        columns
                          signature varchar2(50) path 'signature',
                          status varchar2(10) path 'status',
                          mydate timestamp path 'mydate')) 
      loop
        dbms_output.put_line(message.signature || ', ' || message.status|| ', ' || message.mydate );
       
      end loop;
    end if;
--  exception
--    ...some code
  end getMyReq;
 
begin
  log_id := 12; --for testing
  messageClob := to_clob(
  '
      <myReq xmlns="http://xxx/xx/x" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://xxx/xx/x file:/C:/Users/User1/Documents/test.xsd">
         <signature>HelloWorld</signature>
         <status>End</status>
         <mydate>2005-03-29T13:24:36.000</mydate>
      </myReq >
  ');
  getMyReq(p_messageClob => messageClob,
                  p_logId       => log_id);
end;
/
1 rows affected

dbms_output:

      <myReq xmlns="http://xxx/xx/x" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://xxx/xx/x file:/C:/Users/User1/Documents/test.xsd">
         <signature>HelloWorld</signature>
         <status>End</status>
         <mydate>2005-03-29T13:24:36.000</mydate>
      </myReq >
  
HelloWorld, End, 29-MAR-05 13.24.36.000000

declare

  messageClob clob;
  log_id number;

  procedure getMyReq(p_messageClob clob, p_logId number) is
    logHistoryId number;
  begin

    if p_messageClob is not null then
      dbms_output. put_line(p_messageClob);

      for message In (select * from xmltable(
                        xmlnamespaces(default 'http://xxx/xx/x'),
                        'myReq' passing xmltype(p_messageClob)
                        columns
                          signature varchar2(50) path 'signature',
                          status varchar2(10) path 'status',
--                          mydate date path 'mydate')) 
                          mydate varchar2(23) path 'mydate')) 
      loop
        dbms_output.put_line(message.signature || ', ' || message.status|| ', ' || message.mydate );
       
      end loop;
    end if;
--  exception
--    ...some code
  end getMyReq;
 
begin
  log_id := 12; --for testing
  messageClob := to_clob(
  '
      <myReq xmlns="http://xxx/xx/x" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://xxx/xx/x file:/C:/Users/User1/Documents/test.xsd">
         <signature>HelloWorld</signature>
         <status>End</status>
         <mydate>2005-03-29T13:24:36.000</mydate>
      </myReq >
  ');
  getMyReq(p_messageClob => messageClob,
                  p_logId       => log_id);
end;
/
1 rows affected

dbms_output:

      <myReq xmlns="http://xxx/xx/x" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://xxx/xx/x file:/C:/Users/User1/Documents/test.xsd">
         <signature>HelloWorld</signature>
         <status>End</status>
         <mydate>2005-03-29T13:24:36.000</mydate>
      </myReq >
  
HelloWorld, End, 2005-03-29T13:24:36.000