oracle plsql utl_file
前端之家收集整理的这篇文章主要介绍了
oracle plsql utl_file，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
@H_
301_0@
set SERVEROUT on DECLARE tab_
sql varchar2(4000); tab_lob clob; TYPE ddl_type IS REF CURSOR; ddl_cur ddl_type; type tab_ddl_type is record( ddl_con clob ); tab_ddl tab_ddl_type; cursor tab_cur is select DISTINCT object_type,object_name,OWNER from dba_procedures where owner=‘FWPROD‘ ; file utl_file.file_type; clob_length number(20); clob_part VARCHAR2(1024); offset NUMBER := 1; BEGIN file := utl_file.fopen(‘DATA_PUMP_DIR‘,‘mes_procedure_ddl.log‘,‘ab‘); for i in tab_cur loop tab_
sql := ‘select dbms_
Metadata.get_ddl(‘‘‘||i.object_type||‘‘‘,‘‘‘||i.object_name||‘‘‘,‘||‘‘‘‘||i.owner||‘‘‘) as ddl_con from dual‘; DBMS_OUTPUT.PUT_LINE(tab_
sql); open ddl_cur for tab_
sql; FETCH ddl_cur INTO tab_ddl; --DBMS_OUTPUT.PUT_LINE(tab_ddl.ddl_con); clob_length := LENGTH(tab_ddl.ddl_con); offset := 1; LOOP EXIT WHEN offset >= clob_length; clob_part := DBMS_LOB.SUBSTR (tab_ddl.ddl_con,1024,offset); UTL_FILE.PUT_RAW(file,utl_raw.cast_to_raw(clob_part)); UTL_FILE.FFLUSH(file); offset := offset + 1024; END LOOP; close ddl_cur; END LOOP; utl_file.fclose(file); END; /
