create or replace package body PACK_ADD_COLUMN is
procedure p_add_column(
P_IN_TABLENAME IN VARCHAR2 DEFAULT NULL,
P_OUT_STATUS OUT VARCHAR2
)
as
/* P_OUT_STATUS:='ok';*/
v_sql varchar2(10000) default '';
v_table_name varchar2(100) default '';
v_columu_name varchar2(100) default '';
v_data_type varchar2(100) default '';
v_table_name_sql varchar2(100) default '';
v_type_flag varchar2(10);
v_pk_flag varchar2(10);
v_zl_flag varchar2(10);
begin
P_OUT_STATUS:='OK';
for j in (select
c.table_name,c.column_name,c.data_type
from user_tab_columns c
where c.table_name in upper(''||P_IN_TABLENAME||'')) LOOP
v_type_flag:='0';
v_pk_flag:='0';
v_zl_flag:='0';
v_table_name:=j.table_name;
v_columu_name:=j.column_name;
v_data_type:=j.data_type;
v_table_name_sql:='TB_OBJECT_'||substr(v_table_name,9,13);
IF j.data_type='CLOB' THEN
v_type_flag:='1';
END IF;
IF j.column_name='OB_OBJECT_ID' THEN
v_pk_flag:='1';
END IF;
IF j.column_name='PROGRESS' or j.column_name='RP_ACTION' or j.column_name='RP_GEN_DATETIME'
THEN v_zl_flag:='1';
END IF;
v_sql:='insert into wind_synctable
values ('''||v_table_name||''','''||v_columu_name||''','''|| v_type_flag||''',''WDdb2'','''|| v_table_name_sql||''','''||v_columu_name||''',''MSSQL_WDDB'','''||v_pk_flag||''','''||v_zl_flag||''')';
EXECUTE IMMEDIATE v_sql;
commit;
end loop;
exception
when others then
P_OUT_STATUS:='FALSE';
rollback;
raise;
END p_add_column;
end PACK_ADD_COLUMN;