This is how I dump data from an Oracle Database (tested on 8i,9i,10g,11g,12c) to a delimited ascii file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 24 13:55:47 2017Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit ProductionSQL> set timing onSQL> select Dump_Delimited('select * from all_objects', 'all_objects.csv') nr_rows from dual; NR_ROWS---------- 97116Elapsed: 00:00:11.87SQL> ! cat /u01/etl/report/all_objects_readme.txt ********************************************************************* Record Layout of file /u01/etl/report/all_objects.csv ********************************************************************* Column Sequence MaxLength Datatype ------------------------------ -------- --------- ---------- OWNER 1 128 VARCHAR2 OBJECT_NAME 2 128 VARCHAR2 SUBOBJECT_NAME 3 128 VARCHAR2 OBJECT_ID 4 24 NUMBER DATA_OBJECT_ID 5 24 NUMBER OBJECT_TYPE 6 23 VARCHAR2 CREATED 7 20 DATE LAST_DDL_TIME 8 20 DATE TIMESTAMP 9 19 VARCHAR2 STATUS 10 7 VARCHAR2 TEMPORARY 11 1 VARCHAR2 GENERATED 12 1 VARCHAR2 SECONDARY 13 1 VARCHAR2 NAMESPACE 14 24 NUMBER EDITION_NAME 15 128 VARCHAR2 SHARING 16 13 VARCHAR2 EDITIONABLE 17 1 VARCHAR2 ORACLE_MAINTAINED 18 1 VARCHAR2 ---------------------------------- Generated: 24-02-2017 13:56:50 Generated by: ETL Columns Count: 18 Records Count: 97116 Delimiter: ][ Row Delimiter: ] ---------------------------------- SQL> |
Next to the query and the generated filename the Dump_Delimited function takes another 6 parameters, each one with a default value. Check out the PL/SQL, and BTW… the basics for this code comes from Tom Kyte.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | SET DEFINE OFF;CREATE OR REPLACE DIRECTORY ETL_UNLOAD_DIR AS '/u01/etl/report';GRANT READ, WRITE ON DIRECTORY ETL_UNLOAD_DIR TO ETL;CREATE OR REPLACE FUNCTION Dump_Delimited ( P_query IN VARCHAR2 , P_filename IN VARCHAR2 , P_column_delimiter IN VARCHAR2 := '][' , P_row_delimiter IN VARCHAR2 := ']' , P_comment IN VARCHAR2 := NULL , P_write_rec_layout IN PLS_INTEGER := 1 , P_dir IN VARCHAR2 := 'ETL_UNLOAD_DIR' , P_nr_is_pos_integer IN PLS_INTEGER := 0 )RETURN PLS_INTEGER IS filehandle UTL_FILE.FILE_TYPE; filehandle_rc UTL_FILE.FILE_TYPE; v_user_name VARCHAR2(100); v_file_name_full VARCHAR2(200); v_dir VARCHAR2(200); v_total_length PLS_INTEGER := 0; v_startpos PLS_INTEGER := 0; v_datatype VARCHAR2(30); v_delimiter VARCHAR2(10):= P_column_delimiter; v_rowdelimiter VARCHAR2(10):= P_row_delimiter; v_cursorid PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; v_columnvalue VARCHAR2(4000); v_ignore PLS_INTEGER; v_colcount PLS_INTEGER := 0; v_newline VARCHAR2(32676); v_desc_cols_table DBMS_SQL.DESC_TAB; v_dateformat NLS_SESSION_PARAMETERS.VALUE%TYPE; v_stat VARCHAR2(1000); counter PLS_INTEGER := 0;BEGIN SELECT directory_path INTO v_dir FROM DBA_DIRECTORIES WHERE directory_name = P_dir; v_file_name_full := v_dir||'/'||P_filename; SELECT VALUE INTO v_dateformat FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT'; /* Use a date format that includes the time. */ v_stat := 'alter session set nls_date_format=''dd-mm-yyyy hh24:mi:ss'' '; EXECUTE IMMEDIATE v_stat; filehandle := UTL_FILE.FOPEN( P_dir, P_filename, 'w', 32000 ); /* Parse the input query so we can describe it. */ DBMS_SQL.PARSE( v_cursorid, P_query, dbms_sql.native ); /* Now, describe the outputs of the query. */ DBMS_SQL.DESCRIBE_COLUMNS( v_cursorid, v_colcount, v_desc_cols_table ); /* For each column, we need to define it, to tell the database * what we will fetch into. In this case, all data is going * to be fetched into a single varchar2(4000) variable. * * We will also adjust the max width of each column. */IF P_write_rec_layout = 1 THEN filehandle_rc := UTL_FILE.FOPEN(P_dir, SUBSTR(P_filename,1, INSTR(P_filename,'.',-1)-1)||'_readme.txt', 'w');--Start Header v_newline := CHR(10)||CHR(10)||' ********************************************************************* '; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' Record Layout of file '||v_file_name_full; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' ********************************************************************* '||CHR(10)||CHR(10); UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' Column Sequence MaxLength Datatype '; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' ------------------------------ -------- --------- ---------- '||CHR(10); UTL_FILE.PUT_LINE(filehandle_rc, v_newline);--End Header--Start Body FOR i IN 1 .. v_colcount LOOP DBMS_SQL.DEFINE_COLUMN( v_cursorid, i, v_columnvalue, 4000 ); SELECT DECODE( v_desc_cols_table(i).col_type, 2, DECODE(v_desc_cols_table(i).col_precision,0,v_desc_cols_table(i).col_max_len,v_desc_cols_table(i).col_precision)+DECODE(P_nr_is_pos_integer,1,0,2) , 12, 20, v_desc_cols_table(i).col_max_len ) INTO v_desc_cols_table(i).col_max_len FROM dual; SELECT DECODE( TO_CHAR(v_desc_cols_table(i).col_type), '1' , 'VARCHAR2' , '2' , 'NUMBER' , '8' , 'LONG' , '11' , 'ROWID' , '12' , 'DATE' , '96' , 'CHAR' , '108', 'USER_DEFINED_TYPE', TO_CHAR(v_desc_cols_table(i).col_type) ) INTO v_datatype FROM DUAL; v_newline := RPAD(' '||v_desc_cols_table(i).col_name,34)||RPAD(i,10)||RPAD(v_desc_cols_table(i).col_max_len,11)||RPAD(v_datatype,25); UTL_FILE.PUT_LINE(filehandle_rc, v_newline); END LOOP;--End BodyELSE FOR i IN 1 .. v_colcount LOOP DBMS_SQL.DEFINE_COLUMN( v_cursorid, i, v_columnvalue, 4000 ); SELECT DECODE( v_desc_cols_table(i).col_type, 2, DECODE(v_desc_cols_table(i).col_precision,0,v_desc_cols_table(i).col_max_len,v_desc_cols_table(i).col_precision)+DECODE(P_nr_is_pos_integer,1,0,2) , 12, 20, v_desc_cols_table(i).col_max_len ) INTO v_desc_cols_table(i).col_max_len FROM dual; END LOOP;END IF; v_ignore := DBMS_SQL.EXECUTE(v_cursorid); WHILE ( DBMS_SQL.FETCH_ROWS(v_cursorid) > 0 ) LOOP /* Build up a big output line. This is more efficient than * calling UTL_FILE.PUT inside the loop. */ v_newline := NULL; FOR i IN 1 .. v_colcount LOOP DBMS_SQL.COLUMN_VALUE( v_cursorid, i, v_columnvalue ); if i = 1 then v_newline := v_newline||v_columnvalue; else v_newline := v_newline||v_delimiter||v_columnvalue; end if; END LOOP; /* Now print out that line and increment a counter. */ UTL_FILE.PUT_LINE( filehandle, v_newline||v_rowdelimiter ); counter := counter+1; END LOOP;IF P_write_rec_layout = 1 THEN--Start Footer v_newline := CHR(10)||CHR(10)||' ---------------------------------- '; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' Generated: '||SYSDATE; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' Generated by: '||USER; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' Columns Count: '||v_colcount; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' Records Count: '||counter; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' Delimiter: '||v_delimiter; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' Row Delimiter: '||v_rowdelimiter; UTL_FILE.PUT_LINE(filehandle_rc, v_newline); v_newline := ' ---------------------------------- '||CHR(10)||CHR(10); UTL_FILE.PUT_LINE(filehandle_rc, v_newline);--End Footer--Start Commment v_newline := ' '||P_comment; UTL_FILE.PUT_LINE(filehandle_rc, v_newline);--End CommmentUTL_FILE.FCLOSE(filehandle_rc);END IF; /* Free up resources. */ DBMS_SQL.CLOSE_CURSOR(v_cursorid); UTL_FILE.FCLOSE( filehandle ); /* Reset the date format ... and return. */ v_stat := 'alter session set nls_date_format=''' || v_dateformat || ''' '; EXECUTE IMMEDIATE v_stat; RETURN counter;EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR( v_cursorid ); EXECUTE IMMEDIATE 'alter session set nls_date_format=''' || v_dateformat || ''' '; RETURN counter;END Dump_Delimited;/SHOW ERRORS; |
This is very helpful that how we dump data from an Oracle Database tested on all version of oracle to a delimited ascii file.
Thank you for this blog and sharing this important codes.
Keep posting.