自动生成form Scripts

运行脚本：

begin
            xxx_plsql_generator_pkg2.form_view_iud_p(p_block_name  =>'CONTRACT_T'
                                                   ,p_table_name  =>'WPC_Contract_T'
                                                   ,p_owner       =>'apps'
                                                   ,p_primary_key =>'FConId');
end;

select *
        from all_tab_columns tc
       where tc.table_name = upper('cux_dis_return_ifc')
         and tc.owner = upper('apps')
         and tc.column_name = upper('FConId');

执行脚本

调用的pckage：

create or replace package xxx_plsql_generator_pkg is
  /*==================================================
    Copyright (C) LYR Consulting Co., Ltd
    All rights reserved
  ===================================================*/
  -- Author  : luoyuren
  -- Created : 2010-12-07
  -- Purpose : Auto Generat Plsql Code
  g_exception exception;

  /******************************************************************

  IN
     p_block_name  Form中数据块的名称
     p_table_name  需要进行操作的数据表
     p_owner       数据表的owner
     p_primary_key 数据表的主键

  DESCEIPTOIN
     此过程主要用来生成基于视图的数据块常用触发器ON-INSERT,
     ON-UPDATE,ON-DELETE,ON-LOCK之代码,使用时采用PL/SQL Developer
     开发工具的的Test Windows,代码执行完成后会在DBMS Output标签页
     中输出相关代码,使用时将代码Copy至Oralce Form Builder的Program
     Units中,分为Package Spec和Package Body,并在相应触发器中加入相
     应引用代码;

  ***************************************************************/
  procedure form_view_iud_p(p_block_name  in varchar2
                           ,p_table_name  in varchar2
                           ,p_owner       in varchar2
                           ,p_primary_key in varchar2);

end xxx_plsql_generator_pkg;
/
create or replace package body xxx_plsql_generator_pkg is
  /*==================================================
    Copyright (C) LYR Consulting Co., Ltd
    All rights reserved
  ===================================================*/
  g_output_first boolean := false;
  g_cp_flag      number; -- conc program
  g_newline      varchar2(1) := chr(10);

  type column_rec_type is record(
    column_name varchar2(30),
    nullable    varchar2(1));

  type column_tbl_type is table of column_rec_type index by binary_integer;

  procedure output_msg(p_msg_data in varchar2) is
  begin
    if g_output_first = false
    then
      g_cp_flag := fnd_profile.value('CONC_REQUEST_ID');
      if g_cp_flag > 0
      then
        null;
      else
        dbms_output.enable(buffer_size => 20000000);
      end if;
    end if;

    if (g_cp_flag > 0)
    then
      fnd_file.put_line(fnd_file.log
                       ,p_msg_data);
    else
      dbms_output.put_line(p_msg_data);
    end if;
  exception
    when others then
      null;
  end output_msg;

  procedure form_view_iud_p(p_block_name  in varchar2
                           ,p_table_name  in varchar2
                           ,p_owner       in varchar2
                           ,p_primary_key in varchar2) is
    cursor c_table is
      select 'Y'
        from all_tables t
       where t.table_name = upper(p_table_name)
         and t.owner = upper(p_owner);

    cursor c_tabcol is
      select 'Y'
        from all_tab_columns tc
       where tc.table_name = upper(p_table_name)
         and tc.owner = upper(p_owner)
         and tc.column_name = upper(p_primary_key);

    cursor c_sequence is
      select 'Y'
        from all_objects ao
       where ao.owner = p_owner
         and ao.object_type = 'SEQUENCE'
         and ao.object_name = upper(p_table_name) || '_S';

    cursor c_columns is
      select tc.column_name
            ,tc.nullable
        from all_tab_columns tc
       where tc.table_name = upper(p_table_name)
         and tc.owner = upper(p_owner)
       order by tc.column_id;

    l_dummy          varchar2(1);
    l_msg_data       varchar2(4000);
    l_package_name   varchar2(100);
    l_block_name_ext varchar2(50) := ':' || upper(p_block_name) || '.';
    l_rec_ext        varchar2(30) := 'rec.';

    l_column_tbl   column_tbl_type;
    l_column_count number := 0;

  begin

    if (p_block_name is null) or
       (p_table_name is null) or
       (p_owner is null) or
       (p_primary_key is null)
    then
      l_msg_data := 'Parameter not allow null !';
      raise fnd_api.g_exc_error;
    end if;

    -- check table exists
    open c_table;
    fetch c_table
      into l_dummy;
    if c_table%notfound
    then
      close c_table;
      l_msg_data := 'Table ' || upper(p_table_name) || ' not found !';
      raise fnd_api.g_exc_error;
    end if;
    close c_table;

    --check primary_key exists
    open c_tabcol;
    fetch c_tabcol
      into l_dummy;
    if c_tabcol%notfound
    then
      close c_tabcol;
      l_msg_data := 'Table ' || upper(p_table_name) ||
                    ' not exists column ' || upper(p_primary_key) || ' !';
      raise fnd_api.g_exc_error;
    end if;
    close c_tabcol;

    --check sequence exists
    /*Open c_sequence;
    Fetch c_sequence
      Into l_dummy;
    If c_sequence%Notfound Then
      Close c_sequence;
      l_msg_data := 'Sequence ' || upper(p_block_name) || '_S not exists !';
      Raise fnd_api.g_exc_error;
    End If;
    Close c_sequence;*/

    -- get columns
    for r in c_columns
    loop
      l_column_count := l_column_count + 1;
      l_column_tbl(l_column_count).column_name := r.column_name;
      l_column_tbl(l_column_count).nullable := r.nullable;
    end loop;

    if l_column_count < 1
    then
      l_msg_data := 'Not column in table ' || p_table_name;
      raise fnd_api.g_exc_error;
    end if;

    l_package_name := upper(p_block_name) || '_PKG';
    -- generate package special
    l_msg_data := 'PACKAGE ' || l_package_name || ' IS' || g_newline ||
                  g_newline;
    output_msg(l_msg_data);

    l_msg_data := '/*===============================================================' ||
                  g_newline ||
                  '*   Copyright (C) LYR Consulting Co., Ltd All rights reserved' ||
                  g_newline ||
                  '* ===============================================================' ||
                  g_newline || '*    Program Name:' || l_package_name;

    output_msg(l_msg_data);

    l_msg_data := '*    Author      :' || g_newline || '*    Date        :' ||
                  g_newline || '*    Purpose     :' || g_newline ||
                  '*    Parameters  :' || g_newline ||
                  '*    Update History' || g_newline ||
                  '*    Version    Date         Name            Description' ||
                  g_newline ||
                  '*    --------  ----------  ---------------  --------------------';

    output_msg(l_msg_data);

    l_msg_data := '*     V1.0                                   Creation    ' ||
                  g_newline || '*' || g_newline ||
                  '  ===============================================================*/' ||
                  g_newline;

    output_msg(l_msg_data);

    l_msg_data := '  PROCEDURE insert_row;' || g_newline ||
                  '  PROCEDURE lock_row;' || g_newline ||
                  '  PROCEDURE update_row;' || g_newline ||
                  '  PROCEDURE delete_row;' || g_newline || g_newline ||
                  'END ' || l_package_name || ';' || g_newline;

    output_msg(l_msg_data);

    -- generate package body
    -- begin
    l_msg_data := 'PACKAGE BODY ' || l_package_name || ' IS' || g_newline;
    output_msg(l_msg_data);

    -- insert row

    /*
    l_msg_data := '\*=====================================' || g_newline || '** PROCEDURE:  insert_row()' || g_newline ||
                  '**=====================================*\' || g_newline || 'PROCEDURE insert_row IS' || g_newline;
                  */

    l_msg_data := '/*===============================================================' ||
                  g_newline || '*    Program Name:insert_row()' ||
                  g_newline || '*    Author      :' || g_newline ||
                  '*    Date        :' || g_newline || '*    Purpose     :' ||
                  g_newline || '*    Parameters  :' || g_newline ||
                  '*             In       X     --X的说明';

    output_msg(l_msg_data);

    l_msg_data := '*             Out      Y     --Y的说明' || g_newline ||
                  '*    Update History' || g_newline ||
                  '*    Version    Date         Name            Description' ||
                  g_newline ||
                  '*    --------  ----------  ---------------  --------------------';

    output_msg(l_msg_data);

    l_msg_data := '*     V1.0                                   Creation    ' ||
                  g_newline || '*' || g_newline ||
                  '  ===============================================================*/' ||
                  g_newline || 'PROCEDURE insert_row IS';

    output_msg(l_msg_data);

    l_msg_data := '  CURSOR row_id' || g_newline || '  IS    SELECT ROWID' ||
                  g_newline || '       FROM ' || p_table_name || g_newline ||
                  '      WHERE ' || p_primary_key || ' = ' ||
                  l_block_name_ext || p_primary_key || ';' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := 'BEGIN' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  fnd_standard.set_who;' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  IF ' || l_block_name_ext || p_primary_key ||
                  ' IS NULL THEN' || g_newline || '    SELECT ' ||
                  upper(p_table_name) || '_S.NEXTVAL' || g_newline ||
                  '      INTO ' || l_block_name_ext || p_primary_key ||
                  g_newline || '      FROM SYS.DUAL;' || g_newline ||
                  '  END IF;' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  INSERT INTO ' || p_table_name || ' (';
    output_msg(l_msg_data);
    for i in 1 .. l_column_count
    loop
      if i = l_column_count
      then
        l_msg_data := '     ' || l_column_tbl(i).column_name || ')';
      else
        l_msg_data := '     ' || l_column_tbl(i).column_name || ',';
      end if;
      output_msg(l_msg_data);
    end loop;
    l_msg_data := '  VALUES (';
    output_msg(l_msg_data);
    for i in 1 .. l_column_count
    loop
      if i = l_column_count
      then
        l_msg_data := '     ' || l_block_name_ext || l_column_tbl(i)
                     .column_name || ');' || g_newline;
      else
        l_msg_data := '     ' || l_block_name_ext || l_column_tbl(i)
                     .column_name || ',';
      end if;
      output_msg(l_msg_data);
    end loop;

    l_msg_data := '  OPEN row_id;' || g_newline || '  FETCH row_id INTO ' ||
                  l_block_name_ext || 'ROW_ID;' || g_newline ||
                  '  IF (row_id%NOTFOUND) THEN' || g_newline ||
                  '     CLOSE row_id;' || g_newline ||
                  '     RAISE NO_DATA_FOUND;' || g_newline || '  END IF;' ||
                  g_newline || '  CLOSE row_id;' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := 'END insert_row;' || g_newline;
    output_msg(l_msg_data);

    -- lock row

    --l_msg_data := '\*=====================================' || g_newline || '** PROCEDURE:  lock_row()' || g_newline ||
    --              '**=====================================*\' || g_newline || 'PROCEDURE lock_row IS' || g_newline;

    l_msg_data := '/*===============================================================' ||
                  g_newline || '*    Program Name:lock_row()' || g_newline ||
                  '*    Author      :' || g_newline || '*    Date        :' ||
                  g_newline || '*    Purpose     :' || g_newline ||
                  '*    Parameters  :' || g_newline ||
                  '*             In       X     --X的说明';

    output_msg(l_msg_data);

    l_msg_data := '*             Out      Y     --Y的说明' || g_newline ||
                  '*    Update History' || g_newline ||
                  '*    Version    Date         Name            Description' ||
                  g_newline ||
                  '*    --------  ----------  ---------------  --------------------';

    output_msg(l_msg_data);

    l_msg_data := '*     V1.0                                   Creation    ' ||
                  g_newline || '*' || g_newline ||
                  '  ===============================================================*/' ||
                  g_newline || 'PROCEDURE lock_row IS';

    output_msg(l_msg_data);

    l_msg_data := '  CURSOR c_row' || g_newline || '  IS SELECT *' ||
                  g_newline || '       FROM ' || p_table_name || g_newline ||
                  '      WHERE rowid = ' || l_block_name_ext || 'ROW_ID' ||
                  g_newline || '      FOR UPDATE OF ' || p_primary_key ||
                  ' NOWAIT;' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  rec     c_row%rowtype;' || g_newline ||
                  '  i NUMBER := 0;' || g_newline || 'BEGIN' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  LOOP' || g_newline || '    BEGIN';
    output_msg(l_msg_data);

    l_msg_data := '      i := i + 1;' || g_newline || '      OPEN c_row;' ||
                  g_newline || '      FETCH c_row INTO rec;';
    output_msg(l_msg_data);

    l_msg_data := '      IF (c_row%NOTFOUND) THEN' || g_newline ||
                  '        CLOSE c_row;' || g_newline ||
                  '        fnd_message.set_name(''FND'',''FORM_RECORD_DELETED'');' ||
                  g_newline || '        fnd_message.error;' || g_newline ||
                  '        RAISE FORM_TRIGGER_FAILURE;';
    output_msg(l_msg_data);

    l_msg_data := '      END IF;' || g_newline || '      CLOSE c_row;' ||
                  g_newline;
    output_msg(l_msg_data);

    l_msg_data := '      IF (';
    output_msg(l_msg_data);
    for i in 1 .. l_column_count
    loop
      if i = 1
      then
        l_msg_data := '              ';
      else
        l_msg_data := '          AND ';
      end if;
      if l_column_tbl(i).column_name = p_primary_key
      then
        l_msg_data := l_msg_data || '(' || l_rec_ext || l_column_tbl(i)
                     .column_name || ' = ' || l_block_name_ext ||
                      l_column_tbl(i).column_name || ')';
      else
        l_msg_data := l_msg_data || '((' || l_rec_ext || l_column_tbl(i)
                     .column_name || ' = ' || l_block_name_ext ||
                      l_column_tbl(i)
                     .column_name || ') OR' || g_newline ||
                      '               ((' || l_rec_ext || l_column_tbl(i)
                     .column_name || ' IS NULL)' || ' AND (' ||
                      l_block_name_ext || l_column_tbl(i)
                     .column_name || ' IS NULL)))';
      end if;
      output_msg(l_msg_data);
    end loop;

    l_msg_data := '      ) THEN' || g_newline || '        RETURN;' ||
                  g_newline || '      ELSE' || g_newline ||
                  '        fnd_message.set_name(''FND'', ''FORM_RECORD_CHANGED'');' ||
                  g_newline || '        fnd_message.error;' || g_newline ||
                  '        RAISE FORM_TRIGGER_FAILURE;' || g_newline ||
                  '      END IF;' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '    EXCEPTION' || g_newline ||
                  '      WHEN app_exception.record_lock_exception THEN' ||
                  g_newline ||
                  '        app_exception.record_lock_error(i);' ||
                  g_newline || '    END;' || g_newline || '  END LOOP;' ||
                  g_newline;
    output_msg(l_msg_data);

    l_msg_data := 'END lock_row;' || g_newline;
    output_msg(l_msg_data);

    -- update row
    l_msg_data := '/*===============================================================' ||
                  g_newline || '*    Program Name:update_row()' ||
                  g_newline || '*    Author      :' || g_newline ||
                  '*    Date        :' || g_newline || '*    Purpose     :' ||
                  g_newline || '*    Parameters  :' || g_newline ||
                  '*             In       X     --X的说明';

    output_msg(l_msg_data);

    l_msg_data := '*             Out      Y     --Y的说明' || g_newline ||
                  '*    Update History' || g_newline ||
                  '*    Version    Date         Name            Description' ||
                  g_newline ||
                  '*    --------  ----------  ---------------  --------------------';

    output_msg(l_msg_data);

    l_msg_data := '*     V1.0                                   Creation    ' ||
                  g_newline || '*' || g_newline ||
                  '  ===============================================================*/' ||
                  g_newline || 'PROCEDURE update_row IS';

    output_msg(l_msg_data);

    l_msg_data := 'BEGIN' || g_newline;
    output_msg(l_msg_data);
    l_msg_data := '  fnd_standard.set_who;' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  UPDATE ' || p_table_name || ' SET';
    output_msg(l_msg_data);
    for i in 1 .. l_column_count
    loop
      l_msg_data := '      ' || rpad(l_column_tbl(i).column_name
                                    ,30) || ' = ' || l_block_name_ext ||
                    l_column_tbl(i).column_name;
      if i < l_column_count
      then
        l_msg_data := l_msg_data || ',';
      end if;
      output_msg(l_msg_data);
    end loop;
    l_msg_data := '  WHERE ROWID = ' || l_block_name_ext || 'ROW_ID;' ||
                  g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  IF (SQL%NOTFOUND) THEN' || g_newline ||
                  '     RAISE NO_DATA_FOUND;' || g_newline || '  END IF;' ||
                  g_newline;
    output_msg(l_msg_data);

    l_msg_data := 'END update_row;' || g_newline;
    output_msg(l_msg_data);

    -- delete row

    --l_msg_data := '\*=====================================' || g_newline || '** PROCEDURE:  delete_row()' || g_newline ||
    --                  '**=====================================*\' || g_newline || 'PROCEDURE delete_row IS';

    l_msg_data := '/*===============================================================' ||
                  g_newline || '*    Program Name:delete_row()' ||
                  g_newline || '*    Author      :' || g_newline ||
                  '*    Date        :' || g_newline || '*    Purpose     :' ||
                  g_newline || '*    Parameters  :' || g_newline ||
                  '*             In       X     --X的说明';

    output_msg(l_msg_data);

    l_msg_data := '*             Out      Y     --Y的说明' || g_newline ||
                  '*    Update History' || g_newline ||
                  '*    Version    Date         Name            Description' ||
                  g_newline ||
                  '*    --------  ----------  ---------------  --------------------';

    output_msg(l_msg_data);

    l_msg_data := '*     V1.0                                   Creation    ' ||
                  g_newline || '*' || g_newline ||
                  '  ===============================================================*/' ||
                  g_newline || 'PROCEDURE delete_row IS';

    output_msg(l_msg_data);

    l_msg_data := 'BEGIN' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  DELETE FROM ' || p_table_name || g_newline ||
                  '  WHERE ' || p_primary_key || ' = ' || l_block_name_ext ||
                  p_primary_key || ';' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  IF (SQL%NOTFOUND) THEN' || g_newline ||
                  '     RAISE NO_DATA_FOUND;' || g_newline || '  END IF;' ||
                  g_newline;
    output_msg(l_msg_data);

    l_msg_data := 'END delete_row;' || g_newline;
    output_msg(l_msg_data);

    -- end
    l_msg_data := 'END ' || l_package_name || ';';
    output_msg(l_msg_data);

  exception
    when fnd_api.g_exc_error then
      output_msg(l_msg_data);
    when others then
      output_msg(sqlerrm);
  end form_view_iud_p;

end xxx_plsql_generator_pkg;
/