update : 29-04-2017

DBA Script : lob_dump_blob.sql

-- | FILE : lob_dump_blob.sql |
-- +----------------------------------------------------------------------------+
-- | |
-- | |
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : lob_dump_blob.sql |
-- | CLASS : LOBs |
-- | PURPOSE : This script can be used to dump the contents of a BLOB column. |
-- | The user will be prompted for the OWNER, TABLE_NAME, and |
-- | COLUMN_NAME for the BLOB column to read from. The anonymous |
-- | PL/SQL block will write the contents of the BLOB to a file |
-- | named using the format: OWNER_TABLE_COLUMN_.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 BLOB 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);
-- +----------------------------------------------------+
-- | BLOB WRITE VARIABLES |
-- +----------------------------------------------------+
v_blob_loc BLOB;
v_buffer RAW(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_blob_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_blob_loc,
amount => v_amount,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_amount;
UTL_FILE.PUT_RAW(
file => v_file_handle,
buffer => v_buffer,
autoflush => true);
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;

End Script

Oracle Database Error Messages



Oracle Database High Availability Any organization evaluating a database solution for enterprise data must also evaluate the High Availability (HA) capabilities of the database. Data is one of the most critical business assets of an organization. If this data is not available and/or not protected, companies may stand to lose millions of dollars in business downtime as well as negative publicity. Building a highly available data infrastructure is critical to the success of all organizations in today's fast moving economy.

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.