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.
Hello,
thanks a lot!
i was stuck with this problem and you made my day!
just a precision : dropview and createdviewname are both in the oracle package named qe_qcgu