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.
Assignment 1: Build a script that shows the contents (describe) of a table.
Resolution
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] )'; /