{"id":152,"date":"2007-06-17T13:27:56","date_gmt":"2007-06-17T12:27:56","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=152"},"modified":"2007-06-17T13:27:58","modified_gmt":"2007-06-17T12:27:58","slug":"crazy-things-in-codegen","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=152","title":{"rendered":"Crazy things in CodeGen…"},"content":{"rendered":"

While I was preparing for the presentation at Amis Academy<\/a> about CodeGen<\/a> 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).<\/p>\n

<\/p>\n

\n

<?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><\/font><\/p>\n<\/blockquote>\n

After a couple of iterations the script looked like this:<\/p>\n

\n

<?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]><\/font><\/p>\n<\/blockquote>\n

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.<\/p>\n

\n

<?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]><\/font><\/p>\n<\/blockquote>\n

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.<\/p>\n

\n

FUNCTION dropview
  RETURN NUMBER
IS
BEGIN
  EXECUTE IMMEDIATE ‘drop view ‘ || v_createdviewname;
  RETURN 0;
END dropview; <\/font><\/p>\n<\/blockquote>\n

After cleaning up the views at the end of the script, by adding some extra code to the script, it solved my problem. <\/p>\n

\n

#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}<\/font><\/p>\n<\/blockquote>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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).<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,5,4],"tags":[],"class_list":["post-152","post","type-post","status-publish","format-standard","hentry","category-codegen","category-plsql","category-tools"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=152"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/152\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}