issue looping over clob data in pl/sql. invalid type when setting variable to current iteration

Question

I am currently using pl/sql to attempt to loop over some data in a clob column from a table. When I use just a select query and do the operations to the clob data, the procedure finishes just fine. Here is that example:

DECLARE
  l_xml Clob;
  type fieldIds is table of number;
  isIdInTable Number;
BEGIN
select XML_DATA into l_xml from layout where layout_id=801;

l_xml := XML_DATA;

  FOR cur_rec IN (
    SELECT xt.*
    FROM   XMLTABLE('*//field'
             PASSING XMLTYPE(l_xml)
             COLUMNS 
               fieldId     VARCHAR2(4000)  PATH '@id'
             ) xt)
  LOOP
    DBMS_OUTPUT.put_line('id=' || cur_rec.fieldId );
    select count(1) into isIdInTable from tempCustomFieldDefTable where USEDID = cur_rec.fieldId;

    if cur_rec.fieldId is not null then
    if isIdInTable = 0 then
    insert into tempCustomFieldDefTable values(cur_rec.fieldId);
    end if;
    end if;
  END LOOP;
END;

However, when I attempt to loop over a collection of clobs using the following, I receive this error: PLS-00382: expression is of wrong type upon setting the variable l_xml to the current iteration, XML_DATA. I have attempted to find the difference between the "into" syntax and setting a variable in pl/sql and have not been able to find the answer. Additionally, I have attempted to cast the XML_DATA as a VARCHAR2 as well as using to_clob.

DECLARE
  l_xml CLOB;
  type fieldIds is table of number;
  isIdInTable Number;
BEGIN

for xData in (select XML_DATA from layout where owner=85)
Loop

l_xml := xData;
  FOR cur_rec IN (
    SELECT xt.*
    FROM   XMLTABLE('*//field'
             PASSING XMLTYPE(l_xml)
             COLUMNS 
               fieldId     VARCHAR2(4000)  PATH '@id'
             ) xt)
  LOOP
    DBMS_OUTPUT.put_line('id=' || cur_rec.fieldId );
    select count(1) into isIdInTable from tempCustomFieldDefTable where USEDID = cur_rec.fieldId;

    if cur_rec.fieldId is not null then
    if isIdInTable = 0 then
    insert into tempCustomFieldDefTable values(cur_rec.fieldId);
    end if;
    end if;
  END LOOP;
End loop;  
END;

How can I correctly loop over a collection of clobs using the for X in syntax? Or is there a better way to do this?

Answer 1

The below statement populates a record variable, this record variable contains all the fields in the select (in this case just the one XML_DATA).

for xData in (select XML_DATA from layout where owner=85)

So what you want in the assignment is

l_xml := xData.XML_DATA;