Java object generator for Oracle

table2class.sql

WITH TABLE_NM AS
      (  SELECT UPPER(:TARGET) as TABLE_NM  
              , '// '          as comm01
              , '/**'          as jdoc01
              , ' * '          as jdoc02
              , ' */'          as jdoc03
              , ''             as prefix
              , 'Vo'           as surfix
         FROM DUAL)
 SELECT  JAVA_CLASS
 FROM   (
         SELECT -8000 AS COLUMN_ID, ' ' AS JAVA_CLASS FROM DUAL UNION ALL
         SELECT -7000 AS COLUMN_ID, ' ' AS JAVA_CLASS FROM DUAL UNION ALL
         SELECT -6000 AS COLUMN_ID, 'import java.io.Serializable;' FROM DUAL UNION ALL
         SELECT -5000 AS COLUMN_ID, ' ' FROM DUAL UNION ALL
         SELECT -4000 AS COLUMN_ID, jdoc01 FROM TABLE_NM UNION ALL
         SELECT -3000 AS COLUMN_ID, jdoc02 || B.COMMENTS FROM TABLE_NM A, USER_TAB_COMMENTS B WHERE A.TABLE_NM = B.TABLE_NAME UNION ALL
         SELECT -2000 AS COLUMN_ID, jdoc03 FROM TABLE_NM UNION ALL
         SELECT -1000 AS COLUMN_ID,'public class ' || lower(SUBSTR(TABLE_NM, 1,1))||SUBSTR(replace(INITCAP(lower(TABLE_NM)), '_',''), 2) || surfix || ' implements Serializable {'FROM TABLE_NM UNION  ALL
         SELECT A.COLUMN_ID,
                '    public ' || CASE WHEN A.DATA_TYPE = 'NUMBER' THEN CASE WHEN DATA_PRECISION IS NOT NULL THEN 'Double' ELSE 'Integer' END ELSE 'String' END || ' ' ||
                lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2) ||
                LPAD(' ', 32 - LENGTH(lower(SUBSTR(A.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.COLUMN_NAME)), '_',''), 2))) || '; ' || comm01 || C.COMMENTS
         FROM   USER_TAB_COLUMNS A, TABLE_NM,
               (SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, A.POSITION
                FROM   USER_CONS_COLUMNS A, USER_CONSTRAINTS B
                WHERE  A.TABLE_NAME      = B.TABLE_NAME
                AND    A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
                AND    B.CONSTRAINT_TYPE = 'P') B,
                USER_COL_COMMENTS C
         WHERE  A.TABLE_NAME  = B.TABLE_NAME(+)
         AND    A.COLUMN_NAME = B.COLUMN_NAME(+)
         AND    A.TABLE_NAME  = TABLE_NM
         AND    A.TABLE_NAME  = C.TABLE_NAME(+)
         AND    A.COLUMN_NAME = C.COLUMN_NAME(+)
      --   AND    A.COLUMN_NAME NOT IN('UPDTR_ID','UPDT_DT','UPDT_IP')
         UNION  ALL
         SELECT 9999, '}' FROM DUAL
        )
 ORDER BY COLUMN_ID;