Dumping contents of LONG columns to a file

I've lost the original thread of this question, but contained below is a little script I used to dump the contents of the LONG columns of a database into a text file. Worked on Oracle 7.3.4.0.1 on NT last time I tried. It's probably not too effiecient, anyone else has any ideas on how to do it?

Regards,
Jason.

• Write_Long - Written by Jason Salter 18/05/99
• - jasonsalter_at_hotmail.com
  ---
  
• This procedure will write out data held in a LONG column to a
• server side OS file. It does this in 1,000 byte chunks.
  ---
  
• You can change the cursor statement below to dump the long columns
• for one or more tables.
  ---
  

CREATE OR REPLACE PROCEDURE write_long AS

---

• Declarative Section
  ---
  
• Variables v_LongOwner dba_tab_columns.owner%TYPE; v_LongTab dba_tab_columns.table_name%TYPE; v_LongCol dba_tab_columns.column_name%TYPE; v_RowID ROWID; v_Text varchar2(1000); v_TextLen INTEGER := 0; v_Pos INTEGER := 0; v_DynDDL_Curs INTEGER; v_DynDDL_SQL VARCHAR2(500); v_DynDDL_Dummy INTEGER;
• Handles fileHandle UTL_FILE.FILE_TYPE;
• Cursors Cursor c_Long IS select owner,table_name,column_name from dba_tab_columns where owner NOT IN ('SYS','SYSTEM') and data_type='LONG';

---

• Main Section
  ---
  

BEGIN

• Open the Dynamic DDL cursor workspace. v_DynDDL_Curs := DBMS_SQL.OPEN_CURSOR;
• Open the server-side destination file with WRITE access.
• You must remember to add the line utl_file_dir = c:\work
• into the INITsid.ora parameter file.
• Directory should already exist and you should have
• write permissions to it.

 fileHandle := UTL_FILE.FOPEN('c:\work', 'test.txt','W');

• Open our cursor filled with tables with long values.

 OPEN c_Long;
 LOOP
  FETCH c_Long INTO v_LongOwner, v_LongTab, v_LongCol;   EXIT WHEN c_Long%NOTFOUND;

• Build a dynamic SQL statement that selects the ROWID and LONG column from our
• cursor list. v_DynDDL_SQL := 'SELECT rowid,'||v_LongCol||' from '|| v_LongOwner||'.'||v_LongTab;
• Parse the statement DBMS_SQL.PARSE ( v_DynDDL_Curs, v_DynDDL_SQL , DBMS_SQL.V7 );
• Define the two columns we are interested in. DBMS_SQL.DEFINE_COLUMN_ROWID( v_DynDDL_Curs, 1, v_LongCol ); DBMS_SQL.DEFINE_COLUMN_LONG( v_DynDDL_Curs, 2 );
• Execute the statement to pull back the rows. v_DynDDL_Dummy := DBMS_SQL.EXECUTE( v_DynDDL_Curs );
• Pull back a row from the target table. LOOP IF DBMS_SQL.FETCH_ROWS( v_DynDDL_Curs ) = 0 THEN EXIT; END IF;
• Obtain the ROWID. DBMS_SQL.COLUMN_VALUE_ROWID( v_DynDDL_Curs, 1, v_RowID );
• Write a message to our output file. UTL_FILE.PUT_LINE(fileHandle,'**** Owner = '||v_LongOwner|| ' Table = '||v_LongTab|| ' Column = '||v_LongCol|| ' RowID = '||rowidtochar(v_RowID)||' ****');
• Loop round pulling out 1,000 byte chunks from the LONG column until
• it is exhausted. v_Pos := 0;

    LOOP
     DBMS_SQL.COLUMN_VALUE_LONG( v_DynDDL_Curs, 2, 1000, v_Pos, v_Text, V_TextLen );

     UTL_FILE.PUT_LINE(fileHandle, v_Text);

     EXIT WHEN v_TextLen = 0;

     v_Pos := V_Pos + v_TextLen;

• Loop round until the LONG column is empty. END LOOP;
• Loop round until all rows in the current table are exhausted. END LOOP;
• Loop round until all tables are exhausted. END LOOP;
• Close the cursor. CLOSE c_Long;
• Close the output file. UTL_FILE.FCLOSE(fileHandle);
• Close the Dynamic Cursor workspace. DBMS_SQL.CLOSE_CURSOR( v_DynDDL_Curs );
• Fin END; /