Scripts:dump clob列内容的脚本(慎用)lob_dump_clob.sql
来源:互联网 发布:循环大批量更新数据库 编辑:程序博客网 时间:2024/06/05 09:25
-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : lob_dump_clob.sql |
-- | CLASS : LOBs |
-- | PURPOSE : This script can be used to dump the contents of a CLOB column. |
-- | The user will be prompted for the OWNER, TABLE_NAME, and |
-- | COLUMN_NAME for the CLOB column to read from. The anonymous |
-- | PL/SQL block will write the contents of the CLOB to a file |
-- | named using the format: OWNER_TABLE_COLUMN_<counter>.out |
-- | An example would be: SCOTT_XML_DOCS_LOG_1.out |
-- | SCOTT_XML_DOCS_LOG_2.out |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
COLUMN current_user NEW_VALUE current_user NOPRINT;
SELECT rpad(instance_name, 17) current_instance, rpad(user, 13) current_user FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Dump the Contents of a CLOB Column |
PROMPT | Instance : ¤t_instance |
PROMPT | User : ¤t_user |
PROMPT +------------------------------------------------------------------------+
PROMPT
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET SERVEROUTPUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
ACCEPT oname PROMPT 'Enter Owner Name : '
ACCEPT tname PROMPT 'Enter Table Name : '
ACCEPT cname PROMPT 'Enter Column Name : '
ACCEPT wclause PROMPT 'SQL WHERE clause (including WHERE clause) : '
ACCEPT odir PROMPT 'Enter Output Directory : '
CREATE OR REPLACE DIRECTORY temp_dump_lob_dir AS '&odir';
DECLARE
-- +----------------------------------------------------+
-- | INCOMING VARIABLES |
-- +----------------------------------------------------+
v_oname VARCHAR2(100) := UPPER('&oname');
v_tname VARCHAR2(100) := UPPER('&tname');
v_cname VARCHAR2(100) := UPPER('&cname');
v_outdir VARCHAR2(30) := 'TEMP_DUMP_LOB_DIR';
v_wclause VARCHAR2(4000) := '&wclause';
-- +----------------------------------------------------+
-- | OUTPUT FILE VARIABLES |
-- +----------------------------------------------------+
v_out_filename VARCHAR2(500) := v_oname || '_' || v_tname || '_' || v_cname;
v_out_fileext VARCHAR2(4) := '.out';
v_out_filename_full VARCHAR2(500);
v_out_dirname VARCHAR2(2000);
v_file_count NUMBER := 0;
v_file_handle UTL_FILE.FILE_TYPE;
-- +----------------------------------------------------+
-- | DYNAMIC SQL VARIABLES |
-- +----------------------------------------------------+
TYPE v_lob_cur_typ IS REF CURSOR;
v_lob_cur v_lob_cur_typ;
v_sql_string VARCHAR2(4000);
-- +----------------------------------------------------+
-- | CLOB WRITE VARIABLES |
-- +----------------------------------------------------+
v_clob_loc CLOB;
v_buffer VARCHAR2(32767);
v_buffer_size CONSTANT BINARY_INTEGER := 32767;
v_amount BINARY_INTEGER;
v_offset NUMBER(38);
-- +----------------------------------------------------+
-- | EXCEPTIONS |
-- +----------------------------------------------------+
invalid_directory_path EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_directory_path, -29280);
table_does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_does_not_exist, -00942);
invalid_identifier EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_identifier, -00904);
SQL_cmd_not_prop_ended EXCEPTION;
PRAGMA EXCEPTION_INIT(SQL_cmd_not_prop_ended, -00933);
BEGIN
-- +----------------------------------------------------+
-- | ENABLE SERVER-SIDE OUTPUT |
-- +----------------------------------------------------+
DBMS_OUTPUT.ENABLE(1000000);
SELECT directory_path INTO v_out_dirname FROM all_directories WHERE directory_name = 'TEMP_DUMP_LOB_DIR';
v_sql_string := 'SELECT ' || v_cname || ' FROM ' || v_oname || '.' || v_tname || ' ' || v_wclause;
OPEN v_lob_cur FOR
v_sql_string;
LOOP
FETCH v_lob_cur INTO v_clob_loc;
EXIT WHEN v_lob_cur%NOTFOUND;
v_file_count := v_file_count + 1;
v_out_filename_full := v_out_filename || '_' || v_file_count || v_out_fileext;
v_file_handle := UTL_FILE.FOPEN(v_outdir, v_out_filename_full, 'w', 32767);
v_amount := v_buffer_size;
v_offset := 1;
DECLARE
invalid_LOB_locator EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_LOB_locator, -06502);
BEGIN
WHILE v_amount >= v_buffer_size
LOOP
DBMS_LOB.READ(
lob_loc => v_clob_loc,
amount => v_amount,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_amount;
UTL_FILE.PUT(
file => v_file_handle,
buffer => v_buffer);
UTL_FILE.FFLUSH(file => v_file_handle);
END LOOP;
EXCEPTION
WHEN invalid_LOB_locator THEN
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '| *** ERROR *** |');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.NEW_LINE(file => v_file_handle);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => 'Invalid LOB Locator Exception for :');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '===================================');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> ' || v_oname || '.' || v_tname || '.' || v_cname);
UTL_FILE.NEW_LINE(file => v_file_handle);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => 'SQL Text:');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '===================================');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> ' || v_sql_string);
UTL_FILE.FFLUSH(file => v_file_handle);
WHEN others THEN
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '| *** ERROR *** |');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.NEW_LINE(file => v_file_handle);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => 'WHEN OTHERS ERROR');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '=================');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> SQL CODE : ' || SQLCODE);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> SQL ERROR MESSAGE : ' || SQLERRM);
UTL_FILE.FFLUSH(file => v_file_handle);
END;
UTL_FILE.FCLOSE(v_file_handle);
END LOOP;
CLOSE v_lob_cur;
DBMS_OUTPUT.PUT_LINE('Wrote out ' || v_file_count || ' file(s) to ' || v_out_dirname || '.');
EXCEPTION
WHEN invalid_directory_path THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : Invalid Directory Path: ' || v_outdir);
WHEN table_does_not_exist THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : Table Not Found.');
DBMS_OUTPUT.PUT_LINE('--> SQL: ' || v_sql_string);
WHEN invalid_identifier THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : Invalid Identifier.');
DBMS_OUTPUT.PUT_LINE('--> SQL: ' || v_sql_string);
WHEN SQL_cmd_not_prop_ended THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : SQL command not properly ended.');
DBMS_OUTPUT.PUT_LINE('--> SQL: ' || v_sql_string);
END;
/
DROP DIRECTORY temp_dump_lob_dir;
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : lob_dump_clob.sql |
-- | CLASS : LOBs |
-- | PURPOSE : This script can be used to dump the contents of a CLOB column. |
-- | The user will be prompted for the OWNER, TABLE_NAME, and |
-- | COLUMN_NAME for the CLOB column to read from. The anonymous |
-- | PL/SQL block will write the contents of the CLOB to a file |
-- | named using the format: OWNER_TABLE_COLUMN_<counter>.out |
-- | An example would be: SCOTT_XML_DOCS_LOG_1.out |
-- | SCOTT_XML_DOCS_LOG_2.out |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
COLUMN current_user NEW_VALUE current_user NOPRINT;
SELECT rpad(instance_name, 17) current_instance, rpad(user, 13) current_user FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Dump the Contents of a CLOB Column |
PROMPT | Instance : ¤t_instance |
PROMPT | User : ¤t_user |
PROMPT +------------------------------------------------------------------------+
PROMPT
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET SERVEROUTPUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
ACCEPT oname PROMPT 'Enter Owner Name : '
ACCEPT tname PROMPT 'Enter Table Name : '
ACCEPT cname PROMPT 'Enter Column Name : '
ACCEPT wclause PROMPT 'SQL WHERE clause (including WHERE clause) : '
ACCEPT odir PROMPT 'Enter Output Directory : '
CREATE OR REPLACE DIRECTORY temp_dump_lob_dir AS '&odir';
DECLARE
-- +----------------------------------------------------+
-- | INCOMING VARIABLES |
-- +----------------------------------------------------+
v_oname VARCHAR2(100) := UPPER('&oname');
v_tname VARCHAR2(100) := UPPER('&tname');
v_cname VARCHAR2(100) := UPPER('&cname');
v_outdir VARCHAR2(30) := 'TEMP_DUMP_LOB_DIR';
v_wclause VARCHAR2(4000) := '&wclause';
-- +----------------------------------------------------+
-- | OUTPUT FILE VARIABLES |
-- +----------------------------------------------------+
v_out_filename VARCHAR2(500) := v_oname || '_' || v_tname || '_' || v_cname;
v_out_fileext VARCHAR2(4) := '.out';
v_out_filename_full VARCHAR2(500);
v_out_dirname VARCHAR2(2000);
v_file_count NUMBER := 0;
v_file_handle UTL_FILE.FILE_TYPE;
-- +----------------------------------------------------+
-- | DYNAMIC SQL VARIABLES |
-- +----------------------------------------------------+
TYPE v_lob_cur_typ IS REF CURSOR;
v_lob_cur v_lob_cur_typ;
v_sql_string VARCHAR2(4000);
-- +----------------------------------------------------+
-- | CLOB WRITE VARIABLES |
-- +----------------------------------------------------+
v_clob_loc CLOB;
v_buffer VARCHAR2(32767);
v_buffer_size CONSTANT BINARY_INTEGER := 32767;
v_amount BINARY_INTEGER;
v_offset NUMBER(38);
-- +----------------------------------------------------+
-- | EXCEPTIONS |
-- +----------------------------------------------------+
invalid_directory_path EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_directory_path, -29280);
table_does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_does_not_exist, -00942);
invalid_identifier EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_identifier, -00904);
SQL_cmd_not_prop_ended EXCEPTION;
PRAGMA EXCEPTION_INIT(SQL_cmd_not_prop_ended, -00933);
BEGIN
-- +----------------------------------------------------+
-- | ENABLE SERVER-SIDE OUTPUT |
-- +----------------------------------------------------+
DBMS_OUTPUT.ENABLE(1000000);
SELECT directory_path INTO v_out_dirname FROM all_directories WHERE directory_name = 'TEMP_DUMP_LOB_DIR';
v_sql_string := 'SELECT ' || v_cname || ' FROM ' || v_oname || '.' || v_tname || ' ' || v_wclause;
OPEN v_lob_cur FOR
v_sql_string;
LOOP
FETCH v_lob_cur INTO v_clob_loc;
EXIT WHEN v_lob_cur%NOTFOUND;
v_file_count := v_file_count + 1;
v_out_filename_full := v_out_filename || '_' || v_file_count || v_out_fileext;
v_file_handle := UTL_FILE.FOPEN(v_outdir, v_out_filename_full, 'w', 32767);
v_amount := v_buffer_size;
v_offset := 1;
DECLARE
invalid_LOB_locator EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_LOB_locator, -06502);
BEGIN
WHILE v_amount >= v_buffer_size
LOOP
DBMS_LOB.READ(
lob_loc => v_clob_loc,
amount => v_amount,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_amount;
UTL_FILE.PUT(
file => v_file_handle,
buffer => v_buffer);
UTL_FILE.FFLUSH(file => v_file_handle);
END LOOP;
EXCEPTION
WHEN invalid_LOB_locator THEN
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '| *** ERROR *** |');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.NEW_LINE(file => v_file_handle);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => 'Invalid LOB Locator Exception for :');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '===================================');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> ' || v_oname || '.' || v_tname || '.' || v_cname);
UTL_FILE.NEW_LINE(file => v_file_handle);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => 'SQL Text:');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '===================================');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> ' || v_sql_string);
UTL_FILE.FFLUSH(file => v_file_handle);
WHEN others THEN
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '| *** ERROR *** |');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '+----------------------------+');
UTL_FILE.NEW_LINE(file => v_file_handle);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => 'WHEN OTHERS ERROR');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => '=================');
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> SQL CODE : ' || SQLCODE);
UTL_FILE.PUT_LINE(file => v_file_handle, buffer => ' --> SQL ERROR MESSAGE : ' || SQLERRM);
UTL_FILE.FFLUSH(file => v_file_handle);
END;
UTL_FILE.FCLOSE(v_file_handle);
END LOOP;
CLOSE v_lob_cur;
DBMS_OUTPUT.PUT_LINE('Wrote out ' || v_file_count || ' file(s) to ' || v_out_dirname || '.');
EXCEPTION
WHEN invalid_directory_path THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : Invalid Directory Path: ' || v_outdir);
WHEN table_does_not_exist THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : Table Not Found.');
DBMS_OUTPUT.PUT_LINE('--> SQL: ' || v_sql_string);
WHEN invalid_identifier THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : Invalid Identifier.');
DBMS_OUTPUT.PUT_LINE('--> SQL: ' || v_sql_string);
WHEN SQL_cmd_not_prop_ended THEN
DBMS_OUTPUT.PUT_LINE('** ERROR ** : SQL command not properly ended.');
DBMS_OUTPUT.PUT_LINE('--> SQL: ' || v_sql_string);
END;
/
DROP DIRECTORY temp_dump_lob_dir;
0 0
- Scripts:dump clob列内容的脚本(慎用)lob_dump_clob.sql
- Scripts:dump nclob列的脚本(慎用)lob_dump_nclob.sql
- Scripts:dump blob列内容的脚本(慎用)lob_dump_blob.sql
- Scripts:显示有LOB列的表的脚本(慎用)LobData.sql
- Scripts:此脚本慎用lob_fragmentation_user.sql
- Scripts:根据sql_id创建sql_profile的脚本(此脚本要慎用)gps.sql
- Scripts:重新编译失效对象的脚本(生产环境慎用)dba_recompile_invalid_objects.sql
- Scripts:分析RAC hang的脚本(此脚本要慎用,在某些版本下可能会导致系统重启)RACDIAG.SQL
- Scripts:列出用户信息的脚本sec_users.sql
- Scripts:重建索引的脚本(看懂脚本再用)rebuild_indx.sql
- Scripts:查看回滚的SQL的脚本 huigun.sql
- Scripts:生成随机数的SQL脚本dba_random_number.sql
- Scripts:查询等待事件的SQL脚本owi_event_names.sql
- Scripts:给出系统pid找出sql的脚本pid4sql.sql
- Scripts:查看未提交的事务的脚本CurrentActivity.sql
- Scripts:报告数据库中的top segment的脚本dba_top_segments.sql
- Scripts:显示AWR中执行计划的脚本dplan_allstats.sql
- Scripts:诊断DRM信息的脚本DRMDIAG.SQL
- J2EE学习篇之--Spring技术详解
- Scripts:dump nclob列的脚本(慎用)lob_dump_nclob.sql
- 探查油田——广度搜索
- java程序员必知的8大排序
- Java Swing界面编程(12)---JPanel布局
- Scripts:dump clob列内容的脚本(慎用)lob_dump_clob.sql
- LeetCode N-Queens II
- week14 清空行
- 链表常见操作:逆置(反转) .
- pangDiJlA诒稚勺桓巳柏彼
- Scripts:dump blob列内容的脚本(慎用)lob_dump_blob.sql
- redis pipeline的使用
- unity 面试题
- Scripts:显示有LOB列的表的脚本(慎用)LobData.sql