While I was preparing for the presentation at Amis Academy about CodeGen I created an example to build an XML file based on data in the table. I started out with a script based on the (old) DEPT table (yes, I know, it’s very Oracle 7 to use these old tables).

<?xml version=”1.0″ ?>
<DEPT>
  <DEPTROW>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
    <LOC>NEW YORK</LOC>
  </DEPTROW>
  <DEPTROW>
    <DEPTNO>20</DEPTNO>
    <DNAME>RESEARCH</DNAME>
    <LOC>DALLAS</LOC>
  </DEPTROW>
  <DEPTROW>
    <DEPTNO>30</DEPTNO>
    <DNAME>SALES</DNAME>
    <LOC>CHICAGO</LOC>
  </DEPTROW>
  <DEPTROW>
    <DEPTNO>40</DEPTNO>
    <DNAME>OPERATIONS</DNAME>
    <LOC>BOSTON</LOC>
  </DEPTROW>
</DEPT>

After a couple of iterations the script looked like this:

<?xml version=”1.0″ ?>
# First, define the query.
[DEFARRAY]tabcontents[AS]
select deptno deptno
     , dname dname
     , loc loc
from [OBJNAME];
[ENDDEFARRAY]
#
# Now use the array.
<[OBJNAME]>
[FOREACH]tabcontents
  <[OBJNAME]ROW>
    <DEPTNO>[DEPTNO]</DEPTNO>
    <DNAME>[DNAME]</DNAME>
    <LOC>[LOC]</LOC>
  </[OBJNAME]ROW>
[ENDFOREACH]
</[OBJNAME]>

But when I wanted to run this script against another table (EMP) I had to come up with smart queries to get the right data from the database.

<?xml version=”1.0″ ?>
# First, define the query.
[DEFARRAY]tabcontents[AS]
select [OBJNAME].*, rowid therowid
from [OBJNAME];
[ENDDEFARRAY]
#
# Now use the array.
<[OBJNAME]>
[FOREACH]tabcontents
  [SETALIAS]MyRowId[TO][therowid]
  [SETALIAS]MyTabName[TO][OBJNAME]
  <[OBJNAME]ROW>
  [DEFARRAY]tablayout[AS]
  select utc.COLUMN_NAME Column_Name
    from User_Tab_Columns utc
   where utc.TABLE_NAME = ‘{MyTabName}’;
  [ENDDEFARRAY]
  [FOREACH]tablayout
    [SETALIAS]MyColumnName[TO][Column_Name]
    [DEFARRAY]fieldvalue[AS]
    select {MyColumnName} value
      from {MyTabName}
     where rowid = ‘{MyRowId}’
    [ENDDEFARRAY]
    [FOREACH]fieldvalue
    <{MyColumnName}>[value]</{MyColumnName}>
    [ENDFOREACH]
  [ENDFOREACH]
  </[OBJNAME]ROW>
[ENDFOREACH]
</[OBJNAME]>

I ran this script against the DEPT table and it ran perfectly. Then I ran the script against the EMP table and it ran perfectly as well. But, when I wanted to run the script against both tables at once the script came up with errors. It seems that the views (DEFARRAY creates a view in the database) weren’t refreshed. Luckily I had some custom code in place to remove the views from the database.

FUNCTION dropview
  RETURN NUMBER
IS
BEGIN
  EXECUTE IMMEDIATE ‘drop view ‘ || v_createdviewname;
  RETURN 0;
END dropview;

After cleaning up the views at the end of the script, by adding some extra code to the script, it solved my problem.

#Clean up the views created (using custom code ;-))
[SET]qe_qcgu.v_createdviewname[TO]fieldvalue
[SETALIAS]_dummy[TO]{qe_qcgu.dropview}
[SET]qe_qcgu.v_createdviewname[TO]tablayout
[SETALIAS]_dummy[TO]{qe_qcgu.dropview}
[SET]qe_qcgu.v_createdviewname[TO]tabcontents
[SETALIAS]_dummy[TO]{qe_qcgu.dropview}

It took some time to figure out what was going wrong, but the reward seems to be even bigger when I got the software to do what I intended it to do. This was a really simple example, but I think it shows the power of what you can do with CodeGen.