usefull scripts

--查询显示多个表的行数

SELECT 'SELECT ''' || TABLE_NAME || ''' AS TABLE_NAME, COUNT(*) AS CNT FROM ' || TABLE_NAME || ' UNION ALL '
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'STG%'

--COMMAND 执行，以XML格式显示

declare
l_xmltype XMLType;
l_varchar2 VARCHAR2(20000);
begin
     for cur_xml in (SELECT xmlelement("field",
                  xmlelement("name", column_name),
                  xmlelement("type",
                             decode(data_type,
                                    'VARCHAR2',
                                    'String',
                                    'NUMBER',
                                    'Number')),
                  xmlelement("format", NULL),
                  xmlelement("currency", NULL),
                  xmlelement("decimal", NULL),
                  xmlelement("group", NULL),
                  xmlelement("length", '-1'),
                  xmlelement("precision", '-1'),
                  xmlelement("trim_type", 'both'))AS drr
  FROM user_tab_columns
WHERE table_name = 'STG
order by table_name,column_id) loop
       l_xmltype := cur_xml.drr;
       l_varchar2 := l_xmltype.getStringVal();
       dbms_output.put_line(l_varchar2);
end loop;
end;