On February 21st I did a presentation on CodeGen at AMIS. Just a short presentation on what is possible using this great tool. I have created a couple of questions for the hands-on part of the session.

In case you are wondering what CodeGen is exactly, CodeGen is a freeware tool provided by Quest software and developed by Steven Feuerstein (mainly). CodeGen can generate any kind of textfile (source code, documentation etc) based on any Oracle database object (table/view or program). It can generate any kind of language (SQL, PL/SQL, Java etc) as long as it’s a textfile. The scripts are written in CGML (Code Generation Markup Language) which is a proprietary language to CodeGen. It takes some time to learn, but it is quite powerful. And anything that can’t be done with the standard installation of CodeGen can probably be done by extending CodeGen with your own PL/SQL Code.

For the assignments that were made for the presentation, and their answers (at least, how I would solve them). (I wanted to hide the resolutions, but that doesn’t appear to work on this blog. It does on my own blog (crosspost http://bar-solutions.com/wordpress/?p=221))

Assignment 1: Build a script that shows the contents (describe) of a table.

Resolution 1: This is the resolution using all the different fields of the COL array.

Name Type Nullable Default Comments
-----------------------------------------------
[FOREACH]COL
[COLNAME] [DATA_DECLARATION] [COLNULLABLE] [DATA_DEFAULT] [COMMENTS]
[ENDFOREACH]

Assignment 2: Build a script to create the table.

Resolution 2:

-- Create table
create table [OBJNAME]
(
[FOREACH]col[BETWEEN],
  [COLNAME] [DATA_DECLARATION]
[ENDFOREACH]
);

Assignment 3: Retrieve DATA from the CD-Collection table, make it into an HTML file.

Resolution 3: Use DEFARRAY to create your own array

[DEFARRAY]cdcollection[AS]
select COL_TITLE TITLE, COL_ARTIST ARTIST
  from cd_collection
[ENDDEFARRAY]
<html>
  <body>
    <h2>My CD Collection</h2>
    <table border="1">
      <tr bgcolor="#9acd32">
        <th>Title</th>
        <th>Artist</th>
      </tr>
      [FOREACH]cdcollection
      <tr>
        <td>[TITLE]</td>
        <td>[ARTIST]</td>
      </tr>
      [ENDFOREACH]
    </table>
  </body>
</html>

Assignment 4: Create JAVA classes and PL/SQL Code for a table Example taken from: Oracle and Java Stored Procedures http://www.developer.com/db/article.php/3337411

Resolution 4:

Script 1:

[RTO]RTOJavaType[AS]
  [IF]coldatatype[IN]VARCHAR2,CHAR,VARCHAR
    [SETALIAS]_JavaDataType[TO]String
  [ELSIF]coldatatype[IN]NUMBER,FLOAT,DECIMAL,INTEGER,INT
     [SETALIAS]_JavaDataType[TO]float
    [IF]data_declaration[LIKE]%,%
      [SETALIAS]_JavaDataType[TO]float
    [ELSE]
      [SETALIAS]_JavaDataType[TO]int
    [ENDIF]
  [ELSIF]coldatatype[IN]DATE,TIMESTAMP,DATETIME
     [SETALIAS]_JavaDataType[TO]Date
  [ENDIF]
[ENDRTO]
[RTO]RTOJavaSet[AS]
  [IF]coldatatype[IN]VARCHAR2,CHAR,VARCHAR
    [SETALIAS]_JavaSet[TO]setString
  [ELSIF]coldatatype[IN]NUMBER,FLOAT,DECIMAL,INTEGER,INT
     [SETALIAS]_JavaSet[TO]setfloat
    [IF]data_declaration[LIKE]%,%
      [SETALIAS]_JavaSet[TO]setfloat
    [ELSE]
      [SETALIAS]_JavaSet[TO]setint
    [ENDIF]
  [ELSIF]coldatatype[IN]DATE,TIMESTAMP,DATETIME
     [SETALIAS]_JavaSet[TO]setDate
  [ENDIF]
[ENDRTO]

import java.sql.*;
import oracle.jdbc.*;

public class [OBJNAME]Manager \{

   //Add a record to the database.
   public static void add[OBJNAME](
   [FOREACH]col[BETWEEN],
   [INCLUDE]RTOJavaType
     {_JavaDataType} [COLNAME]
   [ENDFOREACH]
      ) \{

      System.out.println("Creating new record...");

      try \{
         Connection conn =
            DriverManager.getConnection("jdbc:default:connection:");

         [SETALIAS]_Columns[TO]
         [SETALIAS]_PlaceHolder[TO]
         [SETALIAS]_first[TO]Y
         [FOREACH]COL
         [IF]{_first}[EQ]Y
           [SETALIAS]_Columns[TO]{_Columns}[COLNAME]
           [SETALIAS]_PlaceHolder[TO]{_PlaceHolder}?
           [SETALIAS]_first[TO]N
         [ELSE]
           [SETALIAS]_Columns[TO]{_Columns},[COLNAME]
           [SETALIAS]_PlaceHolder[TO]{_PlaceHolder},?
         [ENDIF]
         [ENDFOREACH]
         String sql =
            "INSERT INTO [OBJNAME] " +
            "({_Columns}) " +
            "VALUES ({_PlaceHolder})";
         PreparedStatement pstmt = conn.prepareStatement(sql);
         [SET]QE_TOOLS.COUNTER[TO]0
         [FOREACH]col
           [SET]QE_TOOLS.COUNTER[TO]{QE_TOOLS.COUNTER+1}
           [INCLUDE]RTOJavaSet
         pstmt.{_JavaSet}({QE_TOOLS.COUNTER},[COLNAME]);
         [ENDFOREACH]
         pstmt.executeUpdate();
         pstmt.close();
         \}
      catch(SQLException e) \{
         System.err.println("ERROR! Adding Employee: "
           + e.getMessage());
         \}
   \}
\}

Script 2:

loadjava -u scott/tiger -v -resolve [OBJNAME]Manager.java
--check if it is valid
SELECT object_name, object_type, status
  FROM user_objects
 WHERE object_type LIKE 'JAVA%'
   AND object_name LIKE '%[OBJNAME]%';

Script 3:

[RTO]RTOJavaType[AS]
  [IF]coldatatype[IN]VARCHAR2,CHAR,VARCHAR
    [SETALIAS]_JavaDataType[TO]java.lang.String
  [ELSIF]coldatatype[IN]NUMBER,FLOAT,DECIMAL,INTEGER,INT
     [SETALIAS]_JavaDataType[TO]float
    [IF]data_declaration[LIKE]%,%
      [SETALIAS]_JavaDataType[TO]float
    [ELSE]
      [SETALIAS]_JavaDataType[TO]int
    [ENDIF]
  [ELSIF]coldatatype[IN]DATE,TIMESTAMP,DATETIME
     [SETALIAS]_JavaDataType[TO]Date
  [ENDIF]
[ENDRTO]
CREATE OR REPLACE PROCEDURE add_[OBJNAME] (
  [FOREACH]col[BETWEEN],
  [COLNAME] [COLDATATYPE]
  [ENDFOREACH]
  )
  AS LANGUAGE JAVA
  NAME '[OBJNAME]Manager.add[OBJNAME](
  [FOREACH]col[BETWEEN],
  [INCLUDE]RTOJavaType
  {_JavaDataType}
  [ENDFOREACH]
  )';
/

KC presentation on CodeGen…

Like this:

About The Author

Patrick Barel

Share this:

Like this:

Related Posts

About The Author

Patrick Barel