PL/SQL error on saving of Nested XML in table

Question

I am trying to save XML in a table. The XML has a format like the following:

<employees>
  <employees_id> 1</employees_id>
  <employees_name>Teradata</employees_name>
     <department_id>100</department_id>
     <department_name>Techinical</department_name>
     <department_id>200</department_id>
     <department_name>Management</department_name>
     <department_id>300</department_id>
     <department_name>Telecom</department_name>
  </employees>
  <employees>
  <employees_id> 2</employees_id>
  <employees_name>AT&T</employees_name>
     <department_id>400</department_id>
     <department_name>Techinical</department_name>
     <department_id>500</department_id>
     <department_name>Management</department_name>
     <department_id>600</department_id>
     <department_name>Telecom</department_name>
  </employees>

My table has only an id column and xmltype column.

The procedure which I am using is as follows:

   DECLARE
   CTX         DBMS_XMLGEN.CTXHANDLE;
   LCLOB$XML_AS_CLOB  CLOB;
   LREC$EMPLOYEES_EMPNO NUMBER;
 BEGIN
  FOR LREC$EMPLOYEES_EMPNO IN (198, 197, 180)
   LOOP
    --Create context for XML
    CTX :=
      DBMS_XMLGEN.NEWCONTEXT (
       'SELECT employees.EMPLOYEE_ID, employees.FIRST_NAME, 
        departments.DEPARTMENT_ID, departments.DEPARTMENT_NAME
        FROM EMPLOYEES, departments
        WHERE employees.department_id = departments.department_id and 
        employees.employees_id =' || LREC$EMPLOYEES_EMPNO);
    --Get XML file of Context
     -- Set the row header to be EMPLOYEE
    DBMS_XMLGEN.setRowTag(CTX, 'EMPLOYEE');
    LCLOB$XML_AS_CLOB := DBMS_XMLGEN.GETXML (CTX);
    DBMS_XMLGEN.CLOSECONTEXT (CTX); 
    INSERT INTO HR.OFFLINE_XML (id, XML_FILE)
       VALUES ( LREC$EMPLOYEES_EMPNO, XMLTYPE (LCLOB$XML_AS_CLOB));
    END LOOP;
   COMMIT;
 END;

Error report:

 ORA-06550: line 7, column 4:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

   * & - + / at mod remainder rem .. <an exponent (**)> ||
   multiset year day
ORA-06550: line 18, column 16:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national charac
ORA-06550: line 19, column 23:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national charac
ORA-06550: line 20, column 16:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national charac
ORA-06550: line 21, column 5:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
The symbol "b
ORA-06550: line 24, column 4:
PLS-00103: Encountered the symbol "COMMIT" when expecting one of the following:

   end not pragma final instantiable order overriding static
   member constructor map
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

As Noel said, you can't supply fixed values to a for loop like that. (All the other errors in your report are knock-ons from that, though there are other problems). You can supply a range, as for i in 1..10 loop, but that doesn't help you here, even for your sample data. As it's just for testing you can use a dummy cursor for now:

    FOR LREC$EMPLOYEES_EMPNO IN (
        SELECT 198 AS employee_id FROM dual
        UNION ALL SELECT 197 FROM dual 
        UNION ALL SELECT 180 FROM dual
    ) LOOP

(Although I actually prefer DazzaL's array, but I'll stick with this for now). You don't have to declare the cursor, and the number variable you declared with that name will be superseded by this version. And when you refer to one of the values it returns it has to be by name, so in this case LREC$EMPLOYEES_EMPNO.employee_id. Persoanlly I'd go for a shorter name *8-)

As DazzaL also mentioned you don't need the intermediate CLOB either. It also seems unnecessary to create and close the context each time around the loop, when you can use a bind variable instead.

This works and gets the same result as your version (suitable amended), but not the output you suggested you wanted in the question:

DECLARE
    ctx DBMS_XMLGEN.CTXHANDLE;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT(
       'SELECT employees.EMPLOYEE_ID, employees.FIRST_NAME, 
        departments.DEPARTMENT_ID, departments.DEPARTMENT_NAME
        FROM EMPLOYEES, departments
        WHERE employees.department_id = departments.department_id and      
        employees.employee_id = :id');
    DBMS_XMLGEN.setRowTag(ctx, 'EMPLOYEE');
    FOR r IN (
        SELECT 198 AS employee_id FROM dual
        UNION ALL SELECT 197 FROM dual 
        UNION ALL SELECT 180 FROM dual
    ) LOOP
        DBMS_XMLGEN.SETBINDVALUE(ctx, 'ID', r.employee_id);
        INSERT INTO OFFLINE_XML (id, xml_file)
        VALUES (r.employee_id, DBMS_XMLGEN.GETXMLTYPE(ctx));
    END LOOP;
    DBMS_XMLGEN.CLOSECONTEXT(ctx); 
END;
/

Which populates the table with:

        ID XML_FILE
---------- --------------------------------------------------
       198 <ROWSET>
             <EMPLOYEE>
               <EMPLOYEE_ID>198</EMPLOYEE_ID>
               <FIRST_NAME>Bob</FIRST_NAME>
               <DEPARTMENT_ID>1</DEPARTMENT_ID>
               <DEPARTMENT_NAME>First</DEPARTMENT_NAME>
             </EMPLOYEE>
           </ROWSET>

       197 <ROWSET>
             <EMPLOYEE>
               <EMPLOYEE_ID>197</EMPLOYEE_ID>
               <FIRST_NAME>Adam</FIRST_NAME>
               <DEPARTMENT_ID>1</DEPARTMENT_ID>
               <DEPARTMENT_NAME>First</DEPARTMENT_NAME>
             </EMPLOYEE>
           </ROWSET>

       180 <ROWSET>
             <EMPLOYEE>
               <EMPLOYEE_ID>180</EMPLOYEE_ID>
               <FIRST_NAME>Charlie</FIRST_NAME>
               <DEPARTMENT_ID>2</DEPARTMENT_ID>
               <DEPARTMENT_NAME>Second</DEPARTMENT_NAME>
             </EMPLOYEE>
           </ROWSET>