Parsing a large XML then insert using forall

you have several syntax errors

1. l_row_index Number:=0 should start from 1.
2. (l_temp_nd, '/Row/EMPNO/text()', NULL); makes no sense. you have to extract from the xmltype here.
3. missing index on the FORALL on the last column.
4. to_date needed on the date field.

a basic fix:

SQL> create table users(EMPNO NUMBER(4),SAL NUMBER(4),HIREDATE DATE);

Table created.

SQL> Declare
  2    TYPE import_q_rec IS RECORD
  3                             (EMPN          dbms_sql.NUMBER_TABLE,
  4                             SALN           dbms_sql.NUMBER_TABLE,
  5                             HIREDATE       dbms_sql.Date_Table
  6                              );
  7    l_import_q_tab import_q_rec;
  8    l_row_index      Number:=1;
  9    l_temp_nd        XMLTYPE;
 10    l_result_xml         XMLTYPE:=XmlType('<ROWSET>
 11                                              <ROW>
 12                                             <EMPNO>2290</EMPNO>
 13                                             <SAL>2000</SAL>
 14                                             <HIREDATE>31-DEC-1992</HIREDATE>
 15                                           </ROW>
 16                                              <ROW>
 17                                             <EMPNO>111</EMPNO>
 18                                             <SAL>123</SAL>
 19                                             <HIREDATE>30-NOV-1992</HIREDATE>
 20                                           </ROW>
 21                                         </ROWSET>');
 22  begin
 23    WHILE l_result_xml.Existsnode('/ROWSET/ROW[' || To_Char(l_row_index) || ']') > 0
 24    LOOP
 25      l_temp_nd :=l_result_xml.Extract('/ROWSET/ROW[' || To_Char(l_row_index) || ']');
 26      l_import_q_tab.EMPN(l_row_index) := l_temp_nd.extract('/ROW/EMPNO/text()').getstringval();
 27      l_import_q_tab.SALN(l_row_index) :=  l_temp_nd.extract('/ROW/SAL/text()').getstringval();
 28      l_import_q_tab.HIREDATE(l_row_index) := to_date(l_temp_nd.extract('/ROW/HIREDATE/text()').getstringval(), 'dd-mon-yyyy');
 29      l_row_index := l_row_index + 1;
 30    END LOOP;
 31
 32  FORALL i IN 1..l_row_index-1
 33      INSERT INTO users
 34    VALUES
 35    (l_import_q_tab.EMPN(i), l_import_q_tab.SALN(i),l_import_q_tab.HIREDATE(i));
 36  End;
 37  /

PL/SQL procedure successfully completed.

SQL> select * from users;

     EMPNO        SAL HIREDATE
---------- ---------- ---------
      2290       2000 31-DEC-92
       111        123 30-NOV-92

a simplified re-write to avoid all the array + bulk processing:

SQL> declare
  2    l_result_xml         XMLTYPE:=XmlType('<ROWSET>
  3                                              <ROW>
  4                                             <EMPNO>2290</EMPNO>
  5                                             <SAL>2000</SAL>
  6                                             <HIREDATE>31-DEC-1992</HIREDATE>
  7                                           </ROW>
  8                                              <ROW>
  9                                             <EMPNO>111</EMPNO>
 10                                             <SAL>123</SAL>
 11                                             <HIREDATE>30-NOV-1992</HIREDATE>
 12                                           </ROW>
 13                                         </ROWSET>');
 14  begin
 15
 16    insert into users
 17    select /*+ cursor_sharing_exact */
 18           empno, sal, to_date(hiredate, 'dd-mon-yyyy')
 19      from xmltable('/ROWSET/ROW' passing l_result_xml
 20                    columns
 21                      empno number path 'EMPNO',
 22                      sal number path 'SAL',
 23                      hiredate varchar2(20) path 'HIREDATE'
 24                   );
 25
 26  end;
 27  /

PL/SQL procedure successfully completed.

SQL> select * from users;

     EMPNO        SAL HIREDATE
---------- ---------- ---------
      2290       2000 31-DEC-92
       111        123 30-NOV-92