ORACLE: 打印表结构脚本

使用测试环境： oracle 11g 

DECLARE 
  v_temp varchar2(32767);  
  v_time number;  -- 次数 
  v_clob clob; 
  v_block varchar2(2000);
  v_block_size number := 4000 ; -- put_line() 最大2000Byte; --32767Byte
  v_length number; -- 总长度 
  v_amount number := 1000;
  v_sql varchar2(4000);
  v_long varchar2(32767);
  
 V_OWNER VARCHAR2(150);
 V_OBJECT_NAME VARCHAR2(150);
 v_object_type  VARCHAR2(150);
 v_sql_TEXT CLOB ;
 -- 打印：SIEMENS TEAMCENTER12 标准脚本 
 cursor c_LIST IS 
  SELECT OBJ.OWNER, OBJ.OBJECT_NAME, OBJ.OBJECT_TYPE
    FROM  SYS.DBA_OBJECTS OBJ 
   WHERE OBJ.OBJECT_TYPE = 'TABLE' -- P_OBJECT_TYPE
   AND OBJ.OWNER= 'INFODBA'  -- P_OWNER
   AND OBJ.OBJECT_NAME LIKE '%' -- 'ACCT_TABLE' -- P_OBJECT_NAME
   ORDER BY OBJ.OWNER, OBJ.OBJECT_NAME  ;
-- 环境： oracle 11G 数据库   
-- 使用标准打印脚本函数GET_DDL() 打印表（TABLE)、索引(INDEX)、存储过程（PROCEDURE）源代码
 CURSOR C_OBJ(P_OWNER VARCHAR2, P_OBJECT_TYPE VARCHAR2, P_OBJECT_NAME VARCHAR2 ) IS    
SELECT OBJ.OWNER, OBJ.OBJECT_NAME, OBJ.OBJECT_TYPE,
  DBMS_METADATA.GET_DDL(OBJ.OBJECT_TYPE, OBJECT_NAME) SQL_TEXT
 FROM 
 SYS.DBA_OBJECTS OBJ
  WHERE OBJ.OBJECT_TYPE =  P_OBJECT_TYPE  -- 'TABLE' --
   AND OBJ.OWNER=  P_OWNER --'INFODBA'  --
   AND OBJ.OBJECT_NAME =  P_OBJECT_NAME -- 'ACCT_TABLE' --
   ORDER BY OBJ.OWNER ;

BEGIN   
 FOR R_LIST IN C_LIST LOOP 
	  --FOR R1 IN C_OBJ(P_OWNER=>'INFODBA', P_OBJECT_TYPE=>'TABLE', P_OBJECT_NAME =>'ACCT_TABLE') LOOP
	  FOR R1 IN C_OBJ(P_OWNER=>r_list.owner, P_OBJECT_TYPE=>r_list.OBJECT_TYPE, P_OBJECT_NAME =>R_LIST.OBJECT_NAME ) LOOP
		v_clob := r1.sql_text ;
		v_length := dbms_lob.getlength(v_clob);
		--v_length := 1 ;
		 --v_time := ceil( r_vi.text_length / v_block_size);
		 v_time := ceil( v_length / v_block_size);
		 if v_time = 0 then 
		   v_time := 1;
		 end if;

		 for i in 1..v_time loop
			v_temp := substr( v_clob,  v_block_size*(i-1)+1, v_block_size ) ; 
		  
			v_amount :=  v_block_size*(i-1)+1;
		   
		   dbms_output.put_line(v_temp);
		END LOOP; 
		 dbms_output.put_line('/');
	  END loop;
  
  END LOOP;
end ;
/