ebs二次開發2

CREATE OR REPLACE package APPS.cux_gen_proc_sql as
    procedure gen_insert_sp(v_table_name varchar2,v_proc_name varchar2);
end ;
/

CREATE OR REPLACE package body APPS.cux_gen_proc_sql as
procedure gen_insert_sp(v_table_name varchar2,v_proc_name varchar2)
is
  CURSOR c1 IS
    SELECT owner,table_name,
       column_name,
       data_type,
       data_length,
       nullable
    from dba_tab_columns
    WHERE table_name = upper(v_table_name)
    order by column_id ;
  v_count NUMBER := 0;
  v_tatol NUMBER := 0;
  v_column_name varchar2(30);
BEGIN
  SELECT COUNT(*)
    INTO v_tatol
    FROM dba_tab_columns
   WHERE table_name = upper(v_table_name);
    dbms_output.put_line('PROCEDURE ' || v_proc_name || '(');
    FOR c1_rec IN c1 LOOP
        if lower(c1_rec.column_name) not in('last_update_date' ,'creation_date') then
            if length(c1_rec.column_name)>28 then
               v_column_name:=substr(lower(c1_rec.column_name),1,28);
            else
                v_column_name:=lower(c1_rec.column_name);
            end if;
            dbms_output.put(to_char('p_') || v_column_name || ' ' || to_char(lower(c1_rec.data_type)) );
            if c1_rec.nullable='Y' then
                dbms_output.put(' default null');
            end if;
            v_count := v_count + 1;
            IF v_count < nvl(v_tatol, 0) then
               dbms_output.put_line(',');
             else
                   dbms_output.put_line(' ');
            end if;
        end if;
    END LOOP;
    dbms_output.put_line(' ) is ');
    dbms_output.put_line('BEGIN');
    v_count :=0;
    FOR c1_rec IN c1 LOOP
      v_count := v_count + 1;
      IF v_count = 1 THEN
        dbms_output.put_line('INSERT INTO ' || c1_rec.table_name);
        dbms_output.put_line('( ');
      END IF;
      IF v_count = nvl(v_tatol, 0) THEN
        dbms_output.put_line('  ' || c1_rec.column_name);
        dbms_output.put_line(')');
        dbms_output.put_line('VALUES(');
      ELSE
        dbms_output.put_line('  ' || c1_rec.column_name || ' ,');
      END IF;
    END LOOP;
    v_count := 0;
    FOR c1_rec IN c1 LOOP
      v_count := v_count + 1;
      if length(c1_rec.column_name)>28 then
          v_column_name:=to_char('p_') ||  substr(lower(to_char(c1_rec.column_name)),1,28);
      else
          v_column_name:=to_char('p_') ||  lower(to_char(c1_rec.column_name));--to_char('p_') ||
      end if;

      if to_char(lower(c1_rec.column_name)) in('last_update_date','creation_date') then
         v_column_name:= to_char('sysdate');
      end if;
      IF v_count = nvl(v_tatol, 0) THEN
        dbms_output.put_line('  ' ||  v_column_name);
        dbms_output.put_line(');');
      ELSE
        dbms_output.put_line('  ' ||  v_column_name || ' ,');
      END IF;
    END LOOP;
    dbms_output.put_line(' ');
    dbms_output.put_line('END '|| v_proc_name ||';');
    dbms_output.put_line(' ');
  end gen_insert_sp;
end;
/

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11536986/viewspace-620199/，如需轉載，請註明出處，否則將追究法律責任。