create or replace 
package table_to_bean_generator
is

/*
Run the table_to_bean_generator like this:

begin
  table_to_bean_generator.generate_bean( p_tbl_name=> 'EMP' );
  table_to_bean_generator.generate_bean_mgr( p_tbl_name=> 'EMP' );
end;
/
begin
  table_to_bean_generator.generate_bean_mgr( p_tbl_name=> 'DEPT' , p_where => 'where deptno < 40');
end;

*/

procedure generate_bean
( p_tbl_name in varchar2)
;
procedure generate_bean_mgr
( p_tbl_name in varchar2
, p_where    in varchar2 default null
);

end table_to_bean_generator;
/

create or replace package body table_to_bean_generator
is
  cursor c_col(b_tbl_name in varchar2)
  is
    select column_name
    ,      data_type
    ,      data_scale
    ,      data_length
    ,      case data_type
           when 'VARCHAR2'
           then 'String'
           when 'DATE'
           then 'Timestamp'
           when 'NUMBER'
           then case data_scale
                when 0
                then 'Long'
                else 'Double'
                end
           end java_type
    from   user_tab_columns
    where  table_name = b_tbl_name
    ;
  cursor c_pk_col(b_tbl_name in varchar2)
  is
    select column_name
    from   user_constraints con
    ,      user_cons_columns ccl
    where  con.table_name = b_tbl_name
    and    con.constraint_type ='P'
    and    con.constraint_name = ccl.constraint_name
    and    ccl.table_name = con.table_name
    ;
procedure w(p_txt in varchar2)
is
begin
  dbms_output.put_line(p_txt); --||chr(13)||chr(10));
end w;
procedure generate_bean
( p_tbl_name in varchar2)
is
  l_prefix varchar2(1):='';
begin
  -- mapping: Date => java.sql.Timestamp
  --          Number - scale==0 : Long
  --          Number - scale<>0 : Double
  --          varchar2          : String
  --          at this point, ignore all others
  -- create class with imports, constructor
  w('package nl.amis.hrm;');
  w('import java.sql.Timestamp;');
  w('public class '||initcap(p_tbl_name)||' {');
  -- iterate over columns
  for col in c_col(b_tbl_name => p_tbl_name) loop
    w('private '||col.java_type||' '||lower(col.column_name)||';');
  end loop;
  w('// Constructor');
  w('public '||initcap(p_tbl_name)||' () {');
  w('}');
  w('public '||initcap(p_tbl_name)||' ( ');
  for col in c_col(b_tbl_name => p_tbl_name) loop
    w(l_prefix||col.java_type||' '||lower(col.column_name));
    l_prefix:=',';
  end loop;
  w(') { ');
  for col in c_col(b_tbl_name => p_tbl_name) loop
    w('this.'||lower(col.column_name)||'='||lower(col.column_name)||';');
  end loop;
  w('}');
  -- now generate accessor methods
  for col in c_col(b_tbl_name => p_tbl_name) loop
    w('public void set'||initcap(col.column_name)||' ('||col.java_type||' '||lower(col.column_name)||') {');
    w('this.'||lower(col.column_name)||'='||lower(col.column_name)||';');
    w('}');
    w('public '||col.java_type||' get'||initcap(col.column_name)||' () {');
    w('  return this.'||lower(col.column_name)||';');
    w('}');
  end loop;
    w('}');
end generate_bean;
procedure  generate_create_statements
( p_class_name in varchar2
, p_tbl_name in varchar2
, p_where in varchar2 default null
) is
  l_prefix varchar2(1):='';
  l_date date;
  l_varchar2 varchar2(2000);
  l_number number;
  l_index integer:=0;
  src_cur  INTEGER;
  ignore   INTEGER;
begin
  src_cur := dbms_sql.open_cursor;
  -- parse the SELECT statement
  dbms_sql.parse(src_cur, 'SELECT * from '||p_tbl_name||nvl(p_where,''),  dbms_sql.NATIVE);
  for col in c_col(b_tbl_name=>p_tbl_name) loop
  -- define the column type
  -- loop over the columns in the order of user_tab_columns (same as select * from )
  -- for each column, do
    l_index:= l_index+1;
    if col.data_type = 'DATE'
    then
      dbms_sql.define_column(src_cur, l_index, l_date);
    elsif col.data_type = 'NUMBER'
    then
      dbms_sql.define_column(src_cur, l_index, l_number);
    elsif col.data_type = 'VARCHAR2'
    then
      dbms_sql.define_column(src_cur, l_index, l_varchar2, col.data_length);
    end if;
  end loop;
  -- loop over all records
  ignore := dbms_sql.execute(src_cur);
  LOOP
    -- Fetch a row from the source table
    IF dbms_sql.fetch_rows(src_cur) > 0 
    THEN
      -- get column values of the row
      l_index :=0;
      l_prefix:= '';
      w(' create'||p_class_name||'(');
      -- loop over columns
      -- within record, loop over columns
      for col in c_col(b_tbl_name=>p_tbl_name) loop
        l_index:= l_index+1;
        if col.data_type = 'DATE'
        then
          begin
            dbms_sql.column_value(src_cur, l_index, l_date);
            if l_date is null
            then
              w(l_prefix||'null');
            else
              w(l_prefix||'new Timestamp('||extract(year from l_date)
               ||', '||extract(month from l_date)
               ||', '||extract(day from l_date)
               ||', '||to_char(l_date,'hh24')
               ||', '||to_char(l_date,'mi')
               ||', '||to_char(l_date,'ss')
               ||', 0)'); --y-m-d-h-m-s-n
            end if;
          end;
        elsif col.data_type = 'NUMBER'
        then 
          dbms_sql.column_value(src_cur, l_index, l_number);
          if l_number is null
          then
            w(l_prefix||'null');
          else
             if col.data_scale = 0
            then 
              w(l_prefix||'(long)'||l_number);
             else 
              w(l_prefix||'(double)'||l_number);
            end if;
          end if;
        elsif col.data_type = 'VARCHAR2'
        then
          dbms_sql.column_value(src_cur, l_index, l_varchar2);
          if l_varchar2 is null
          then
            w(l_prefix||'null');
          else
            w(l_prefix||'"'||l_varchar2||'"');
          end if;
        end if;
        l_prefix:= ',';
      end loop;
      w(');');
    ELSE
      -- No more rows to process
      EXIT;
    END IF;
  END LOOP;  
end generate_create_statements;
procedure generate_bean_mgr
( p_tbl_name in varchar2
, p_where    in varchar2 default null)
is
  l_prefix varchar2(1):='';
  l_class_name varchar2(40):= initcap(p_tbl_name);
begin
  w('package nl.amis.hrm;');
  w('import java.util.ArrayList;');
  w('import java.util.List;');
  w('import java.sql.Timestamp;');
  w('public class '||initcap(p_tbl_name)||'Manager {');
  w('private List<'||l_class_name||'> '||lower(p_tbl_name)||'s= new ArrayList();');
  w('// Constructor');
  w('public '||initcap(p_tbl_name)||'Manager() {');
  w(' resetData();');
  w('}');
  -- resetData
  w('public void resetData() {');
  w('  '||lower(p_tbl_name)||'s= new ArrayList();');
  generate_create_statements(p_class_name => l_class_name, p_tbl_name => p_tbl_name, p_where => p_where);
  w('}');
  -- create
  w('public void create'||l_class_name||'(');
    l_prefix:='';
    for col in c_col(b_tbl_name => p_tbl_name) loop
      w(l_prefix||col.java_type||' '||lower(col.column_name));
      l_prefix:=',';
    end loop;
  w('){');   
  w(' '||l_class_name||' new'||l_class_name||'= null;');
  w('  '||' new'||l_class_name||'= new '||l_class_name||'( ' );
    l_prefix:='';
    for col in c_col(b_tbl_name => p_tbl_name) loop
      w(l_prefix||' '||lower(col.column_name));
      l_prefix:=',';
    end loop;
  w(');');
  w(lower(p_tbl_name)||'s.add(new'||l_class_name||');');
  w('}');
-- generate the find method
  for pk_col in c_pk_col( b_tbl_name => p_tbl_name) loop
  w('public  '||l_class_name||' find'||l_class_name||'( Long id) {');
  w('      // iterate through '||lower(p_tbl_name)||'s to locate entry where get<PrimaryKeyColumn> equal to id ');
  w('      // if none are found, return null ');
  w('  '||l_class_name||' '||lower(p_tbl_name)||'Found = null;');
  w('      for ('||l_class_name||' '|| lower(p_tbl_name)||':'||lower(p_tbl_name)||'s ) {');
  w('        if ('||lower(p_tbl_name)||'.get'||initcap(pk_col.column_name)||'().compareTo(id)==0) {');
  w(            lower(p_tbl_name)||'Found = '||lower(p_tbl_name)||';');
  w('            break;');
  w('        }');
       w('}');
   w('    return '||lower(p_tbl_name)||'Found; ');
   w(' }');
  end loop; 
-- generate accessors for emps
   w(' public void set'||l_class_name||'s(List<'||l_class_name||'> '||lower(p_tbl_name)||'s) {');
   w('     this.'||lower(p_tbl_name)||'s = '||lower(p_tbl_name)||'s;');
   w(' }');
   w(' public List<'||l_class_name||'> get'||l_class_name||'s() {');
   w('     return '||lower(p_tbl_name)||'s;');
   w(' }');
-- generate a main method
    w('public static void main(String[] args) {');
        w(l_class_name||'Manager mgr = new '||l_class_name||'Manager();');
        w('System.out.println("Found "+mgr.get'||l_class_name||'s().size()+ "'||l_class_name||'s");');
    w('}');
    w('}');
end generate_bean_mgr;
end table_to_bean_generator;
/