Oracle批量加注释，并生成html

excel连接列名生成oracle注释

notes: A2为列名，B2为注释

="comment on column ColAgreementHeader."&A2&" is q'{"&B2&"}';"

 

效果如下：

comment on column table1.Id is q'{PK}';

comment on column table1.col1 is q'{Counterparty Organization ID}';

comment on column table1.col2 is q'{Agreement Description}';

--生成html格式

SET MARKUP HTML ON SPOOL ON pre off entmap off
SET ECHO OFF
SET TERMOUT OFF
SET TRIMOUT OFF
set feedback off
set heading on
set linesize 200
set pagesize 10000
set timing off
col nullable for a8
spool t1.html
select tc.column_name,
tc.DATA_TYPE||decode(tc.data_length,0,'','('||tc.data_length||')') data_type,
tc.NULLABLE,
cc.comments
from user_tab_columns tc,user_col_comments cc
where tc.table_name=cc.table_name and tc.column_name=cc.column_name and tc.table_name='TABLE1'
order by column_id;
spool off