create or replace procedure BATCH_CHECK_DDL_P(in_username in varchar2 ) is /*************************************************************** *NAME : BATCH_INSERT_TABLEA_P *PURPOSE : --批量查询数据库内某个用户的建表语句 *IMPUT : -- 表名子 *OUTPUT : -- N/A *Author : -- CICI *CreateDate : -- 2012、12、24 *UpdateDate : -- ************************************************************/ V_TABLE_NAMES VARCHAR2(10000); cursor cur is select OBJECT_NAME from all_objects where OWNER = upper(in_username) and object_type = 'TABLE'; begin /*查询属于ss_hr用户的全部表的信息*/ open cur; fetch cur into V_TABLE_NAMES; WHILE cur%FOUND LOOP exit when not cur%found; --如果游标到尾则结束 /*遍历获取建表语句*/ dbms_output.put_line('================================================='); dbms_output.put_line (dbms_metadata.get_ddl(object_type => 'TABLE', name => upper(V_TABLE_NAMES), schema=>upper(in_username))); dbms_output.put_line('================================================='); fetch cur into V_TABLE_NAMES; END LOOP; CLOSE cur; end BATCH_CHECK_DDL_P;