Shulou(Shulou.com)06/01 Report--

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

/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.