Re: ascii text unload

Use the following customizable procedures set and table definition. This creates flat delimited text files that can be loaded by Oracle using SQL*Loader, or by Sybase using bcp, or by Informix using informix loader, or by Excel using CSV file import, in customizable languages.

The UTL_LOADFMT table describes a format (samples included here) given its code that can be used to export and reload a file with SQL*Loader. This table is accessed when executing the procedure UTL_UNLOAD from any SQL*Plus or SQL*Dba or Server Manager SQL prompt. It generates files on the server using the UTL_FILE package, taking care of separators, and trying to reencode conveniently characters that cause problems, and converting dates using a compatible date format. When exporting a file, it can be reimported via SQL*Loader into Oracle using a control file which can be generated directly from the table definition in catalog and using the same format code. The procedure UTL_CONTROL below generates such control files on the server. All this uses dynamic SQL and requires that the specified PATHNAME parameter is set in the database configuration file (UTL_FILE_DIR variable must be set to include that directory, or be set to *).

Don't ask me for more support. Try this at your own risks. I'm not employed by Oracle.

• Cut here ----
• Table of formats for delimited text files ----
• When the strings prefix is NULL, use the suffix for all datatypes of fields
• else use the prefix and suffix only for string datatypes. DROP TABLE utl_loadfmt; CREATE TABLE utl_loadfmt( format CHAR(16), -- name prefix VARCHAR2(1) DEFAULT NULL, -- start of string esc VARCHAR2(1) DEFAULT '\', -- code to escape esc2 VARCHAR2(8) DEFAULT '\\', -- ... substitute suffix VARCHAR2(1) DEFAULT NULL, -- end of string suffix2 VARCHAR2(8) DEFAULT NULL, -- ... substitute sep VARCHAR2(1) DEFAULT '|', -- field separator sep2 VARCHAR2(8) DEFAULT '\|', -- ... substitute rs VARCHAR2(1) DEFAULT CHR(10), -- record separator rs2 VARCHAR2(8) DEFAULT CHR(9), -- ... substitute datfmt VARCHAR2(32) DEFAULT 'DD-MON-YY', -- for TO_CHAR(date) nls_language VARCHAR(32) DEFAULT 'American' -- TO_DATE/TO_CHAR ); CREATE UNIQUE INDEX utl_loadfmt_pk ON utl_loadfmt(format);
• Oracle SQL*Loader (no prefix, no suffix, no NL, escape OK) ----
• This first format is the default for the next procedure below ---- INSERT INTO utl_loadfmt VALUES
  ('ORAtUS',NULL,'\','\\',NULL,NULL,CHR(9),CHR(1),CHR(10),CHR(7)
  ,'DD-MON-YY','American'); INSERT INTO utl_loadfmt VALUES
  ('ORAtFR',NULL,'\','\\',NULL,NULL,CHR(9),CHR(1),CHR(10),CHR(7)
  ,'DD-MON-YY','French'); INSERT INTO utl_loadfmt VALUES
  ('ORA|US',NULL,'\','\\',NULL,NULL,'|','\|',CHR(10),CHR(7)
  ,'DD-MON-YY','American'); INSERT INTO utl_loadfmt VALUES
  ('ORA|FR',NULL,'\','\\',NULL,NULL,'|','\|',CHR(10),CHR(7)
  ,'DD-MON-YY','French'); COMMIT;
• Sybase BCP (no prefix, no suffix, no NL, no escape) ---- INSERT INTO utl_loadfmt VALUES
  ('SYBtUS',NULL,NULL,NULL,NULL,NULL,CHR(9),CHR(1),CHR(10),CHR(7)
  ,'Mon DD YYYY HH:MI:SS":000"AM', 'American'); INSERT INTO utl_loadfmt VALUES
  ('SYBtFR',NULL,NULL,NULL,NULL,NULL,CHR(9),CHR(1),CHR(10),CHR(7)
  ,'Mon DD YYYY HH:MI:SS":000"AM', 'French'); INSERT INTO utl_loadfmt VALUES
  ('SYB^US',NULL,NULL,NULL,NULL,NULL,'^',CHR(1),CHR(10),CHR(7)
  ,'Mon DD YYYY HH:MI:SS":000"AM', 'American'); INSERT INTO utl_loadfmt VALUES
  ('SYB^FR',NULL,NULL,NULL,NULL,NULL,'^',CHR(1),CHR(10),CHR(7)
  ,'Mon DD YYYY HH:MI:SS":000"AM', 'French'); INSERT INTO utl_loadfmt VALUES
  ('SYB|US',NULL,NULL,NULL,NULL,NULL,'|',CHR(1),CHR(10),CHR(7)
  ,'Mon DD YYYY HH:MI:SS":000"AM', 'American'); INSERT INTO utl_loadfmt VALUES
  ('SYB|FR',NULL,NULL,NULL,NULL,NULL,'|',CHR(1),CHR(10),CHR(7)
  ,'Mon DD YYYY HH:MI:SS":000"AM', 'French'); INSERT INTO utl_loadfmt VALUES
  ('SYB`US',NULL,NULL,NULL,NULL,NULL,'`',CHR(1),CHR(10),CHR(7)
  ,'Mon DD YYYY HH:MI:SS":000"AM', 'American'); INSERT INTO utl_loadfmt VALUES
  ('SYB`FR',NULL,NULL,NULL,NULL,NULL,'`',CHR(1),CHR(10),CHR(7)
  ,'Mon DD YYYY HH:MI:SS":000"AM', 'French'); COMMIT;
• Informix On-Load (no prefix, no sep, suffix |, NL OK, escape) ---- INSERT INTO utl_loadfmt VALUES
  ('IFX|US',NULL,'\','\\','|','\|',NULL,NULL,CHR(10),'\'||CHR(10)
  ,'MM/DD/YYYY', 'American'); INSERT INTO utl_loadfmt VALUES
  ('IFX|FR',NULL,'\','\\','|','\|',NULL,NULL,CHR(10),'\'||CHR(10)
  ,'DD/MM/YYYY', 'French'); COMMIT;
• Microsoft Excel CSV (prefix & suffix, NL OK, w/o escape) ---- INSERT INTO utl_loadfmt VALUES
  ('CSVtUS','"',NULL,NULL,'"','""',CHR(9),CHR(9),CHR(10),CHR(10)
  ,'""MM/DD/YYYY HH:MIAM""', 'American'); INSERT INTO utl_loadfmt VALUES
  ('CSVtFR','"',NULL,NULL,'"','""',CHR(9),CHR(9),CHR(10),CHR(10)
  ,'""DD/MM/YYYY HH24:MI""', 'French'); INSERT INTO utl_loadfmt VALUES
  ('CSV,US','"',NULL,NULL,'"','""',',',',',CHR(10),CHR(10)
  ,'""MM/DD/YYYY HH:MIAM""', 'American'); INSERT INTO utl_loadfmt VALUES
  ('CSV,FR','"',NULL,NULL,'"','""',',',',',CHR(10),CHR(10)
  ,'""DD/MM/YYYY HH24:MI""', 'French'); INSERT INTO utl_loadfmt VALUES
  ('CSV;US','"',NULL,NULL,'"','""',';',';',CHR(10),CHR(10)
  ,'""DD/MM/YYYY HH:MIAM""', 'American'); INSERT INTO utl_loadfmt VALUES
  ('CSV;FR','"',NULL,NULL,'"','""',';',';',CHR(10),CHR(10)
  ,'""DD/MM/YYYY HH24:MI""', 'French'); COMMIT;
• Unload a table into a delimited textfile on server ---- CREATE OR REPLACE PROCEDURE utl_control( tablename IN VARCHAR2, pathname IN VARCHAR2, filename IN VARCHAR2, ctrlname IN VARCHAR2, formatname IN CHAR DEFAULT 'ORAtUS') -- for Oracle (US) with tabs IS v_prefix VARCHAR2(1); v_esc VARCHAR2(1); v_esc2 VARCHAR2(8); v_suffix VARCHAR2(1); v_suffix2 VARCHAR2(8); v_sep VARCHAR2(1); v_sep2 VARCHAR2(8); v_rs VARCHAR2(1); v_rs2 VARCHAR2(8); v_datfmt VARCHAR2(32); v_nls_language VARCHAR2(32); genprefix VARCHAR2(32); genesc VARCHAR2(32); genesc2 VARCHAR2(32); gensuffix VARCHAR2(32); gensuffix2 VARCHAR2(32); gensep VARCHAR2(32); gensep2 VARCHAR2(32); genrs VARCHAR2(32); genrs2 VARCHAR2(32); v_tablename VARCHAR2(32); v_last_column_id NUMBER; CURSOR cur IS SELECT c.column_id, c.column_name, c.data_type, -- explicit internal datatype name c.data_length, -- useful only for CHAR/VARCHAR2 c.data_precision, c.data_scale, -- not NULL only for NUMBER c.nullable -- 'Y' or 'N' FROM user_tab_columns c WHERE c.table_name = v_tablename ORDER BY c.column_id; line VARCHAR2(255); control_file UTL_FILE.FILE_TYPE; BEGIN
  • check that table exists (else raise an exception) SELECT table_name INTO v_tablename FROM user_tables WHERE table_name = tablename OR table_name = UPPER(tablename);
  • find format options in the description table for the given format name
  • (one row retreived, raise an exception if format name not found) SELECT u.prefix, u.esc, u.esc2, u.suffix, u.suffix2, u.sep, u.sep2, u.rs, u.rs2, u.datfmt, u.nls_language INTO v_prefix, v_esc, v_esc2, v_suffix, v_suffix2, v_sep, v_sep2, v_rs, v_rs2, v_datfmt, v_nls_language FROM utl_loadfmt u WHERE u.format = formatname;
  • fix format options into constants used to build the dynamic query IF v_prefix IS NULL THEN genprefix := NULL; ELSIF v_prefix = '''' THEN genprefix := ''''''''''; ELSE genprefix := '''' || v_prefix || ''''; END IF; IF v_esc IS NULL THEN genesc := NULL; ELSIF v_esc = CHR(9) THEN genesc := 'X''09'''; ELSE genesc := '''' || REPLACE(v_esc, '''', '''''') || ''''; END IF; IF v_esc2 IS NULL THEN genesc2 := NULL; ELSIF v_esc2 = CHR(9) THEN genesc2 := 'X''09'''; ELSE genesc2 := '''' || REPLACE(v_esc2, '''', '''''') || ''''; END IF; IF v_suffix IS NULL THEN gensuffix := NULL; ELSIF v_suffix = '''' THEN gensuffix := ''''''''''; ELSE gensuffix := '''' || v_suffix || ''''; END IF; IF v_suffix2 IS NULL THEN gensuffix2 := NULL; ELSIF v_suffix2 = CHR(9) THEN gensuffix2 := 'X''09'''; ELSE gensuffix2 := '''' || REPLACE(v_suffix2, '''', '''''') || ''''; END IF; IF v_sep IS NULL THEN gensep := NULL; ELSIF v_sep = '''' THEN gensep := ''''''''''; ELSIF v_sep = CHR(9) THEN gensep := 'X''09'''; ELSE gensep := '''' || v_sep || ''''; END IF; IF v_sep2 IS NULL THEN gensep2 := NULL; ELSIF v_sep2 = CHR(9) THEN gensep2 := 'X''09'''; ELSE gensep2 := '''' || REPLACE(v_sep2, '''', '''''') || ''''; END IF; IF v_rs IS NULL THEN genrs := NULL; ELSIF v_rs = CHR(9) THEN genrs := 'X''09'''; ELSIF v_rs = CHR(10) THEN genrs := 'X''0A'''; ELSIF v_rs = '''' THEN genrs := ''''''''''; ELSE genrs := '''' || v_rs || ''''; END IF;
  • now create a file to save SQL*Loader control into
  • may raise an exception if pathname not authorized in utl_file_dir! control_file := UTL_FILE.FOPEN(pathname, ctrlname, 'w'); UTL_FILE.PUT_LINE(control_file, 'LOAD DATA INFILE '||filename); UTL_FILE.PUT_LINE(control_file, 'PRESERVE BLANKS'); UTL_FILE.PUT_LINE(control_file, 'INTO TABLE "'||v_tablename||'" TRUNCATE');
  • if prefix is set, don't add prefix or suffix for NUMBER (CSV)
  • add suffix for NUMBER only if no prefix is set (Informix) line := 'FIELDS';
  • always accept prefix and suffix for CHARs and VARCHARs (CSV) IF v_prefix IS NOT NULL THEN line := line || ' ENCLOSED BY ' || genprefix; IF v_suffix IS NOT NULL THEN line := line || ' AND ' || gensuffix; END IF; END IF; UTL_FILE.PUT_LINE(control_file, line); UTL_FILE.PUT_LINE(control_file, ' TRAILING NULLCOLS');
  • Determine the last column_id before proceeding SELECT MAX(c.column_id) INTO v_last_column_id FROM user_tab_columns c WHERE c.table_name = v_tablename;
  • find and describe the table, field by field
  • and build a line of load format for each field FOR rec IN cur LOOP
    • check if leading '(' or ',' must prefix the field name IF rec.column_id = 1 THEN line := '( ' || rec.column_name; ELSE line := ', ' || rec.column_name; END IF;
    • depending on datatype of current field, generate delimitation IF rec.data_type = 'NUMBER' THEN
      • Use CHAR instead of DATE for more flexibility by SQL formatting IF rec.data_precision IS NULL THEN
        • Unconstrained NUMBER, same as NUMBER(38), so we should
        • allocate space for CHAR(39). But this datatype is stupidly
        • assigned for columns created as TINYINT, SMALLINT and INTEGER!
        • So we will limit NUMBER to the precision required by INTEGER
        • i.e. NUMBER(9) formatted as CHAR(10). line := line || CHR(9) || 'CHAR(10)'; ELSIF rec.data_scale = 0 THEN line := line || CHR(9) || 'CHAR(' || TO_CHAR(1 + rec.data_precision) || ')'; ELSE line := line || CHR(9) || 'CHAR(' || TO_CHAR(1 + rec.data_precision + 1) || ')'; END IF; ELSIF rec.data_type = 'FLOAT' THEN
      • Use FLOAT, no formatting line := line || CHR(9) || 'FLOAT EXTERNAL(32)'; ELSIF rec.data_type = 'CHAR' OR rec.data_type = 'VARCHAR2' OR rec.data_type = 'LONG' THEN IF rec.data_type = 'LONG' THEN line := line || CHR(9) || 'CHAR(2000)'; ELSE line := line || CHR(9) || 'CHAR(' || TO_CHAR(rec.data_length) || ')'; END IF; ELSIF rec.data_type = 'DATE' THEN
      • Use CHAR instead of DATE for more flexibility by SQL formatting
      • This allows additional NLS settings...
      • Here compute the maximum acceptable length line := line || CHR(9) || 'CHAR(' || TO_CHAR(LENGTH( REPLACE( -- remove all single " format specifiers REPLACE( -- shorten "" format to one char literal REPLACE( -- lengthen MONTH format to longest SEPTEMBER REPLACE( -- lengthen DAY format to longest WEDNESDAY REPLACE( -- shorten HH24 format to effective HH UPPER(v_datfmt), 'HH24', 'HH'), 'DAY', 'WEDNESDAY'), 'MONTH', 'SEPTEMBER'), '""','-'), '"', NULL))) || ')'; ELSIF rec.data_type = 'RAW' OR rec.data_type = 'LONG RAW' THEN IF rec.data_type = 'RAW' THEN line := line || CHR(9) || 'CHAR(' || TO_CHAR(rec.data_length * 2) || ')'; ELSE line := line || CHR(9) || 'CHAR(4000)'; END IF; ELSIF rec.data_type = 'ROWID' THEN line := line || CHR(9) || 'CHAR(18)'; ELSE -- MLSLABEL NULL; END IF;
    • for the last column, the default terminator is not appropriate IF rec.column_id = v_last_column_id AND v_suffix IS NULL AND v_sep <> v_rs THEN IF v_rs <> CHR(10) THEN line := line || ' TERMINATED BY ' || genrs; END IF; ELSIF v_sep IS NOT NULL THEN line := line || ' TERMINATED BY ' || gensep; ELSIF v_prefix IS NULL AND v_suffix IS NOT NULL THEN line := line || ' TERMINATED BY ' || gensuffix; END IF; UTL_FILE.PUT_LINE(control_file, line);
    • depending on datatype of current field, generate additional conversion line := NULL; IF rec.data_type = 'NUMBER' THEN IF rec.data_scale = 0 THEN line := CHR(9) || '"TO_NUMBER(:' || rec.column_name || ',''FM' || LPAD('0', rec.data_precision, '9') || ''')"'; ELSE line := CHR(9) || '"TO_NUMBER(:' || rec.column_name || ',''FM' || LPAD('9', rec.data_precision - rec.data_scale, '9') || '.' || RPAD('0', rec.data_scale, '0') || ''')"'; END IF; ELSIF rec.data_type = 'FLOAT' THEN line := CHR(9) || '"REPLACE(:' || rec.column_name || ',' || v_sep2 || ',' || v_sep || ')"'; ELSIF rec.data_type = 'CHAR' OR rec.data_type = 'VARCHAR2' OR rec.data_type = 'LONG' THEN NULL; -- should add REPLACE expressions here ELSIF rec.data_type = 'DATE' THEN line := CHR(9) || '"TO_DATE(:' || rec.column_name || ',''' || REPLACE(REPLACE(REPLACE(v_datfmt, '\', '\\'), '''', ''''''), '"', '\"') || ''',''NLS_DATE_LANGUAGE=' || v_nls_language || ''')"'; ELSIF rec.data_type = 'RAW' OR rec.data_type = 'LONG RAW' THEN line := CHR(9) || '"HEXTORAW(:' || rec.column_name || ')"'; ELSIF rec.data_type = 'ROWID' THEN line := CHR(9) || 'CHARTOROWID(:' || rec.column_name || ')"'; ELSE -- MLSLABEL NULL; END IF; IF line IS NOT NULL THEN UTL_FILE.PUT_LINE(control_file, line); END IF; END LOOP;
  • Terminate the fields description UTL_FILE.PUT_LINE(control_file, ')');
  • Close the generated control file UTL_FILE.FCLOSE(control_file); END; / SHOW ERROR; DROP PUBLIC SYNONYM utl_control; CREATE PUBLIC SYNONYM utl_control FOR utl_control; GRANT EXECUTE ON utl_control TO PUBLIC WITH GRANT OPTION;
• Generate control files to load all table from a delimited textfile CREATE OR REPLACE PROCEDURE utl_allcontrol( pathname IN VARCHAR2, formatname IN CHAR DEFAULT 'ORAtUS', -- for Oracle (US) with tabs fileext IN VARCHAR2 DEFAULT '.dat', ctrlext IN VARCHAR2 DEFAULT '.ctl') IS CURSOR cur IS SELECT table_name FROM user_tables; BEGIN FOR rec IN cur LOOP BEGIN utl_control( tablename => rec.table_name, pathname => pathname || '/' || LOWER(SUBSTR(rec.table_name,1,1)), filename => LOWER(rec.table_name) || fileext, ctrlname => LOWER(rec.table_name) || ctrlext, formatname => formatname); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Cannot generate control file ' || LOWER(rec.table_name) || ctrlext || ' in path ' || pathname || '/' || LOWER(SUBSTR(rec.table_name,1,1)) || ' for data file ' || LOWER(rec.table_name) || fileext || ' of table ' || rec.table_name); END; END LOOP; END; / SHOW ERROR; DROP PUBLIC SYNONYM utl_allcontrol; CREATE PUBLIC SYNONYM utl_allcontrol FOR utl_allcontrol; GRANT EXECUTE ON utl_allcontrol TO PUBLIC WITH GRANT OPTION;
• Unload a table into a delimited textfile on server ---- CREATE OR REPLACE PROCEDURE utl_unload( tablename IN VARCHAR2, pathname IN VARCHAR2, filename IN VARCHAR2, formatname IN CHAR DEFAULT 'ORAtUS') -- for Oracle (US) with tabs IS v_prefix VARCHAR2(1); v_esc VARCHAR2(1); v_esc2 VARCHAR2(8); v_suffix VARCHAR2(1); v_suffix2 VARCHAR2(8); v_sep VARCHAR2(1); v_sep2 VARCHAR2(8); v_rs VARCHAR2(1); v_rs2 VARCHAR2(8); v_datfmt VARCHAR2(32); genprefix VARCHAR2(32); gensuffix VARCHAR2(32); gensep VARCHAR2(32); CURSOR cur IS SELECT c.column_name, c.data_type FROM user_tab_columns c WHERE c.table_name = UPPER(tablename) ORDER BY c.column_id; sqlline VARCHAR2(32000); line VARCHAR2(255); output_file UTL_FILE.FILE_TYPE; cda INTEGER; ret INTEGER; dataline VARCHAR2(32000); BEGIN
  • find format options in the description table for the given format name
  • (one row retreived, raise an exception if format name not found) SELECT u.prefix, u.esc, u.esc2, u.suffix, u.suffix2, u.sep, u.sep2, u.rs, u.rs2, u.datfmt INTO v_prefix, v_esc, v_esc2, v_suffix, v_suffix2, v_sep, v_sep2, v_rs, v_rs2, v_datfmt FROM utl_loadfmt u WHERE u.format = formatname;
  • fix format options into constants used to build the dynamic query IF v_prefix IS NULL THEN genprefix := NULL; ELSE genprefix := '''' || v_prefix || '''||'; END IF; IF v_suffix IS NULL THEN gensuffix := NULL; ELSE gensuffix := '''' || v_suffix || '''||'; END IF; IF v_sep IS NULL THEN gensep := '||'; ELSIF v_sep = '''' THEN gensep := '||''''''||'; ELSIF v_sep = CHR(9) THEN gensep := '||CHR(9)||'; ELSE gensep := '||'''||v_sep||'''||'; END IF;
  • find and describe the table, field by field
  • and build a (big) dynamic SELECT query sqlline := NULL; FOR rec IN cur LOOP
    • depending on the current field, generate appropriate formatting IF rec.data_type = 'NUMBER' THEN line := 'TO_CHAR(' || rec.column_name || ')';
      • if prefix is set, don't add prefix or suffix for NUMBER (CSV)
      • add suffix for NUMBER only if no prefix is set (Informix) IF v_prefix IS NULL THEN line := line || gensuffix; END IF; ELSIF rec.data_type = 'CHAR' OR rec.data_type = 'VARCHAR2' THEN
      • always generate prefix and suffix for CHARs and VARCHARs (CSV) IF v_prefix IS NULL THEN line := genprefix || 'REPLACE(REPLACE(REPLACE(' || rec.column_name || ',''\'',''\\''),''|'',''\|''),CHR(10),CHR(7))' || gensuffix; ELSE line := genprefix || 'REPLACE(' || rec.column_name || ',''"'',''""'')||''"''' || gensuffix; END IF; ELSIF rec.data_type = 'DATE' THEN line := 'TO_CHAR(' || rec.column_name || ',''' || v_datfmt || ''')';
      • if prefix is set, don't add prefix or suffix for NUMBER (CSV)
      • add suffix for NUMBER only if no prefix is set (Informix) IF v_prefix IS NULL THEN line := line || gensuffix; END IF; ELSE line := rec.column_name;
      • if prefix is set, don't add prefix or suffix for OTHER (CSV)
      • add suffix for OTHER only if no prefix is set (Informix) IF v_prefix IS NULL THEN line := line || gensuffix; END IF; END IF;
    • check if separator must be initiate the line for current table-field
    • then add the line (displayed below) to the SQL query IF sqlline IS NULL THEN line := 'SELECT ' || line; sqlline := line; ELSE line := gensep || line; sqlline := sqlline || line; END IF; DBMS_OUTPUT.PUT_LINE(SUBSTR(line,1,255)); END LOOP;
  • check if the loop generated any table-field (table should exist) IF sqlline IS NOT NULL THEN
    • terminate the query to select data from the table line := 'FROM ' || tablename; sqlline := sqlline || ' ' || line; DBMS_OUTPUT.PUT_LINE(line);
    • now create a file to save formatted data into
    • may raise an exception if not authorized! output_file := UTL_FILE.FOPEN(pathname, filename, 'w');
    • parse and execute the query cda := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cda, sqlline, DBMS_SQL.V7); DBMS_SQL.DEFINE_COLUMN(cda, 1, dataline, 32000); ret := DBMS_SQL.EXECUTE(cda);
    • fetch each formatted data row into the open file
    • depending on the volume of the table, this may be long! WHILE DBMS_SQL.FETCH_ROWS(cda) > 0 LOOP DBMS_SQL.COLUMN_VALUE(cda, 1, dataline); UTL_FILE.PUT_LINE(output_file, dataline); END LOOP;
    • terminate the query and close the output file DBMS_SQL.CLOSE_CURSOR(cda); UTL_FILE.FCLOSE(output_file); END IF; END; / DROP PUBLIC SYNONYM utl_unload; CREATE PUBLIC SYNONYM utl_unload FOR utl_unload; GRANT EXECUTE ON utl_unload TO PUBLIC WITH GRANT OPTION;
• Generate control files to load all tables from delimited textfiles CREATE OR REPLACE PROCEDURE utl_allcontrol( pathname IN VARCHAR2, formatname IN CHAR DEFAULT 'ORAtUS', -- for Oracle (US) with tabs fileext IN VARCHAR2 DEFAULT '.dat', ctrlext IN VARCHAR2 DEFAULT '.ctl') IS CURSOR cur IS SELECT table_name FROM user_tables; BEGIN FOR rec IN cur LOOP BEGIN utl_control( tablename => rec.table_name, pathname => pathname || '/' || LOWER(SUBSTR(rec.table_name,1,1)), filename => LOWER(rec.table_name) || fileext, ctrlname => LOWER(rec.table_name) || ctrlext, formatname => formatname); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Cannot generate control file ' || LOWER(rec.table_name) || ctrlext || ' in path ' || pathname || '/' || LOWER(SUBSTR(rec.table_name,1,1)) || ' for data file ' || LOWER(rec.table_name) || fileext || ' of table ' || rec.table_name); END; END LOOP; END; / SHOW ERROR; DROP PUBLIC SYNONYM utl_allcontrol; CREATE PUBLIC SYNONYM utl_allcontrol FOR utl_allcontrol; GRANT EXECUTE ON utl_allcontrol TO PUBLIC WITH GRANT OPTION;