{"id":17,"date":"2013-05-10T11:07:00","date_gmt":"2013-05-10T11:07:00","guid":{"rendered":"https:\/\/sanmaharjan.com\/2013\/05\/10\/exporting-query-output-into-file\/"},"modified":"2014-02-03T12:50:09","modified_gmt":"2014-02-03T12:50:09","slug":"exporting-query-output-into-file","status":"publish","type":"post","link":"https:\/\/sanmaharjan.com\/exporting-query-output-into-file\/","title":{"rendered":"Exporting Query Output into File"},"content":{"rendered":"
For reporting purpose you might need to dumps lots of query into file.
\nyes yes, most of the client tools allows to dump directly into xls, html, xml file which take a transfer bandwith to transfer data from server to client. so for big data this system might not be so worth it.you can find lots of way to dump text into file.\u00a0 here is one of the way i have been doing since 2 yrs.<\/p>\n

create or replace procedure dump_table_to_csv(l_query in varchar2,
\np_dir\u00a0\u00a0 in varchar2,
\np_filename in varchar2,
\nv_separator\u00a0\u00a0\u00a0\u00a0 VARCHAR2 DEFAULT ‘|’,
\nDateFORMAT VARCHAR2 DEFAULT ‘DD-MON-YYYY’ )
\nAS
\nl_output\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 utl_file.file_type;
\nl_theCursor\u00a0\u00a0\u00a0\u00a0 integer default dbms_sql.open_cursor;
\nl_columnValue\u00a0\u00a0 varchar2(4000);
\nl_status\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 integer;
\nl_colCnt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 number := 0;
\nl_separator\u00a0\u00a0\u00a0\u00a0 varchar2(1);
\nl_descTbl\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbms_sql.desc_tab;
\nBEGIN
\nl_output := utl_file.fopen( p_dir, p_filename, ‘w’,’10000′ );<\/p><\/blockquote>\n

EXECUTE IMMEDIATE ‘ALTER SESSION SET NLS_DATE_FORMAT=”DD-MON-YYYY”’;
\ndbms_sql.parse(\u00a0 l_theCursor,\u00a0 l_query, dbms_sql.native );
\ndbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );<\/p>\n

for i in 1 .. l_colCnt loop
\nutl_file.put( l_output, l_separator ||\u00a0 l_descTbl(i).col_name );
\ndbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
\nl_separator := v_separator;
\nend loop;
\nutl_file.new_line( l_output );<\/p>\n

l_status := dbms_sql.execute(l_theCursor);<\/p>\n

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
\nl_separator := ”;
\nfor i in 1 .. l_colCnt loop
\ndbms_sql.column_value( l_theCursor, i, l_columnValue );
\nutl_file.put( l_output, l_separator || l_columnValue );
\nl_separator := v_separator;
\nend loop;
\nutl_file.new_line( l_output );
\nend loop;
\ndbms_sql.close_cursor(l_theCursor);
\nutl_file.fclose( l_output );
\nEXCEPTION
\nWHEN OTHERS THEN
\nraise;
\nEND;
\n\/<\/p>\n

and where first argument is your query which generates the data.
\n2nd one is name of the directory alias
\n3rd filename you want to make it.
\n4th seperator, by which you want to separate data from each other. default is ‘|’
\nand 5th is Date format, in different country way timeformat is different.<\/p>\n

e.g.<\/p>\n

exec dump_table_to_csv (‘select * from tabs’, ‘DATA_PUMP_DIR’,’test.txt’,’,’,’YYYY-MM-DD’);<\/p><\/blockquote>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

For reporting purpose you might need to dumps lots of query into file. yes yes, most of the client tools allows to dump directly into xls, html, xml file which take a transfer bandwith to transfer data from server to client. so for big data this system might not be so worth it.you can find […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[31],"tags":[21,20],"_links":{"self":[{"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/posts\/17"}],"collection":[{"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/comments?post=17"}],"version-history":[{"count":2,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/posts\/17\/revisions"}],"predecessor-version":[{"id":30,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/posts\/17\/revisions\/30"}],"wp:attachment":[{"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/media?parent=17"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/categories?post=17"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/tags?post=17"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}