script to create comma delimited files

From: <kal121_at_my-deja.com>
Date: Fri, 06 Aug 1999 21:13:47 GMT
Message-ID: <7ofj60$qr3$1@nnrp1.deja.com>

I'm sure everybody has a version of this, but here's one I wrote which works pretty well, and is very simple to use - compared to other scripts I've tried in the past. Let me know what you think. I'm sharing this because I've been very frustrated in the past trying to create just a simple comma-delimited file from Oracle. There really is no "simple" way, I've discovered, and I got tired of using Access...

/*

** CREATE_COMMA_FILE.SQL

• K. List
• 07/21/99
  **
  
• This procedure will convert 1.) A SINGLE TABLE or 2.) ALL USER_TABLES
• into a comma-delimited file(s) with quotes "" for use by SQL*Loader.
  **
  
• USAGE NOTES:
• To call this procedure:
• SQLPLUS> exec create_comma_file ('tablename') for a single table (specify 'tablename')
• SQLPLUS> exec create_comma_file ('all') for all tables in a user's schema
  **
  
• The file will be named "tablename.txt" and is saved in the directory specified
• by the "utl_file_dir" parameter in your init.ora file, so be sure to set
• the utl_file_dir parameter before running this procedure!
  **
  
• Search and replace this string - d:\klfolder\test - with your own path
  **
  
• This procedure will NOT work on tables with LONG RAW columns */

CREATE OR REPLACE PROCEDURE create_comma_file (tab_name_in IN VARCHAR2) AS

file1					UTL_FILE.FILE_TYPE;
column_string			VARCHAR2(4000);
last_column_data_type	VARCHAR2(40);
s1						VARCHAR2(4000);
cursor1					INTEGER;
ret_val					INTEGER;
tab_name_in_converted	VARCHAR2(255);

CURSOR c1a IS
SELECT table_name FROM user_tables
WHERE TABLE_NAME = UPPER(tab_name_in);

CURSOR c1 IS
SELECT table_name FROM user_tables
WHERE num_rows > 0
AND table_name NOT IN

        (SELECT table_name FROM user_tab_columns WHERE data_type = 'LONG RAW'); CURSOR c2 (table_name_in VARCHAR2) IS
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = table_name_in
ORDER BY column_id; /* this is very important to maintain order in the text files!!! */

CURSOR c3 IS
SELECT table_name FROM user_tab_columns WHERE data_type = 'LONG RAW';

long_raw_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (long_raw_exception, -932);

empty_table_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (empty_table_exception, -1002);

BEGIN cursor1 := DBMS_SQL.OPEN_CURSOR;

/* CREATE FILE FOR ONE TABLE ONLY */
IF UPPER(tab_name_in) != 'ALL' THEN

        /* don't really need a loop here, but nested cursor c2 doesn't work otherwise */

        FOR c1a_rec IN c1a LOOP

                /* The file specified here should be the same as utl_file_dir */

                file1 := UTL_FILE.FOPEN('d:\klfolder\test', c1a_rec.table_name||'.txt', 'W');

                /* build up a concatenated string of columns, unique for each table */

                FOR c2_rec IN c2 (c1a_rec.table_name) LOOP

			IF c2_rec.data_type = 'VARCHAR2' THEN
				column_string :=
column_string||'''"'''||'||'||c2_rec.column_name||'||''",''||';
			ELSE
				column_string :=
column_string||c2_rec.column_name||'||'',''||';
			END IF;

			last_column_data_type := c2_rec.data_type;

		END LOOP;

		IF last_column_data_type = 'VARCHAR2' THEN

			/* remove extraneous characters from end of
string */
			column_string := SUBSTR(column_string, 1,
LENGTH(column_string)-8);
			column_string := column_string||'||'||'''"''';

		ELSE

			column_string := SUBSTR(column_string, 1,

LENGTH(column_string)-7);

                END IF;

		DBMS_SQL.PARSE(	cursor1,
					'SELECT '||column_string||' FROM
'||c1a_rec.table_name,
					DBMS_SQL.NATIVE);

		DBMS_SQL.DEFINE_COLUMN(cursor1, 1, s1, 4000);

		ret_val := DBMS_SQL.EXECUTE_AND_FETCH (cursor1);

		LOOP
			DBMS_SQL.COLUMN_VALUE (cursor1, 1, s1);

			UTL_FILE.PUT_LINE(file1, s1);

			EXIT WHEN DBMS_SQL.FETCH_ROWS (cursor1) = 0;
		END LOOP;

		UTL_FILE.FCLOSE(file1);

	END LOOP;

/* CREATE FILES FOR *ALL* USER TABLES */ ELSE

	/* for each table in user_tables, loop to get the table_name */
	FOR c1_rec IN c1 LOOP

		/* The file specified here should be the same as
utl_file_dir */
		file1 := UTL_FILE.FOPEN('d:\klfolder\test',

c1_rec.table_name||'.txt', 'W');

                /* build up a concatenated string of columns, unique for each table */

                FOR c2_rec IN c2 (c1_rec.table_name) LOOP

			IF c2_rec.data_type = 'VARCHAR2' THEN
				column_string :=
column_string||'''"'''||'||'||c2_rec.column_name||'||''",''||';
			ELSE
				column_string :=
column_string||c2_rec.column_name||'||'',''||';
			END IF;

			last_column_data_type := c2_rec.data_type;

		END LOOP;

		IF last_column_data_type = 'VARCHAR2' THEN

			/* remove extraneous characters from end of
string */
			column_string := SUBSTR(column_string, 1,
LENGTH(column_string)-8);
			column_string := column_string||'||'||'''"''';

		ELSE

			column_string := SUBSTR(column_string, 1,

LENGTH(column_string)-7);

                END IF;

		DBMS_SQL.PARSE(	cursor1,
					'SELECT '||column_string||' FROM
'||c1_rec.table_name,
					DBMS_SQL.NATIVE);

		DBMS_SQL.DEFINE_COLUMN(cursor1, 1, s1, 4000);

		ret_val := DBMS_SQL.EXECUTE_AND_FETCH (cursor1);

		LOOP
			DBMS_SQL.COLUMN_VALUE (cursor1, 1, s1);

			UTL_FILE.PUT_LINE(file1, s1);

			EXIT WHEN DBMS_SQL.FETCH_ROWS (cursor1) = 0;
		END LOOP;

		UTL_FILE.FCLOSE(file1);
		column_string := '';	/* reset column_string variable

*/

        END LOOP;         DBMS_OUTPUT.PUT_LINE ('Tables which did not have files created for them because they contain long raw columns:');

	FOR c3_rec IN c3 LOOP
		DBMS_OUTPUT.PUT_LINE (c3_rec.table_name);
	END LOOP;

END IF;

	EXCEPTION
	WHEN long_raw_exception THEN
		DBMS_OUTPUT.PUT_LINE (CHR(9));
		DBMS_OUTPUT.PUT_LINE ('ERROR: Cannot run this procedure
against a table with a long raw column');
	WHEN empty_table_exception THEN
		DBMS_OUTPUT.PUT_LINE (CHR(9));
		DBMS_OUTPUT.PUT_LINE ('ERROR: Cannot run this procedure

against an empty table');

END;
/

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 06 1999 - 16:13:47 CDT